Dynamic cell references in formulas

i'm working on a spreadsheet that allows a user to select a state, and then does a vlookup on another worksheet to return the cell. for example, if you type in AK, it will return C3. C3 is the upper left cell of 6x7 table.

i want my chart to automatically update based on that cell reference C3, so i have attempted to create named ranges to reference in the chart source data. the chart also needs to be automatically updated when new columns are added to the report, which makes it a little trickier. Data is the name of the worksheet that contains the data tables.

here is the named range that seems to be working for the data labels, with fixed rows C & D):
=OFFSET(Data!$C$1,,,2,COUNTA(Data!$C$1:$IV$1)+1)

here is the named range for the values, which works when you start at a fixed value such as C3:
=OFFSET(Data!$C$3,,,6,COUNTA(Data!$C$1:$IV$1)+1)

in the formula immediately above, i need to somehow replace the "$C$3" with the cell that is referenced to begin with using the vlookup. so that any time the user changes the state, the range begins from the updated cell.

can you help me???

Hi

If the result of the vlookup is in B1 (ie the C3 result is in B1) then try

=OFFSET(INDIRECT("Data!" & B1),,,6,COUNTA(Data!$C$1:$IV$1)+1)

rylo

Seems to work! Thanks so much for the quick response!

The Indirect function seems to work great for defining the named range, but the chart source data does not accept this named range.

any suggestions on how i can get around that?

Hi

Does it not accept the name at all, or does it not update when the dynamic items change?

If the latter, then you may have to make the name refer to a single row or column of data, and have fixed names that are offsets from the dynamic name.

If this doesn't describe your situation, then time for an example file to show what you are dealing with.


rylo