absolute cell references for varying chart data - how?

WayneTN · May 6th, 2002, 12:37 PM

I am a newbie but I've tried and been unable to find help anywhere on this charting situation. I have a spreadsheet that is coded to show today's date in cell A1 [=today()] and then past days in subsequent rows [=A1-1], [=A2-1], etc. Then, in col. B I am entering some daily parameter, say temperature. My chart is formatted to show only the past 50 days. As you would expect, when I start up the spreadsheet all of the dates change, but now the temperature amounts are off by one day. I wrote a macro to insert a new cell beside today's date (and shift the other temperature amounts down), but here is the problem: the series for the chart curve also shifts down even though it originally showed cell references as $B$1:$B$50 - with the dollar signs. (It changes to $B$2:$B$51). How can I keep the curve pointing to cells B1:B50 even though I insert a new cell and enter a new temperature daily? TIA.........Wayne

[ This Message was edited by: WayneTN on 2002-05-06 11:39 ]

[ This Message was edited by: WayneTN on 2002-05-06 11:41 ]

Ricky Morris · May 6th, 2002, 12:44 PM

Try crating a range name Data with the following formula: =indirect("B1:B30"). Reference your chart to Data to keep it from changing.

WayneTN · May 7th, 2002, 10:27 AM

Ricky,

This was exactly what I needed. Most of what Excel can do, I never have use it. But, when I finally figured out how to apply what you were telling me to do, it worked like a champ. My chart now refers to and plots the data within an absolute address range of cells that never changes - only the data within the range updates with my daily input.

Thanks a lot!
Wayne

May 6th, 2002, 12:44 PM	#2
Ricky Morris Board Regular Join Date: Mar 2002 Posts: 363	Try crating a range name Data with the following formula: =indirect("B1:B30"). Reference your chart to Data to keep it from changing. __________________ It's never too late to learn something new. Ricky

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 6th, 2002, 12:37 PM	#1
WayneTN Board Regular Join Date: May 2002 Location: Tennessee, USA Posts: 72	I am a newbie but I've tried and been unable to find help anywhere on this charting situation. I have a spreadsheet that is coded to show today's date in cell A1 [=today()] and then past days in subsequent rows [=A1-1], [=A2-1], etc. Then, in col. B I am entering some daily parameter, say temperature. My chart is formatted to show only the past 50 days. As you would expect, when I start up the spreadsheet all of the dates change, but now the temperature amounts are off by one day. I wrote a macro to insert a new cell beside today's date (and shift the other temperature amounts down), but here is the problem: the series for the chart curve also shifts down even though it originally showed cell references as $B$1:$B$50 - with the dollar signs. (It changes to $B$2:$B$51). How can I keep the curve pointing to cells B1:B50 even though I insert a new cell and enter a new temperature daily? TIA.........Wayne [ This Message was edited by: WayneTN on 2002-05-06 11:39 ] [ This Message was edited by: WayneTN on 2002-05-06 11:41 ]

May 7th, 2002, 10:27 AM	#3
WayneTN Board Regular Join Date: May 2002 Location: Tennessee, USA Posts: 72	Ricky, This was exactly what I needed. Most of what Excel can do, I never have use it. But, when I finally figured out how to apply what you were telling me to do, it worked like a champ. My chart now refers to and plots the data within an absolute address range of cells that never changes - only the data within the range updates with my daily input. Thanks a lot! Wayne