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

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.
LVL 1
Mike CaldwellConsultant to IP industryAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike CaldwellConsultant to IP industryAuthor 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.
0
Rob HensonFinance AnalystCommented:
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
0
Mike CaldwellConsultant to IP industryAuthor 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
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rob HensonFinance AnalystCommented:
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
0
Mike CaldwellConsultant to IP industryAuthor 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.
0
Rob HensonFinance AnalystCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike CaldwellConsultant to IP industryAuthor Commented:
Looks like I should have learned about and used pivot tables years ago.  I really appreciate the patience and education.
0
Rob HensonFinance AnalystCommented:
Glad to be of help, I only really started using the full features of pivots recently.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.