Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
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. |
#10
|
|||
|
|||
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. |
#11
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a Pivot Table Calculated Field to get a Unique Count | Excel Worksheet Functions | |||
Pivot Table services | Excel Discussion (Misc queries) | |||
Indirect reference from one worksheet to another | Excel Worksheet Functions | |||
Copy worksheet with Pivot Table and break link to original workshe | Excel Worksheet Functions | |||
Problem with Pivot Table Drop-Down Menus | Excel Worksheet Functions |