Include vlookup results in chart datasource or formula

[Cardstang]

Hello all,

I have a series of about 30 named tables that are identically sized and all reside on the same tab of a spreadsheet. They are all 12 rows tall and 13 columns wide (My first table is A10:M21).

I have a vlookup formula that looks at the entry of another cell that has data validation. The result of the vlookup is the range of one of my named tables.

For example, my data validation entries are:
Table1, Table2, Table3, etc.

Selecting Table1 produces in the results of the vlookup formula "A10:M21". Selecting Table2 results in "A30:M41" and so on.

I'd like to use the results as the datasource of a chart. Of course, if I set the datasource as the cell where the vlookup formula is, it's just one cell and it doesn't make a very nice chart.

And similarly (and perhaps alternately), how do I get the same vlookup results into a function?



Thanks!

[HansV]

I'm certain that it's possible, but I have trouble visualizing your setup. Could you post a sample workbook?

[Cardstang]

I think I kept everything needed to make this a useful sample.

Thanks for looking at this.

[HansV]

You could use the following code in the worksheet module:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Range("P13"), Target) Is Nothing Then
    Me.ChartObjects(1).Chart.SetSourceData Source:=Me.Range(Range("P14"))
  End If
End Sub

Don't forget to save the workbook as a .xlsm!

See attached version.

sample.xlsm

[Cardstang]

Very nice!