Using INDIRECT function to specify source data
 

I have a simple line chart that plots monthly data points from a stock index
over time. Each month I have to manually modify the range of cells that are
the source data to include the new month's number.

Question - Is it possible for me to use the INDIRECT function to avoid the
manual update by having the ending cell reference consist of another cell
whose value automatically changes to the new ending row number when the date
changes to the new month? Here's an example to illustrate:

Current manual method:
- "Value" field in source data
='SheetName'!$F$6:$F$82
- When the new month arrives I change the $F$82 to $F$83 because the new
month's number is in the next row of data

Proposed method (which doesn't work as far as I can tell, but illustrates
the idea)
- Cell A5 on the data sheet holds the number of the row where the data point
for the latest month resides. This value updates automatically when the new
month arrives
- I'd like to use the INDIRECT function to point to a range that always
starts in the same place and automatically changes the end of the range when
the value of A5 changes. Something like this:
=INDIRECT("'SheetName'!$F$6:$F$" & 'SheetName'!A5)

Is there someway to make this work?

Hi,

You can use named ranges to do this. See Jon's example of dynamic charting.
http://peltiertech.com/Excel/Charts/DynamicCharts.html

Cheers
Andy

donesquire wrote:
I have a simple line chart that plots monthly data points from a stock index
over time. Each month I have to manually modify the range of cells that are
the source data to include the new month's number.

Question - Is it possible for me to use the INDIRECT function to avoid the
manual update by having the ending cell reference consist of another cell
whose value automatically changes to the new ending row number when the date
changes to the new month? Here's an example to illustrate:

Current manual method:
- "Value" field in source data
='SheetName'!$F$6:$F$82
- When the new month arrives I change the $F$82 to $F$83 because the new
month's number is in the next row of data

Proposed method (which doesn't work as far as I can tell, but illustrates
the idea)
- Cell A5 on the data sheet holds the number of the row where the data point
for the latest month resides. This value updates automatically when the new
month arrives
- I'd like to use the INDIRECT function to point to a range that always
starts in the same place and automatically changes the end of the range when
the value of A5 changes. Something like this:
=INDIRECT("'SheetName'!$F$6:$F$" & 'SheetName'!A5)

Is there someway to make this work?

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

This is great, thanks!

"Andy Pope" wrote:

Hi,

You can use named ranges to do this. See Jon's example of dynamic charting.
http://peltiertech.com/Excel/Charts/DynamicCharts.html

Cheers
Andy

donesquire wrote:
I have a simple line chart that plots monthly data points from a stock index
over time. Each month I have to manually modify the range of cells that are
the source data to include the new month's number.

Question - Is it possible for me to use the INDIRECT function to avoid the
manual update by having the ending cell reference consist of another cell
whose value automatically changes to the new ending row number when the date
changes to the new month? Here's an example to illustrate:

Current manual method:
- "Value" field in source data
='SheetName'!$F$6:$F$82
- When the new month arrives I change the $F$82 to $F$83 because the new
month's number is in the next row of data

Proposed method (which doesn't work as far as I can tell, but illustrates
the idea)
- Cell A5 on the data sheet holds the number of the row where the data point
for the latest month resides. This value updates automatically when the new
month arrives
- I'd like to use the INDIRECT function to point to a range that always
starts in the same place and automatically changes the end of the range when
the value of A5 changes. Something like this:
=INDIRECT("'SheetName'!$F$6:$F$" & 'SheetName'!A5)

Is there someway to make this work?

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info