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

"neoschenker" wrote in message
...
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" wrote:

you're welcome - thanks for the feedback

"neoschenker" wrote in message
...
thank you so much for replying. that worked perfectly.

"JulieD" wrote:

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


"neoschenker" wrote in message
...
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.....

"neoschenker" wrote:

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.

"JulieD" wrote:

Hi

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

hope this helps
Cheers
JulieD

"neoschenker" wrote in
message
...
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.