Need a pop up of a range of cell values or to copy same to a new tab in Excel 2010

Mike Caldwell
Mike Caldwell used Ask the Experts™
on
I need to deliver a demo to a client that will eventually be web based if we get the gig, but for now a proof of concept using Excel, ideally without macros, etc.

First I have a Summary page with various categories listed and a count of how many are within the category.  What I need then is if a category is clicked either a pop up window comes up with prepositioned data associated with the category or a text window.  Client will not accept macros.  Only four categories, but the results page could include several hundred line items, so manually creating Comment boxes is not practical.  I have been experimenting with hyperlinks, but cannot get a range to show up, only one line in the named range.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike CaldwellConsultant to IP industry

Author

Commented:
I have hyperlinks to a new sheet working fine.  However my problem is that I need to fill in (extract) a subset of data on the target sheet.  For example, suppose I have a page listing patent numbers.  I click on a patent number, I need to either pop up a list of all of the inventors (preferred), or jump to a page where the list has been extracted to.  It cannot be a dedicated sheet for that patent, because there will be hundreds of patents.
Rob HensonFinance Analyst

Commented:
If you have the Summary as a Pivot Table, based on a more extensive data table; double clicking on an item within the pivot will create and activate a sheet containing the breakdown of the number double clicked.

Thanks
Rob H
Mike CaldwellConsultant to IP industry

Author

Commented:
Rob, that looks like what I need.  I've made some pretty complex spreadsheets over the years, but never a Pivot Table.  Been reading posted "How To's" and watched a few You Tube demos, but just cannot figure out how to make it do what I am after.  To make it clear, I am posting a very small sample of my 20K line item spreadsheet.  If I could make the two sample sheets work, I think I could do all the rest of the stuff I want.
Pivot-Test.xlsx
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Rob HensonFinance Analyst

Commented:
See attached updated. For the count by Year I added a column to the data to pull the year from Publication date.

Thanks
Rob H
Copy-of-Pivot-Test.xlsx
Mike CaldwellConsultant to IP industry

Author

Commented:
Wow, really helpful.  I'm wondering if there is a way to click on a year on the Year Count sheet, which then jumps to a different sheet that lists just the application numbers for that year.  That sounds like a book mark, but would only have the data for the year clicked.
Finance Analyst
Commented:
Pivot table has the option that if you double click on a value, it will create a sheet with a breakdown of the value clicked. So double click the count against a particular year and it will give you the data for that year.

On that basis if you double click the overall total at bottom right it will duplicate the source data.

Thanks
Rob H
Mike CaldwellConsultant to IP industry

Author

Commented:
Looks like I should have learned about and used pivot tables years ago.  I really appreciate the patience and education.
Rob HensonFinance Analyst

Commented:
Glad to be of help, I only really started using the full features of pivots recently.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial