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.