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

[neoschenker]

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

"neoschenker" <[email protected]> wrote in message
news:[email protected]...
>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.

[neoschenker]

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" <[email protected]> wrote in message
> news:[email protected]...
> >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.
>
>
>

[neoschenker]

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" <[email protected]> wrote in message
> > news:[email protected]...
> > >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]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> > >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.
>> >
>> >
>> >

[neoschenker]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> > >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]

you're welcome - thanks for the feedback

"neoschenker" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in
>> >> > message
>> >> > news:[email protected]...
>> >> > >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.
>> >> >
>> >> >
>> >> >
>>
>>
>>

[neoschenker]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in
> >> >> > message
> >> >> > news:[email protected]...
> >> >> > >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

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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" <[email protected]> wrote in
>> >> >> > message
>> >> >> > news:[email protected]...
>> >> >> > >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.
>> >> >> >
>> >> >> >
>> >> >> >
>> >>
>> >>
>> >>
>>
>>
>>

[neoschenker]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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" <[email protected]> wrote in
> >> >> >> > message
> >> >> >> > news:[email protected]...
> >> >> >> > >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 ken

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

Cheers
JulieD

"neoschenker" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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" <[email protected]> wrote in
>> >> >> message
>> >> >> news:[email protected]...
>> >> >> > 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" <[email protected]> wrote in
>> >> >> >> > message
>> >> >> >> > news:[email protected]...
>> >> >> >> > >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.
>> >> >> >> >
>> >> >> >> >
>> >> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>