Dynamic Chart From different Sheet

[Msadiqrajani]

Hi,
I have different dataset of same size on different.

What I want is to choose Sheet name from dropdown list and Chart got data of that sheet.

Suppose I have 3 sheets Sheet1, Sheet2 and Sheet3.

When I select Sheet 1 from dropdown list Chart will be created with data on that sheet.

What I have done is have created dynamic name range using Indirect function thats working. But when I am using this in chart as range it throws error.

This is formula I am using creating Dynamic name Range

=INDIRECT("'"&Model!$B$2&"'"&"!A3:A183")

Using Cell B2 Value as Sheet name

This formula for chart value.

=SERIES(,Model.xlsx!chDate,'S&P 500'!$B$2:$B$183,1)

 

[GlennUK]

Are you sure that the dynamic name range using Indirect function is working? How did you check it?

 

[Msadiqrajani]

I checked through VlookUp function.

 

[GlennUK]

You say : "But when I am using this in chart as range it throws error." ... what error happens exactly?

 

[Msadiqrajani]

It throws this error..

"A formula in worksheet contains one or more invalid reference.

Verify that your formula contains a valid path,workbook,name range and cell reference"

 

[GlennUK]

Choose a name that does not start with ch ... Excel can be fussy about that.

 

[Msadiqrajani]

Hi,
Its working now...
Created new Name range with same formula and new chart.
When entering name range to Chart series, instead of entering directly into formula bar, I select name range by right click>select data.

Thanks for your time....

 

[GlennUK]

That's great!