Hi,
I'm trying to create what is essentially a report card for our staff. I have all the data in a pivot table and then I am using 'GETPIVOTDATA's in the report template to extract the information, linking the sheets via a cell that contains the staff members name. Easy enough. However for reasons of efficiency and practicality I would like to be able to select the names from a drop down list on the sheet rather than having to type it in manually or maintaining a separate sheet for everyone. The operators will change from month to month and there are approximately 90 staff at any given point in time that will need to be reported on so it really needs to be sourced from the pivot data.
Hopefully somebody on here has done this before and will be able to help me outIf you need me to make up a sample let me know.
Thanks in advance!
Last edited by JoeyGirl; 02-26-2010 at 01:22 AM.
G'day JoeyGirl,Originally Posted by JoeyGirl
That would be great in supplying a sample.
Cheers
RC
Have I made you happy ??? If yes, please make me happy by pressing the Reputation icon in my post.
Please don't forget to do the same to other contributors of this forum.
Thanks
I don't void confusion, I create it
Hi,
Here is a basic model of the functionality. Obviously the real thing is more attractive and contains a lot more information and calculations off the data
Basically what I want is the yellow box to be a drop down list of all the staff names in the pivot table.
Cheers
G'day
Try this in 'Report' D8
http://www.contextures.com/xlDataVal01.html
Let us know if there is any problems.
Cheers
RC
Have I made you happy ??? If yes, please make me happy by pressing the Reputation icon in my post.
Please don't forget to do the same to other contributors of this forum.
Thanks
I don't void confusion, I create it
Hey,
I was looking at that website earlier yesterday, but I can't get the named range to work in the pivot table so that it can be picked up properly by the list.
In applying the OFFSET formula for a dynamic named range to the pivot table I get "Amy, <blank>, <blank>, <blank>, Jake, <blank>, <blank>, <blank>" as my list options due to all the empty cells not being included in the count of the number of entries. This is because there are eight cells with contents in column A in the pivot table, so that formula is only counting 8 spaces from the top of the data when it creates my list.
Is there perhaps a way to alter this formula to count blank spaces in the named range formula but not include the blank spaces in the list?
Hi,
See attached sample ...
HTH
G'day
See this link with an example attached and highlighted in yellow (Formula only)
http://www.cpearson.com/EXCEL/noblanks.htm
Cheers
RC
Have I made you happy ??? If yes, please make me happy by pressing the Reputation icon in my post.
Please don't forget to do the same to other contributors of this forum.
Thanks
I don't void confusion, I create it
Hi,
Thanks for both of your contributions! I think I have enough information to finish my spreadsheet now, you've been so helpful (:
Cheers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks