Retain cell references when column moves

[Marlene]

Hi there

Column C in a table contains data which is pulled into a chart:

=Sheet1!C5:C13

I want the chart to pull data from column C even if I insert a new column
to the left of Column C. In other words, when the current Column C data
moves to the right and becomes Column D, I DON'T want the reference to change
to

=Sheet1!D513

I want it to stay

=Sheet1!C5:C13

and reflect the new values that are in the new column C.

The Indirect function does this for a single cell, but I can't seem to make
it work for a range of cells as shown above. Any way to do this?

Every time a new column of data is added, I waste tons of time manually
changing all the cell references in about 100 charts, so that the charts are
built from the new data now in column C rather than the old data now in
column D. Any help would be appreciated.

Marlene

 

[joelpj]

Hi there

Column C in a table contains data which is pulled into a chart:

=Sheet1!C5:C13

I want  the chart to pull data from column C even if I insert a new column
to the left of Column C.  In other words, when the current Column C data
moves to the right and becomes Column D, I DON'T want the reference to change
to

=Sheet1!D513

I want it to stay

=Sheet1!C5:C13

and reflect the new values that are in the new column C.

The Indirect function does this for a single cell, but I can't seem to make
it work for a range of cells as shown above.  Any way to do this?

Every time a new column of data is added, I waste tons of time manually
changing all the cell references in about 100 charts, so that the charts are
built from the new data now in column C rather than the old data now in
column D.  Any help would be appreciated.

Marlene

Hi Marlene,

Try using the INDIRECT() formula to reference your cells
i.e. make the reference part text and part numbers

Sorry I don't have time to flesh this out

Joel

 

[Marlene]

Hi Joel:

Yes, I did find the Indirect function. But unfortunately, as I mentioned in
my original post, the Indirect function seems to work only with references to
individual cells (e.g. C1), not ranges of cells (e.g. C1:C15)

I did check the MS information for the Indirect function and confirmed this.
At least, the syntax that MS gives for the function reflects a single cell
only, and there is no mention of being able to use it for a cell range.

Is there a way to use this or any other method to accomplish the same goal
on a range of cells?

Marlene

 

[John Mansfield]

Hi,

You can use range names in combination with the OFFSET function to anchor
your chart data to a specific reference. For example (assuming Excel 2003),
and assuming columns B and C below cover rows 5 - 13 in the file called
"Test.xls" - Sheet1, create a simple column chart based on the data below:

Col B Col C
a 4
b 2
c 5
d 4
e 2
f 3
g 4
h 5
i 6

To anchor the series reference to a column:

Step 1

Go to Insert -> Name -> Define and name your data range (column C) "Data"
i.e. assign the name "Data" to the range Sheet1!C5:C13.

Step 2

Go to Insert -> Name -> Define and name your category range (column B)
"DataLabels".

Step 3

Activate your chart and go to Chart -> Source Data. Enter the formula
"=Test.xls!Data" into the series values input.

You can find more information about this technique here:

http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=518

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=246

http://www.peltiertech.com/Excel/Charts/index.html

http://www.andypope.info/charts/Scrolling.htm

 

[Shane Devenshire]

Hi,

I just want to clear up a missunderstanding INDIRECT will work on a range.
This is a valid formula:
=SUM(INDIRECT("A1"&F1))
where F1 contains a number which represents the end of your range.

However, for charting a fixed range I would recommend range names.