Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
272 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 1

Author Comment

by:Mike Caldwell
ID: 39902303
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 33

Expert Comment

by:Rob Henson
ID: 39903214
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
ID: 39905538
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 33

Expert Comment

by:Rob Henson
ID: 39906090
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
 
LVL 1

Author Comment

by:Mike Caldwell
ID: 39907206
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 33

Accepted Solution

by:
Rob Henson earned 2000 total points
ID: 39909663
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
ID: 39910278
Looks like I should have learned about and used pivot tables years ago.  I really appreciate the patience and education.
0
 
LVL 33

Expert Comment

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
New style of hardware planning for Microsoft Exchange server.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

721 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