asked on

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.

Mike Caldwell

ASKER

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 Henson

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 Caldwell

ASKER

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

Rob Henson

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 Caldwell

ASKER

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.

ASKER CERTIFIED SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Caldwell

ASKER

Looks like I should have learned about and used pivot tables years ago. I really appreciate the patience and education.

Rob Henson

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