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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

"JulieD" wrote:

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.

Hi ken

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

Cheers
JulieD

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

"JulieD" wrote:

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.