Excel Charts based on Lookups

[PJFry]

Every month I issue a report for my firm that has a good number of charts on it. My input set up is like this:
On one sheet I key in the new vaules for the month. This sheet also has historical data in it. The next sheet uses hlookups to pull the data off the input sheet. This is the sheet that feeds the graphs. What ever changes are made on the input sheet are automatically mirrored on the graphing sheet. To update the graphs to the current month, I simply change the lookup reference and the graph sheet automatically pulls the most current twelve months of data from the input sheet.

The problem occurs when I add or delete line itemsin the input sheet. If I decided to include a new chart, I insert the necessary number of lines into the input chart and add the data. The graphing sheet updates fine, but the graphs still read off of the old ranges and I manually re-map the graphs to the correct ranges. I have tried just adding the data at the end, but that gets confusing after a few months of new information. I am a wee bit **** and I like my chart data in the same order as the charts.

Is there a way to map an excel graph using a reference lookup. Example: "Twelve Month Coupon Rate" maps to "='UUI GI'!$C$95:$O$95. Is there a way to tell the chart to lookup "Twelve Month Coupon Rate" in the UUI GI Sheet and bring back!$C$XX:$O$XX? (The column range will never change).

Please let me know if any clarification is needed.

Thanks PJ

[duane]

it seems you want your hlookup row offset to change if you insert rows. There are two ways to do this that I use:

1) Name at least one cell on the row in question as a range - say data1 for example. Let's say this cell is in row 26

then in your hlookup

if it was =hlookup(something,range,25) make it =hlookup(something,range,row(data1)-1)

2) If column a has some identifier for the row you can do it with a match to give you the row offset

=hlookup(something,range(match(somethingelse,a1:a1000,0))

the identifier and the column for the match could also be any other column

hope this helps!