GetPivotData & Indirect

[CraigMcKee]

Hello, last one I promise!

I've just been playing with GetPivotData and it will do just what I want for a summary sheet. I don't want to have to manually edit hundreds of formulas though so was hoping to use INDIRECT to refer to the sheet names etc so I can just fill these in in a cell.

I have it half working....

=GETPIVOTDATA(TEXT(INDIRECT("B5"),"@"),'Question 1'!$F$6,TEXT(INDIRECT("B5"),"@"),"Correct")

In cell B5 I have the text "QUESTION 1" so would then just change this one column to Question 2, Question 3 etc.

What I want to do now is replace the sheet name but everything I've tried comes up with #REF.

How can I refer to cell F6 on the worksheet "Question 1" in this formula from a cell value?

[CraigMcKee]

Got it, got it, got it. Just had to use INDIRECT without the TEXT

[xlnitwit]

You should be able to use
=GETPIVOTDATA(B5,INDIRECT("'"&B5&"'!$F$6"),B5,"Correct")

[CraigMcKee]

I kept getting errors until I read somewhere that the TEXT one worked but got there now. Many thanks.

[xlnitwit]

Sorry, I forgot you need to add quotes for the data field name
=GETPIVOTDATA(""&B5,INDIRECT("'"&B5&"'!$F$6"),B5,"Correct")