Thread: Clicking a cell should trigger a MsgBox with specific details

Hello,

I am trying to get the following to work:

I have a table with values in it. Each value is a sum of other values which are located on different sheets in the same excel workbook. In order to add the values for each cell I am using several SUM() matrix formulas which works great.

What I would like to do is when I click on a value in my table, I would like to get some sort of pop-up box that lists all the values that were used in summing up to that value.

For example the table goes from K8:V16
The value in T10 is 1200, which is the sum of several other numbers

The sum of T10 actually is:
Bus Ticket 20 EUR
Restaurant 200 EUR
Computer 900 EUR
Documents 80 EUR

The reason the matrix formula sums all of this up is because all the expenses occurred on the same day (e.g. Oct 1, 2010)

So what I would like is that when I click T10, that I get a list of these expenses in a pop-up box (showing a list similar to the one above). And of course, if I click T11, I should get a different list, just as if I click U10 or any other cell in the table I should get the values and names of the expenses that add up to that cell.

I was thinking of using MsgBox, but I don't know how to:
1) trigger a VBA script through a single click on a cell
2) recognize which cell is triggering the event
3) using the information in the cell to find in adjacent sheets which values were used to add up to the total
4) instead of 3 above, or in addition: I know how to use VLOOKUP, which gives me ONE value in return, but don't know what to use to get all values...maybe using a loop?

Thank you in advance. I know that this is probably not easy, maybe we can tackle it step by step, and I will test and see.



I am using Excel 2010, but if possible would like to write a script that works on Excel 2003 also.

Cheers,
SSDLFUN

Please post a sample file.

Can we ask why you're not using a Pivot Table which would seem to offer you all of the functionality you require by default ?

If you designed the spreadsheet properly you could use Donkey's suggestion

I have to say that from a drill down perspective pursuing Pivots is something of a no brainer irrespective of your ability with formulae - you are in essence looking to recreate the wheel.
(the 3D nature of your set up will not lend itself to this task however... Multi Consolidation Pivots have limited flexibility - from a design perspective it is always better to have a single repository)

I would also take this opportunity to state that matrices of Arrays are bad news (always) ... it is likely that they will come back to haunt you from a performance perspective.

IMO (FWIW) you should add a key to your individual sheets such that you can revert to the much more efficient SUMIF* with concatenated criteria compared to concatenated key
*SUMIFS obviously excluded given backwards compatibility requirements

In terms of drill down without Pivots - you're looking at:

1 - using the Double Click Event to trap the intent
2 - iterating all appropriate worksheets
3 - applying Filters to each iterate sheet based on intersecting "Header" values of the Target Cell
4 - copying the visible cells (post filter less header) and writing to the newly created sheet (obviously adding each subset after the other so as not to overwrite)

the above is not something I have time to do myself presently but should hopefully act as a pointer if nothing else

You can't pop up in a pivottable, you can drill down & it is more efficient than array formulas and VBA

Originally Posted by ssdlfun

@Donkey: Not sure what you mean by "adding a *key* to the individual sheets to revert to SUMIF.

Consider A1:C10000 of:

a	x	67
b	x	12
c	y	10
d	z	26
a	y	39
a	z	16
b	z	94
c	x	38
a	x	10
b	y	80
etc...

Now consider we want to SUM C where A is "a" and B is "z"

Prior to XL2007 we could as you have shown use an Array or SUMPRODUCT equiv.

=SUMPRODUCT(($A$1:$A$10000="a")*($B$1:$B$10000="z");$C$1:$C$10000)

However these formulae are inefficient... prior to XL2007+ a good alternative is to concatenate fields of interest, eg:

D1:
=$A1&"@"&$B1
copied down to D10000

We can now dispense with inefficient formulae and revert to SUMIF using a concatenated criteria

=SUMIF($D$1:$D$10000;"a@z";$C$1:$C$10000)
(obviously the criteria need not be a constant)

used in large volume and/or with large ranges and/or in volatile context the gain from the SUMIF approach is likely to prove significant.

SUMIF also offers wildcard functionality so if we wanted to just search for "non-blank@z" then:

=SUMIF($D$1:$D$10000;"?*@z";$C$1:$C$10000)

a well constructed key offers lots of flexibility and in an efficient manner.

