Absolute referencing for chart source data

[Redbox911]

Hi,

I was wondering if someone could help me with this. I tried looking for a solution to this, but haven't been able to find one.

I have a chart that references a range of data. Let's assume A1:A5. I need to insert a row at the top with a new set of values periodically. BUT, I want the chart to continue referencing A1:A5, not A2:A6 (which happens when I insert a row). Is there a way of creating an absolute reference?

I found workarounds by creating a macro that readjusts the range, but is there a simpler and better way of doing this? I know that for cells you can use the INDIRECT function. I tried that in the source data, but it results in an error. Can anyone help me? Thanks!

 

[pgc01]

Hi Redbox911

Can you explain exactly how you've done it?

I just tried with Indirect() and had no probelm. I would, however, prefer to use Index() since it's non volatile.

I used a worksheet name referring to

=INDIRECT("Sheet1!A1:A5")

and

=INDEX(Sheet1!$A$A,1):INDEX(Sheet1!$A$A,5)

Both worked with no problem.

 

[Redbox911]

Hi pgc,

Thanks for the reply. The way I did it was to actually open up the source data for a graph and enter =INDIRECT("Sheet1!A1:A5") in place of =Sheet1!A1:A5. I would first select the graph, go to the chart menu on top and select Source Data. I entered in the new range (=INDIRECT("Sheet1!A1:A5")) there.

I know that you can set up an range on a worksheet using Indirect to reference A1:A5, but I was wondering if there was a built in Excel feature that will allow me to "name" the range in an absolute way when I enter the range into the Source Data.

Thanks!!

 

[pgc01]

Hi

I believe that is not possible, you cannot set the source data to an expression that includes operations.

If I'm not mistaken you can only set the source data to a range (address or name), or a constant (value or name).

 

[Redbox911]

Got it. Thanks a lot for the help, pgc!!