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???