With Excel as you know Elegance and Efficiency do not always equate to the same and Efficiency is always the more important consideration (IMO)

*also have D functions... but can prove cumbersome depending on configuration

@royUK: that is true, when I write a spreadsheet for myself, I use PivotTables. This time the spreadsheet is for my parents, they don't even know what Excel is, so I am trying to make it simple. When they see a number and want to know what is behind it, I would like it to be intuitive and for them to get some details on the number when clicking...

@DonkeyOte: I see what you mean. In my case I guess I could use SUMIFS, though SUMIF is not enough given that I am using more than one criteria. Nevertheless I am actually summing more than one column in my real spreadsheet, which would probably not work with SUMIFS, unless I used a support column and then added them together. Regardless, I think I will use SUMIFS to make it smoother, though need to see the backward compatibility on that with Excel 2003. Thanks for your explanations!

Given all this, I am going back to the step by step approach...first figuring out how to make a pop-up with the information of the cell that triggered the event.
Can anyone help with this first step?

Thanks so far!

Originally Posted by ssdlfun

@DonkeyOte: ...though SUMIF is not enough given that I am using more than one criteria.

I can only guess from the above that you did not follow the example I provided ?

Originally Posted by ssdlfun

Nevertheless I am actually summing more than one column in my real spreadsheet, which would probably not work with SUMIFS, unless I used a support column and then added them together

Correct - that holds true for both SUMIF & SUMIFS
(unless dimensions of criteria tests match the summation range - which they won't here).

Originally Posted by ssdlfun

Regardless, I think I will use SUMIFS to make it smoother, though need to see the backward compatibility on that with Excel 2003.

If you need backwards compatibility then you can not use SUMIFS.

Originally Posted by ssdlfun

Given all this, I am going back to the step by step approach...first figuring out how to make a pop-up with the information of the cell that triggered the event.
Can anyone help with this first step?

See the Selection Change event, however, I can only reiterate that this is not the method you should be pursuing... given you are seemingly intent on this approach I confess I bow out at this point.

Originally Posted by ssdlfun

I did follow your examples (I think I did it right...maybe I am not doing it right). With SUMPRODUCT you are right, though wtih SUMIF it does not seem like I can do more than one criteria (??)

It seems given your prior point re: summation dimension that this is a moot discussion but multiple criteria is the purpose of the concatenation, no ?

=SUMIF($D$1:$D$10000;"a@z";$C$1:$C$10000)

the above uses two criteria - a & z the two being merged into a single condition by virtue of concatenated key column (D at source)

If you mean you want multiple "multiple" criteria - ie "a@z" and "b@z" then

=SUM(SUMIF($D$1:$D$10000;{"a"\"b"}&"@z";$C$1:$C$10000))
confirmed with Enter
-- use of inline array perhaps not best advice here..
-- I am also guessing at Spanish delimiter being \

Originally Posted by ssdlfun

If you have another suggestion on how to provide someone with detailed information who does not know Excel at all, or almost computers, I am happy to hear it.

I believe I've offered some pointers on all aspects.

Perhaps you could use this UDF.

A cell holding the formula
=ThreeFormulas(A1+B2+C3, "Car fare:"&A1&CHAR(13) &"Ticket:"&B2&CHAR(13) &"Dinner:"&C3)
will display the sum in the first argument. Selecting that cell will show a validation input message determined by the second argument. (and the third, if it exists)

Function ThreeFormulas(Formula1 As Variant, Optional Formula2 As Variant, Optional Formula3 As Variant) As Variant
    Dim formulaSnip As String, val2 As String, val3 As String
    Application.Volatile
    With Application.Caller
        formulaSnip = Mid(.Formula, 16)
        
        With .Validation
            .Delete
            .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop _
                    , Formula1:="=True", Operator:=xlBetween
             .ShowInput = True
             
            If Not IsMissing(Formula3) Then
                .InputTitle = CStr(Evaluate("CHOOSE(2," & formulaSnip))
                .InputMessage = CStr(Evaluate("CHOOSE(3," & formulaSnip))
            ElseIf Not IsMissing(Formula2) Then
                .InputMessage = CStr(Evaluate("CHOOSE(2," & formulaSnip))
            End If
        End With
    End With
    
    ThreeFormulas = Evaluate("CHOOSE(1," & formulaSnip)
End Function