I need to make a chart work with dynamic reference using a selector in cell A1 but referencing to a named table in another sheet as the data source.
The dynamic reference will use named tables table1, table2, table3, etc...
I have tried to use indirect formula in the chart data range to accomplish this but it gives me an error.
current Chart data range: ='sheet 1'!$A$1
Desired data range: =indirect("table1 &"[data]"")
Any ideas to get it right ?
Many thanks
You can create a named range using INDIRECT, then refer to the name in the chart series formula.
Hi, I tried but ir didn't work ,=indirect("table1 &"[data]"") gives me an error when I use it in th data range.
- cybersource
Last edited by cybersource; 12-26-2018 at 07:10 AM.
That INDIRECT formula doesn't make sense to me. Please see attached example. Use the yellow cell I1 to select which table you want to include in your chart. See the named range DataForChart that uses INDIRECT to select the range based on I1.
Hi 6StringJazzer.
Is it not possible to enter the indirect or offset formula directly into the Chart range input box? I have a number of different charts with multiple data series and to create named ranges for each series will be extremely tedious.
I don't think so but I haven't tried it. I don't believe you can use functions there, but give it a try and let us know what you find out.Originally Posted by kemit
I have tried, to no luck though. Please show me the syntax of entering the dynamic range name into the series input box. I just don't seem to get it right.
='sheetname'!dynamicrangename does not want to work.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks