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!
Include vlookup results in chart datasource or formula
-
- Lounger
- Posts: 35
- Joined: 17 Feb 2010, 22:08
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Include vlookup results in chart datasource or formula
I'm certain that it's possible, but I have trouble visualizing your setup. Could you post a sample workbook?
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 35
- Joined: 17 Feb 2010, 22:08
Re: Include vlookup results in chart datasource or formula
I think I kept everything needed to make this a useful sample.
Thanks for looking at this.
Thanks for looking at this.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Include vlookup results in chart datasource or formula
You could use the following code in the worksheet module:
Don't forget to save the workbook as a .xlsm!
See attached version.
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
See attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 35
- Joined: 17 Feb 2010, 22:08