Graphs within Excel

[voisey]

Hi Guys

Is it possible to have a variable within the source data for a graph within Excel? I have multiple worksheets with data and am having to produce multiple graphs as well. I would like to be able to select a different worksheet from the one graph.

e.g. Currently source data =Sheet1!$A$1:$A$100
I would like to be able to replace "Sheet1" with a cell reference and then users could select the sheet they want from a dropdown list e.g. If cell ref: D7 was the resultant from data validation (drop-down list and could be "sheet1"; "sheet2"; or even "sheet3") how would I instruct the graph to reference the contents of cell D7?

NB: Each sheet (1,2 and 3) will always have data in the same location which I want to report on

Any help would be appreciated

 

[AnotherHiggins]

You can create Named Ranges in excel using the Indirect function to determine what sheet the name refers to.

Something like this:
- Insert > Name > Define
- Pick a name
- In the Refers to field, enter something like [COLOR=blue white]=INDIRECT(Sheet1!$D$1 & "!$A$1:$A$100")[/color]

Now if Sheet1!$D$1 contains "Sheet1", then the Named Range refers to Sheet1!$A$1:$A$100. If Sheet1!$D$1 contains "BOB", then the Named Range refers to BOB!$A$1:$A$100.

Taking it further, you can make the named ranges dynamic so they will take into account how many rows are used and display the chart properly.

Here is a link that will explain how to use Named Ranges in charts.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Hi,

Many times the relative difficulty of a task, relates to how the source data is organized.

I suggest having ALL the data on one sheet. It makes things so much simpler. Your SELECTION, via a FORMS DropDown box, could be the CRITERIA for an AutoFilter. All this can be controlled with a minimum of VBA (macro) code. Then your Chart can be configured in Tools > Options > Chart tab to show only VISIBLE cells.

Your Source Data does not change. Only the DropDown/AutoFilter changes, and VOLA!

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[voisey]

Hi

Thanks anotherhiggins, tried that and it works

Cheers for your help. Unfortunately, SkipVought, I'm not able to have all data on one sheet - there are nine programmes/project who need to keep their data separate