Solution not required, rather a pointer in the right direction.

[Akashwani]

Good day.

I have set myself a little task!!

I have multiple "Tables" on one sheet and I want to be able to type the "Table Name" or a Defined Name (Named Range) into a cell on another sheet and have that sheet populated with all the data from the relevent "Table" or Defined Range and then produce a chart.

Could you please give me a few "hints" on how I should be looking at solving this?

I'm currently thinking Indirect!

Thanks

Ak

 

[James Snyder]

Just a nudge, and maybe not much of a nudge at that! Here is how I would approach it:
• On sheet(1) (w/tables), define the range of the table that you want to access
• On sheet(2), use a "With" statement to work with sheet1 stuff and use a copy destination:= to move the defined range
• On sheet(2) with the new data, build your graph with the data just moved

 

[Akashwani]

Hi James,

That was a nudge in the right direction (VBA), thanks.

I'm probably going to go with the following method, when I get my data organised that is!!.....

Change chart data range using a drop down list (vba) | Get Digital Help - Microsoft Excel resource

Cheers.

Ak

 

[nuked]

Personally, I would fill my entire sheet with the formula =IFERROR(INDIRECT($A$1),"")

Then in A1 type the name of the range I want to see. You could use data validation to make it a drop down.

 

[Akashwani]

Hi nuked.

Thanks for your input.

I tried that method, but I was only getting results from the first Named Range!!
I'm not sure where I was going wrong with it!

I "assumed" that if I typed the name of the Named Range in say A1, put =INDIRECT($A$2) in all the cells that I wanted to display the data, then the sheet would show the relevent data from that Named Range, but it didn't work that way for me.

All Named Ranges are of the same dimensions (X Columns * Y Rows) All Named Ranges and the Data Validation List names all match. I have deleted all the Named Ranges and started from scratch (numerous times) but I still only get data from the first Named Range!!

The VBA solution seems to be the (hassle free) way to go.

Thanks.

Ak

 

[Akashwani]

Oops,

This.....
if I typed the name of the Named Range in say A1

Should have read...
if I typed the name of the Named Range in say A2

Ak

 

[James Snyder]

Just as an FYI, I am a coder, so my solutions will always be VBA or Excel's macro language. I don't know diddly about formulas!

 

[Akashwani]

James, how I wish I knew just a little bit about VBA!!

Seeing as I have your attention James.

How would you change the following code so that it was based on a List of Named Ranges, rather than Tables?....

Sub SelectTable()
With ActiveSheet.Shapes(Application.Caller).ControlFormat
    If ActiveSheet.Shapes(Application.Caller).Name = "Drop Down 1" Then
        Worksheets("Dashboard").ChartObjects("Chart 3").Chart.SetSourceData Source:= _
        Range(.List(.Value) & "[#All]")
        Worksheets("Dashboard").ChartObjects("Chart 3").Chart.PlotBy = xlRows
    End If
End With
End Sub

Any ideas?

Thanks

Ak

 

[James Snyder]

Unfamiliar to me, but you are talking specifically about the "Source:="?