Quickly replace chart source

[tswartz]

I have 8 sheets with multiple person's monthly numbers for three years. It is basically arranged NAME in A1, January 2005 in B1, etc. until December 2007 in AN1. People's names go down the rows (to row 321) and then have corresponding monthly numbers.

I also have a template made up using a chart to overlay 2005, 2006, and 2007 numbers to see trending for each category (making 8 charts total).

My question is...how can I quickly replace the 8 chart sources to reflect a different row? In a perfect world, I'd allow the user to choose a person's name from a dropdown box and then Excel would generate the trending report. (I.E. if they chose person #222, Excel would replace the 8 chart source data in Sheet2 with row B222:AN222.) Any easy way to do this?

(If not, maybe there's a manual way to do it like Find/Replace?)

 

[HalfAce]

Hello tswartz,
One way you can do this is to use a series of vlookup formulas for your chart's source data.
I would use a validation cell or something for the name to be chosen from and then when
the lookup cell value changes it will auto update the chart with the correct data from that
name's row in your lookup table.

It can also be done with vba but it's generally accepted that "if it can be done with formula(s)
instead of vba, it should be."

Hope it helps.

 

[almagg]

i used a drop down box for graphs and this is what i did.

i created the chart with a 'feader' table.
in my case i had tables beneath this that were linked to various sheets.
when the user selected a dealer from the drop down box, the code looked for the appropriate linked table, copied it and pasted/special into the feader table.

took no time at all. the graph did not have to be rebuilt each time. all that is done is the data gets changed.

know what i mean?

 

[Jon Peltier]

Lots of ways to handle this. Put the chart above the table (insert a bunch of rows), plot all the rows, and use an autofilter to select just the one person's row. By default a chart doesn't plot a hidden row. Unfortunately this is limited to 255 rows, because you can't plot more series in a chart.

You could define a name (Insert menu > Names > Define), call it RowToPlot, and use this as its Refers To formula:

=offset(B1:AN1,Item,0)

Where Item is another name, which refers to a cell somewhere out of the way, like AP1. Change the value of AP1 (Item), and RowToPlot changes.

Add a dropdown from the Forms menu, and use AP as the cell link and A2:A321 as the input range. Make a chart using B1:AN1 as the Categories and B2:AN2 as the values, then replace B2:AN2 in the Source Data > Series dialog or in the Series formula with RowToPlot (don't remove the sheet name and exclamation point, leave it in front of the name). Changing the dropdown now changes the chart.

 

[HalfAce]

took no time at all. the graph did not have to be rebuilt each time. all that is done is the data gets changed.

Does this mean you're all set up now?
If not, you might try Jon's suggestions. (From all I've read he is the all time chart guru.)
If that's not what you're going to do then post back. I can describe what I meant with
the vlookups in more detail. (All it requires is selecting a name from the validation cell
and the chart will auto update as soon as the selection is made.)

 

[tswartz]

Jon, that's actually working great. I'm amazed at how you always seem to help me out, no matter how stupid my question is. I appreciate it.

I have the numbers linked from the other sheet to some cells below each chart (for reference). Is there an easy way to get the cell values (that I've just pasted as a link using Paste Special) to change based on the drop down menu?

 

[Jon Peltier]

tswartz -

You can use the same kind of OFFSET formula to populate the cells next to the chart.

 

[tswartz]

Duh - there's an example of a question I should have thought more about before posting. Thanks again, Jon.

When I use the offset formula in the cell, it gives me two #VALUE errors before printing the numbers. (January, February show #VALUE and then in March's spot, January's number shows up.) Any idea why this is? It works fine in the chart.

 

[Jon Peltier]

Check that the second or third arguments in the OFFSET are consistent with the first, and link to the correct cell within the range.