Help on Updating Graph Data, Please...Marti

[Marti]

Hi,

I am Excel 2003, WinXP, all updated. I have a spreadsheet with 40
identical tabs, numbered named 1 - 40, each holding survey data for
separate organizations. I have one tab with 9 graphs - the graphs
compare average responses from one organization to the averages from
all organizations, or one organization's average responses to another
organization's average responses. All the averages are computed in
the individual tabs, and Tab 1 contains total responses and averages
for all the organizations. (whew...)

Only problem I have is when I have to change the graphs. I have to
right-click on each graph, go to the source data portions, change the
series name and values, and after I do that, change the print header
to reflect the new company comparison.

I'm not a VBA person unfortunately, only a pretty good Excel user. Is
there a way to automate some or all of that? I never know what
company will be updated and require new graphs, or when I will want to
compare two organizations rather than one to the overall average. I'm
looking for something like a macro that would prompt me for the two
tab numbers to compare, then do the update on the source data series
name and values, and update the header!

Am I asking for too much?

Thanks,

Marti

 

[Bernard Liengme]

This is how I would start out:
Use a new sheet to capture the required organization's data - call this
SheetX
On SheetX in some cell, day D1 enter the name of the sheet (please do not
call them tabs!) holding required organization's data - for example enter
the text: Sheet10
Capture the data from Sheet10 using formulas in the form
=INDIRECT($D$1&"!F"&ROW()-2)
That's an exclamation before the F.
In this is in row 3 of SheetX, it collects the entry from cell F1 of Sheet10
The formula can be copied down the column to capture from F2, F3 .....
Use this (and other cells) to make the charts.
Now all you need do is type another sheet name in D1 (or where you decide)
to plot data from another sheet.

Come back if this is not clear - She_How_Must_Be_Obeyed thinks I should do
something other than computer stuff on Sunday, so I had to be brief!
best wishes

 

[Marti]

Thanks, Bernard - I'll try to follow and see what problems I have - if
I can't overcome I'll post again attn: you!

Marti

 

[Marti]

Worked like a charm! Thanks a bunch, you just don't know how much
work you saved me!

Marti