Solved

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

Posted on 2014-03-03
8
264 Views
Last Modified: 2014-03-06
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.
0
Comment
Question by:Mike Caldwell
  • 4
  • 4
8 Comments
 
LVL 1

Author Comment

by:Mike Caldwell
Comment Utility
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
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
 
LVL 1

Author Comment

by:Mike Caldwell
Comment Utility
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
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:Mike Caldwell
Comment Utility
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
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
Comment Utility
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
 
LVL 1

Author Closing Comment

by:Mike Caldwell
Comment Utility
Looks like I should have learned about and used pivot tables years ago.  I really appreciate the patience and education.
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
Glad to be of help, I only really started using the full features of pivots recently.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now