Hi,



I have a dashboard with 3 columns as depicted below, where year means the period referred to, Newbees are the new agencies and Existing are the already existing agencies.

Year Newbee Existing
2010-11 2.12 163.43
2011-12 7.92 206.04
2012-13 11.83 236.19
2013-14 16.95 237.90

What I want to do is that when I click on cell A2 (2010-11) or cell A3 (2011-12) or so on, it opens a pop up or a window and displays the names of the agencies in the popup as displayed below:

2010-11 (Period referring to)
Sl. No. Newbee Existing
1 Amit assoc. Gupta & co
2 Parth co. Aggarwal Logistics
3 Glennfier The Company
4 Bumbble ABC & Co
5 XYZ assoc
6 hfuh pvt ltd
7 Mhfnd & co.

This information will be pulled form a source sheet in the same workbook which will have the period wise breakup of the Newbees and Existing agencies.
I have attached an example file as well.
Please help as this is really urgent and with your help I might be able to close it and share this dashboard with my COO today itself.

Regards

Prayank

Attached Files

Example.xlsx (10.7 KB, 7 views)

Dunno whether it'll be today, but if you'll allow a little freedom with the requirements I can think of a few ways to do something similar. There are three events you can try:

1) BeforeDoubleClick requires the user to double click on a cell. I've used this one.

2) BeforeRightClick is activated when the user right-clicks on a cell, before the menu pops up. This enables you to add an item to the menu, or to "Cancel" the menu and do something else instead. Probably not what you want, but it's available. (I've never tried it.)

3) If you really want a click, I've never used it but you probably want the Worksheet_SelectionChange event.

That's the first part. Now, what your program will do when it's triggered is another set of choices. The obvious one is to put the data in a MsgBox. Or you could fill the data in some predesignated cells somewhere nearby, within the user's field of vision, probably just to the right.

Or you could update the target cell's comment, and let the cursor's presence over the cell trigger the comment to pop up. That might work; I'm not sure. But it brings up another possibility: How about having the required data always in the comment? Then you don't need any fancy events in the presentation sheet; you just need a Worksheet_Change function in the data sheet, so that every time you update the data there it updates the comment in the presentation worksheet.

Hi,

I used the "DoubleClick" method and activated a filter in the "Source" sheet.
When you dbl click a date this will fire the code to set the filter to the date selected.
It will then take you to the "Source" sheet for review.
The user will then after review click on the tab to take them back to the "DashBoard".

Attached Files

Example-3.xlsm (16.1 KB, 9 views)