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
268 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
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 500 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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Outlook Free & Paid Tools
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

820 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