Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
donesquire
 
Posts: n/a
Default 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?
  #2   Report Post  
Andy Pope
 
Posts: n/a
Default

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
  #3   Report Post  
donesquire
 
Posts: n/a
Default

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Datarange of source data Joyce Excel Discussion (Misc queries) 1 May 23rd 05 11:22 PM
referencing named formula using INDIRECT function [email protected] Excel Worksheet Functions 19 May 11th 05 09:48 AM
DATA VALIDATION with LEFT function Gabe Excel Discussion (Misc queries) 2 May 6th 05 06:37 PM
Charting data ranges that change mikelee101 Charts and Charting in Excel 2 December 17th 04 12:07 AM
Ignoring charachter data with Product function Stan Altshuller Excel Worksheet Functions 2 December 8th 04 03:27 PM


All times are GMT +1. The time now is 04:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"