Quickly replace chart source

tswartz

New Member
Joined
Jun 7, 2005
Messages
19
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?)
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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

Well-known Member
Joined
Aug 21, 2002
Messages
705
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

MrExcel MVP
Joined
May 14, 2003
Messages
5,269
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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

New Member
Joined
Jun 7, 2005
Messages
19
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

MrExcel MVP
Joined
May 14, 2003
Messages
5,269
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
tswartz -

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

tswartz

New Member
Joined
Jun 7, 2005
Messages
19
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

MrExcel MVP
Joined
May 14, 2003
Messages
5,269
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Check that the second or third arguments in the OFFSET are consistent with the first, and link to the correct cell within the range.
 

Forum statistics

Threads
1,181,409
Messages
5,929,771
Members
436,688
Latest member
sunnyBNH013

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top