Automatically change cell reference?

[Guest]

I have created a workbook for our metric dashboard reporting. It is an
extensive file that has a tab that is just for the display board, a tab for
raw data entry/ calculations, and several tabs for the drill down charts that
go to the display tab. I have the cells set up to change color by what the
metric calculations are by using conditional formating. Each month when I
update the entry tab, I have to go to the display tab and change each data
reference cell to reflect the next column for that data point to reflect the
new month. Is there a way to make it update to the new month column for the
cells that refer to the other tab?

 

[T. Valko]

Sounds like you need dynamic ranges:

http://contextures.com/xlNames01.html#Dynamic

Biff

 

[Guest]

This seems to add another reference as you add more data. I am looking to
automatically index to the next column in the referenced row when new data is
input. This will have the cell referencing the most current data entered in
the entry row. If my columns are set up as the months and the data points
are the rows. as I enter in the data for March, the cell on the display that
was pointing to February will advance one column to now display March data.
Does that make more sense?

 

[T. Valko]

From your explanation it sounds like you're wanting what a dynamic range
does.

Or, to put it another way, it sounds like you want the *LAST* entry in a
range. Does that better describe what you want? If that's what you're
looking for then what type of data is it? Text? Numeric? Could be either/or?
Are these values the results of formulas? Do these formulas return formula
blanks? Are there any empty/blank cells *within* the range?

See if this helps:

http://xldynamic.com/source/xld.LastValue.html

Biff

 

[Guest]

Hi,

Excel -- Data Validation -- Create Dependent Lists.

Use this URL: http://www.contextures.com/xlDataVal02.html

Challa Prabhu