Pivot table linked to current sheet!!!!

[richardeallen]

HI guys

Thanks in advance for any help anyone can give me.

I'm sure this is a very simple problem but cant find an answer anywhere.

I have a work sheet set up as a "Master" that contains a Pivot table with the range being:

'Master'!$C:$G

It all works fine. However this master sheet will be copied and renamed by other users and i need the pivot table to refer to range $C:$G on the renamed sheet not 'Master' as is the case now.

The copied sheets will remain in the same workbook so it shouldn't really be to hard to sort but can i do it........

I've tried INDIRECT and several other methods none of which seem to work for me.

Can someone please help.

My formula needs to basically acheive:

Pivot table range = 'This sheet'!$C:$G

Help!!!!

Thanks

[richardeallen]

P.S. It can pick up the sheet name from a cell within the sheet if required as i already have to enter it for something else.

Cheers

[olasa]

This is not optimal but it works 'fairly' well (see encl).
I have used a Named range (menu:Insert, Name...):
test=INDIRECT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)&"!"&"$A$3:$B$8")

When the pivottable is updated from Sheet4 is uses Sheet4 as the Indirect name and when on Sheet5 (after update) is uses Sheet5.

However, there are two limitations:
1. both pivottables updates. They can however have different layouts.
2. the sheet names can not contain blanks

I might be a start. Otherwise I would make a post in the VBA section.
//Ola