Directly reference table column in chart

[FarazM]

I'm looking to reference a table column for my chart data,

=Workbook1.xlsx!Table1[Values]

If possible it would be even better to be able to dynamically change the table name using the INDIRECT formula:

=INDIRECT("Workbook1.xlsx!Table"&$A$1&"[Values]")
where A1=1/2/3/etc.

Currently the only way I have found to this is with defined name where,
ValueSeries=INDIRECT("Table1[Values]")

I know you can use the OFFSET function or a table reference in a defined name,
However I am looking to copy my sheet and as far as I know named references do not copy along with the sheet.

If anyone could assist me on this issue it would greatly appreciated!

 

[cb366374]

I would avoid INDIRECT and OFFSET functions whenever possible (always possible if you build smart spreadsheets). How many times are you copying the table? It's not very difficult to copy named ranges. You can make them dynamic using INDEX as well.

Here is one I recently wrote, where the table is "data_Weekly", the column is "Cumulative Actual", and I'm using a user input called input_startweek to determine where to start the named range and input_endweek for where to stop it.

=INDEX(data_Weekly[Cumulative Actual],input_startweek+1,1):INDEX(data_Weekly[Cumulative Actual],input_endweek+1,1)