1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel - Quicker way to change range of cells used for source data

Discussion in 'Business Applications' started by Aussiemosis, Apr 18, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. Aussiemosis

    Aussiemosis Thread Starter

    Joined:
    Apr 17, 2008
    Messages:
    9
    Hello again, if I make a chart and look at the source data it might be something like "=Prices!$A$10:$A$100" and to change it takes a long time when there are several data series.

    Is there a way to make cell A1 control the start point and cell A2 control the end point, so that X-axis is updated to include only the range of data within the start and end points.

    Changing the above formula for the source data, I was thinking something like "=Prices!Offset($A$10,A1):Offset($A$10,A2)" but Excel doesn't like that, not one bit. And hopefully that demonstrates what I'm trying to do, probably better than my explanation.

    Thank you in advance yet again.
    Aussiemosis.
     
  2. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    Hi Aussiemosis.
    Are you able to upload an example.
    Are the prices input by you, or are they imported from somewhere else?
    How many data series are you jumping to and are they always a fixed range?
     
  3. Aussiemosis

    Aussiemosis Thread Starter

    Joined:
    Apr 17, 2008
    Messages:
    9
    The Villan I have attached an example, where there are three charts, each one using a different range (doesn't necessarily have to be 10 cells every time). Now instead of having three charts I would rather have just one, with the range being controlled by something I put in cells A1 and A2. I guess that could be a starting date, perhaps the current date. A1 is how many days before that to start the range, and A2 is how many days after that to end the range. If that clears it up?

    Hopefully much easier than continually editing the source data.

    Thank you.
    Aussiemosis.
     
  4. Aussiemosis

    Aussiemosis Thread Starter

    Joined:
    Apr 17, 2008
    Messages:
    9
    Retrying with the attachment, hopefully.
     

    Attached Files:

  5. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    I can see what you are trying to do. However, if your data range for each chart is variable as you seem to indicate, then a macro would probably be more suitable, where the auto macro asks you for the start and stop date and it does the rest for you.
    This needs a macro expert.
    Just be patient and one will come along shortly.
     
  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    What is going to be entered into cell A1 and cell A2? Are you going to enter the actual dates to use or will you be entering the starting and ending row numbers to use? Will the newest days worth of data always be entered in the top row of the workbook in row 6 while shifting the older days data down?

    If you are planning on entering the actual dates into the cells I would highly recommend formatting those cells as dates and then using DATA VALIDATION to force the user to select from values that are acceptable.

    Regards,
    Rollin
     
  7. Aussiemosis

    Aussiemosis Thread Starter

    Joined:
    Apr 17, 2008
    Messages:
    9
    Hello Rollin_Again...
    The value I enter into cells A1 and A2 can be either the start and end dates, in date format, or it can be for example 1 and 10 to give the range from the most recent data point up to 10 data points. Or even the start and end row numbers. Really just whichever method turns out easier.

    Newest data will always be in row 6, older data will be shifted down.

    Thank you.
    Aussiemosis.
     
  8. Aussiemosis

    Aussiemosis Thread Starter

    Joined:
    Apr 17, 2008
    Messages:
    9
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/704875

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice