Using INDIRECT() for chart series formula

1
votes

I was looking for some help on a dynamic graph I am trying to create.

To begin with, I have created a named range called PerX with the following formula.

='MySheetName!$B$2:$AZ$2

When I assign the chart series value to this formula, the chart works fine. Yay!

However, when I try to indirectly reference a different sheet name using the following formula, nothing is displayed. (The value in the cell A1 on sheet name 'Chart' being the name of the other sheet I am trying to pull data from)

=INDIRECT("'"&Chart!$A$1&"'!$B$2:$AZ$2")

Can anyone identify what I am doing wrong or whether what I am trying to do is possible?

Thanks a bunch

Chris

1
excelchartsnamed-ranges

1 Answers

0
votes

Just worked it out :)

=OFFSET(INDIRECT("'"&Chart!$A$1&"'!A1"),1,1,1,COUNTA(TA_Skills))

Where TA_Skills is the length of the range I need.