How do I use a pivot table value to reference a worksheet

[Guest]

I am trying to get the sytax right on a function that gets a value from a
pivot table:

=GETPIVOTDATA((cell("contents",a5))!B9,"Total Student Capacity")

I need it to get the contents from A5 which is the name of a worksheet in
the file. I've looked through help and tried a bunch of different things
with no luck. I have a long list of names that I don't want to have to
manually type in the sheet name as I will be adding in more data later. Any
help would be greatly appreciated.

 

[JulieD]

Hi

if i understand you correctly
=GETPIVOTDATA("Total Student Capacity",INDIRECT(A5 &"!B9"))

hope this helps
Cheers
JulieD

 

[Guest]

I tried using the INDIRECT function earlier today but that's a new one for
me. I thought the =getpivotdata function needed the reference first and then
the requested value - reverse of what you posted. I tried it both ways and
still got an error. It seems like it shouldn't tough to get the value from
A5 and use that for the sheet name. I can get it to work with
=GETPIVOTDATA(ANDERSON!B9,"Total Student Capacity") if the value in A5 is
"ANDERSON" but I can't find a way for it to pull that value and combine it
with !B9. I'm going to look at the INDIRECT function again in the help menu.
Thanks for replying.

 

[Guest]

i'm getting warmer. i got it to work by creating a new function at cell K5:
=CONCATENATE(A5,"!B9") then updating the main formula to:
=GETPIVOTDATA(INDIRECT(K5),"Total Student Capacity")

it may be hillbilly but it's getting the job done. Now it creates another
question - some of the values that are returned in K5 have a space in the
name such as:

DESERT HEIGHTS!B9 which is messing it all up giving me a #REF error. If
somebody knows of a good way around this I would appreciate the help. I'm
going to keep digging.....

 

[JulieD]

put the ' ' around all the sheet names, shouldn't hurt if they don't have a
space in them
e.g.
=CONCATENATE("'",A5,"'!B9")
single quote between a double in the first element to concatenate
single quote between the double and the exclaimation mark in the third

 

[Guest]

thank you so much for replying. that worked perfectly.

 

[JulieD]

you're welcome - thanks for the feedback

thank you so much for replying. that worked perfectly.

 

[Guest]

Now I run into an interesting situation. I had this long list of school
names which the pivot table was sorting. Now I have added more to that list.
The concatenate function did not automatically update per the cell it was
referencing - it still retained the old data after I refreshed the pivot
table. So I deleted out the concatenate cells except for the first, did a
copy and paste, and they all showed the data from the first cell instead of
actually referencing back to the cell that it was supposed to look at. The
only way I can get it to pull in the actual data is to delete the equal sign
from =CONCATENATE("'",A5,"'!B9") and then add it back in.

=CONCATENATE("'",A5,"'!B9") gives me a value of ALLEN!B9 which is correct
when I copied and pasted this cell down one I get
=CONCATENATE("'",A6,"'!B9") and the value is still ALLEN!B9 instead of
ANDERSON!B9 which is the value found in A6.

too many words....let me know if anyone has some ideas. thanks.

 

[JulieD]

Hi

how about zipping up the file and emailing it direct to me (julied at
hcts dot net dot au) , i'm not experiencing this on my test data (you have,
of course, checked ot see that calculation is automatic?)

cheers
JulieD

 

[Guest]

It's about 8mb - is that a problem? If it is I can post it on our ftp site
for you.

 

[JulieD]

Hi ken

it's on its way back to you ... might be a version problem... check it out
and let me know.

Cheers
JulieD