Change source worksheet for pivot based on cell value

[Obeide]

Hi,

I need to change the source for a pivot table based on the value in a cell. I have a worksheet named 'Summary' with a cell (A1) that has a dropdown containing the choices of Option1 and Option2 which refer to worksheets named 'Option1' and 'Option2' (this changes dynamically and there may be more than two options). Each Option worksheet has a table with exactly the same range (C56:L251) and I want the pivot table on the Summary worksheet to update depending on whether Option1 or Option2 is chosen as the source in cell A1.

I have tried unsuccessfully using a named range

=INDIRECT("'"&'Summary'!A1&"'!C56:C251")

Any and all help gratefully received.
Excel 2007

 

[circledchicken]

Hi and welcome to the MrExcel forum,

Try changing you're named range to:
=INDIRECT(Summary!$A$1&"!C56:C251")

 

[Obeide]

Hi thanks for your suggestion, I have tried it and I get the error 'Reference is not valid'. Can you please suggest something else I might be able to try?

 

[trazer985]

whats with the & inside the quotes?

 

[circledchicken]

Hi thanks for your suggestion, I have tried it and I get the error 'Reference is not valid'. Can you please suggest something else I might be able to try?

It should work. Try again ensuring:

• There are dollar signs around the A1 - i.e. $A$1
• You are using the named range as your reference rather than the actual formula
• The named range formula has been entered correctly - no extra quote marks have shown up

If it still doesn't work, perhaps paste a link to your file on Dropbox or Skydrive and I can have a look.

I don't think it is a good idea to do this though, as the user still needs to refresh the PivotTable each time unless you add some VBA code to hande that for example. It would be easier to just have seperate sheets with the different PivotTables and links to them on the Summary sheet.

 

[Obeide]

Hi, thanks for your help with this, i finally figured out that the issue was that I had a blank space after the sheet name, so it couldn't find it!.

 

[Obeide]

Oh and Merry Christmas/Happy Holidays

 

[circledchicken]

Oh and Merry Christmas/Happy Holidays

Thanks ! - Merry Christmas to you too!