LOSING MY MIND: Comment Display Question; please help

bradwolo

New Member
Joined
May 20, 2013
Messages
3
This is somewhat similar to a number of questions that are in here, but none seem to directly address this question. I apologize if I've just missed a thread but I can't seem to figure out how to do this.

I have a table that tallies errors made on an assignment based on a few different criteria. What I am looking to do is create a hover comment or message when the cell is selected that will list the assignment number from the error that was tallied in that cell.

IE assuming the following:

A B C D
Assignment number Employee Error Type Date
1 1001 Mike Entry 05/18/13
2 1002 Mike Entry 05/18/13
3 1003 Steve Document 05/18/13
4 1004 Carol Document 05/18/13
5 1005 Mike Validation 05/18/13
6 1006 John Entry 05/19/13
7 1007 Steve Validation 05/19/13
8 1008 Steve Document 05/19/13
9 1009 Mike Document 05/19/13


And from these data, I have created a table that tallies this data autimatically:

A B C
1 Entry Errors:
2 Employee 05/18/13 05/19/13
3 Mike 2 0
4 Steve 0 0
5 Carol 0 0
6 John 0 1

I need to be able to either hover or click the data in this table and display the corresponding assignment number. For example, in the bottom table if you clicked or hovered over cell B2, a comment box would display "1001, 1002".

Is this possible in excel?


Thanks in advance.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

bradwolo

New Member
Joined
May 20, 2013
Messages
3
Welcome to MrExcel.

Why don't you just create a pivot table from your list?

Hello Andrew, thank you. Maybe I don't fully understand the pivot table, but I'm not sure how that would allow me to click the tallied data and have excel display the corresponding assignment numbers...
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
It wouldn't give you a comment box, but it would allow you to drill down to the levels that you want to see.
 

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
While I agree with Andrew that a pivot table/drill down would be simplest, it could well be possible to put these data in the cell's comment, which can be shown when hovering over the cell. Did you use code to create th table? If so, show it and we'll tweak.
 

bradwolo

New Member
Joined
May 20, 2013
Messages
3
Thanks guys -

So I'm going to try and lay it all out below, hopefully its not too in depth.

I do some error tracking and quality control/loss mitigation underwriting, and part of what we're doing is compiling errors made by departments that lead to losses, and then identifying any trends we see. We have two tables tracking data, one from our loss mitigation department and the other from our audit department. The two tables of input data are as follows:


Meeting Date
GrI Loan
Accountable Party
Deficiency Type
5/17/2013
1000
Closing
**Documentation: ARM Data Incorrect
5/17/2013
1001
Underwriting/POD
**Income: Misc.
5/17/2013
1002
Underwriting/POD
**Liabilities: Misc, **Documentation: Misc.
4/19/2013
1003
Underwriting/POD
**Collateral: Incorrect Appraisal Form Completed
4/19/2013
1004
Underwriting/POD
**Collateral: Misc.
1/31/2013
1005
POD/GFE Desk/Closing
**Documentation: TIL Incorrect
1/31/2013
1006
Underwriting/POD/Closing
**Eligibility: Trust not properly documented/Ineligible Trust
1/17/2013
1007
Underwriting/POD
**Income: Employment History , **Income: Salaried Income Overstated
12/6/2012
1008
Underwriting/POD
**Credit: Late payment reflected on history
12/6/2012
1009
Underwriting
**Income: Self Employed Income Documents Missing/Unclear, **Income: Self Employed Income overstated

<tbody>
</tbody>

Meeting Date
GRI Loan Number
Accountable Party
Deficiency Type
5/17/2013
1100
Underwriting/POD/Closing
**Income: Misc.
5/17/2013
1101
Underwriting/POD
**Income: Ineligible income type, **Income: Rental Income Improperly Calculated/Documented, **Collateral: Misc.
5/17/2013
1102
Underwriting/POD
**AUS: Misc., **Liabilities: Property not documented as free and clear, **Income: Misc., **Liabilities: Taxes / Insurance Incorrect or not properly documented
5/17/2013
1103
Underwriting/POD/Closing
**Income: Employment History
5/17/2013
1104
Underwriting/POD
**Income: Ineligible income type
5/17/2013
1105
Underwriting/POD
**Income: Employment Type Incorrect
5/17/2013
1106
Underwriting/POD/Closing
**Income: Misc., **Liabilities: Liability's corresponding property not Documented, **Documentation: Misc.
5/17/2013
1107
Underwriting/POD
**Income: Misc., **AUS: Misc.
5/17/2013
1108
Underwriting/POD
**Liabilities: Property not documented as free and clear, **Liabilities: Liability's corresponding property not Documented, **AUS: Misc., **Income: Misc.

<tbody>
</tbody>


and then I track them in this table, tallying the error based on department, meeting date, and type of error. As you can see the deficiency type can be one or more of any of the items in the dropdown list, which is what has made this difficult to do with pivot tables (at least for me).
Deficiency Type
14-Feb
21-Feb
28-Feb
7-Mar
14-Mar
21-Mar
29-Mar
5-Apr
12-Apr
19-Apr
26-Apr
3-May
10-May
17-May
Totals
Percentage
ASSETS
2
5
-
11
1
3
2
-
1
1
8
2
-
-
44
18.03%
**Assets: Business Funds used not properly documented
-
-
-
-
-
-
-
-
-
-
-
-
-
-
1
0.41%
**Assets: Funds to Close from Unverified Account
-
1
-
-
-
-
-
-
-
-
1
-
-
-
2
0.82%
**Assets: Gift Not Properly Documented
1
-
-
-
-
-
-
-
-
-
2
-
-
-
4
1.64%
**Assets: Earnest Money not properly sourced
-
-
-
-
-
-
-
-
-
-
-
-
-
-
2
0.82%
**Assets: Input to AUS Incorrectly
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
0.00%
**Assets: Insufficient Assets Verified
1
2
-
9
1
-
1
-
1
-
1
-
-
-
16
6.56%
**Assets: Large Deposits/Transfers not properly documented
-
1
-
1
-
3
1
-
-
1
2
1
-
-
13
5.33%
**Assets: Misc.
-
-
-
-
-
-
-
-
-
-
1
-
-
-
1
0.41%
**Assets: Missing Statement/Missing Statement Pages
-
-
-
-
-
-
-
-
-
-
1
1
-
-
2
0.82%
**Assets: No Proof of Liquidation
-
1
-
1
-
-
-
-
-
-
-
-
-
-
3
1.23%
AUS
1
-
-
3
-
-
1
3
-
1
1
1
3
4
22
9.02%
**AUS: Case Number not Entered
-
-
-
-
-
-
-
-
-
-
-
-
-
-
1
0.41%
**AUS: Closed on Invalid, Ineligble, Caution, or Refer
-
-
-
-
-
-
-
1
-
-
1
-
1
-
3
1.23%
**AUS: Misc.
-
-
-
3
-
-
1
-
-
-
-
1
1
4
11
4.51%
**AUS: Not re-ran after changes
-
-
-
-
-
-
-
1
-
1
-
-
1
-
3
1.23%
**AUS: Ran as Incorrect Product Type
-
-
-
-
-
-
-
-
-
-
-
-
-
-
1
0.41%
**AUS: Ran as Incorrect Property Type
1
-
-
-
-
-
-
1
-
-
-
-
-
-
3
1.23%
COLLATERAL
-
-
1
-
1
4
-
2
1
2
1
1
-
1
17
6.97%
**Collateral: Comparable Properties Insufficient
-
-
-
-
-
-
-
1
-
-
-
-
-
-
3
1.23%
**Collateral: Incorrect Appraisal Form Completed
-
-
-
-
-
-
-
-
-
1
-
-
-
-
1
0.41%
**Collateral: Misc.
-
-
1
-
1
3
-
-
-
1
-
1
-
1
9
3.69%
**Collateral: Missing Necessary Comments/Photos
-
-
-
-
-
-
-
-
1
-
1
-
-
-
2
0.82%
**Collateral: No Final Inspection as Required
-
-
-
-
-
1
-
-
-
-
-
-
-
-
1
0.41%
**Collateral: Property not documented as Free and Clear
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
0.00%
**Collateral: Significant Adjustments not Addressed
-
-
-
-
-
-
-
1
-
-
-
-
-
-
1
0.41%
CREDIT
-
4
1
2
-
1
2
-
-
-
-
-
1
-
15
6.15%
**Credit: Disputed accounts >$500 <24 months old not manually downgraded
-
1
-
-
-
-
-
-
-
-
-
-
-
-
2
0.82%
**Credit: Judgments on Credit Report not evidenced as Satisfied
-
-
1
-
-
-
-
-
-
-
-
-
-
-
3
1.23%
**Credit: Late payment reflected on history
-
-
-
-
-
-
-
-
-
-
-
-
-
-
1
0.41%
**Credit: Misc.
-
3
-
2
-
1
2
-
-
-
-
-
1
-
9
3.69%
DOCUMENTATION
-
-
1
5
-
3
3
-
-
-
1
4
2
4
26
10.66%
**Documenation: GFE Incorrect
-
-
-
-
-
-
-
-
-
-
-
-
1
-
1
0.41%
**Documentation: ARM Data Incorrect
-
-
-
-
-
-
-
-
-
-
1
-
-
-
1
0.41%
**Documentation: Expired
-
-
-
-
-
-
-
-
-
-
-
-
-
-
1
0.41%
**Documentation: Incorrect Dates on Closing Docs
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
0.00%
**Documentation: Misc.
-
-
-
-
-
1
-
-
-
-
-
2
-
3
7
2.87%
**Documentation: Missing Documents
-
-
1
5
-
2
3
-
-
-
-
2
1
1
16
6.56%
**Documentation: TIL Incorrect
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
0.00%
ELIGIBILITY
-
-
-
5
1
3
-
2
-
-
5
5
1
-
26
10.66%
**Eligibility: Bankruptcy, Short Sale, DIL, or Foreclosure not sufficiently seasoned
-
-
-
-
-
-
-
-
-
-
1
-
-
-
1
0.41%
**Eligibility: Homepath related Eligibility Issue
-
-
-
-
-
-
-
1
-
-
1
-
-
-
2
0.82%
**Eligibility: Ineligible due to Credit
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
0.00%
**Eligibility: Max LTV/CLTV exceeded
-
-
-
-
-
2
-
-
-
-
-
-
-
-
2
0.82%
**Eligibility: Misc
-
-
-
1
1
1
-
-
-
-
-
-
1
-
6
2.46%
**Eligibility: Multiple Financed Properties Issue
-
-
-
-
-
-
-
-
-
-
3
5
-
-
8
3.28%
**Eligibility: Occupancy Incorrect/Misrepresented
-
-
-
4
-
-
-
-
-
-
-
-
-
-
5
2.05%
**Eligibility: Trust not properly documented/Ineligible Trust
-
-
-
-
-
-
-
1
-
-
-
-
-
-
2
0.82%
INCOME
1
4
-
14
2
5
1
-
4
-
8
3
4
15
77
31.56%
**Income: Commission / Bonus Income Documents Missing/Unclear
-
-
-
-
-
-
-
-
-
-
-
-
-
-
1
0.41%
**Income: Commission / Bonus Income Overstated
-
1
-
-
-
-
-
-
1
-
-
-
-
-
3
1.23%
**Income: Employment History
-
-
-
1
-
-
-
-
-
-
-
-
-
2
4
1.64%
**Income: Employment Type Incorrect
-
-
-
-
1
2
-
-
-
-
-
-
-
1
4
1.64%
**Income: Ineligible income type
-
-
-
-
-
-
-
-
-
-
1
-
-
2
3
1.23%
**Income: Misc.
-
1
-
1
-
1
-
-
-
-
1
1
1
9
15
6.15%
**Income: Rental Income Improperly Calculated/Documented
-
-
-
4
-
-
1
-
-
-
-
1
-
1
7
2.87%
**Income: Salaried Income Documents Missing/Unclear
-
-
-
3
-
1
-
-
-
-
2
-
1
-
11
4.51%
**Income: Salaried Income Overstated
1
-
-
1
-
-
-
-
-
-
-
-
-
-
4
1.64%
**Income: 2106 not deducted
-
1
-
-
-
-
-
-
1
-
1
-
2
-
6
2.46%
**Income: Self Employed Income Documents Missing/Unclear
-
-
-
2
1
1
-
-
1
-
1
1
-
-
9
3.69%
**Income: Self Employed Income overstated
-
-
-
2
-
-
-
-
-
-
1
-
-
-
6
2.46%
**Income: Self Employed mortgages, notes payable in one year not deducted
-
1
-
-
-
-
-
-
1
-
1
-
-
-
4
1.64%
LIABILITIES
-
3
2
4
4
5
4
1
2
-
2
6
3
10
51
20.90%
**Liabilities: Divorce Decree Liabilities not included
-
-
1
1
1
-
-
-
-
-
-
1
-
-
4
1.64%
**Liabilities: Liability's corresponding property not Documented
-
-
-
-
-
1
1
-
-
-
-
2
-
4
8
3.28%
**Liabilities: Misc
-
-
1
-
-
-
1
-
-
-
-
1
-
3
6
2.46%
**Liabilities: Payment / Liability terms not verified
-
-
-
-
-
-
-
-
-
-
1
-
-
-
2
0.82%
**Liabilities: Property not documented as free and clear
-
2
-
1
-
1
-
-
1
-
-
2
3
2
12
4.92%
**Liabilities: Second Lien not Subordinated / Documented as Paid
-
-
-
1
1
1
-
-
-
-
-
-
-
-
3
1.23%
**Liabilities: Soft Pull not within 15 Days of Closing
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
0.00%
**Liabilities: Tax Return/Tax Transcript Liabilities not included
-
-
-
1
1
-
1
1
-
-
-
-
-
-
5
2.05%
**Liabilities: Taxes / Insurance Incorrect or not properly documented
-
1
-
-
-
1
1
-
1
-
1
-
-
1
7
2.87%
**Liabilities: Undisclosed Liability
-
-
-
-
1
1
-
-
-
-
-
-
-
-
4
1.64%
Overall Total
4
16
5
44
9
24
13
8
8
4
26
22
14
34
244
113.93%

<tbody>
</tbody>


The formula that tallies each entry is as follows:

=(SUMPRODUCT(--(ISNUMBER(FIND($E35,Table2[Deficiency Type]))),--(Table2[Meeting Date]=DATE(2013,2,14)), --(ISNUMBER(FIND("Underwriting",Table2[Accountable Party])))))+(SUMPRODUCT(--(ISNUMBER(FIND($E35,Table3[Deficiency Type]))),--(Table3[Meeting Date]=DATE(2013,2,14)), --(ISNUMBER(FIND("Underwriting",Table3[Accountable Party])))))+(SUMPRODUCT(--(ISNUMBER(FIND($E35,Table5[Deficiency Type]))),--(Table5[Meeting Date]=DATE(2013,2,14)), --(ISNUMBER(FIND("Underwriting",Table5[Accountable Party])))))+(SUMPRODUCT(--(ISNUMBER(FIND($E35,Table513[Deficiency Type]))),--(Table513[Meeting Date]=DATE(2013,2,14)), --(ISNUMBER(FIND("Underwriting",Table513[Accountable Party])))))



It is so lengthy because there are three criteria each entry must meet to be tallied (meeting date, accountable department, and deficiency type) and it adds the results from the two different tables of data (loss mitigation and audit). Also, once the issues are resolved they are pulled to a resolved list, but we didn't want the information to fall off the tracking list above, so I had to include that in the formula as well. Essentially, the formula is tallying based on three criteria for four different tables and adding all the results together.

I know that there may have been an easier way to do this possibly using the countifs function, but I wasn't able to get it working, and as I'm sure you can tell as this point I am an excel novice.

Sorry for the lengthy post, any help is greatly appreciated.





***EDIT - Also I forgot the important part. So what management would like to do (I'm not entirely sure its possible with the way these items are being tallied) is to hover over one of the entries in the table, ie **Assets: Insufficient Assets Verified identified on March 7, and see the nine loan numbers from the 9 errors tallied in that cell.
 
Last edited:

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
I can see why you're losing your mind, with formulae like that! Difficult to maintain, and nigh on impossible (here's where someone will show us different - impossible is always a suitable word for someone to take the bait…) to get what you want (the hovering thing) without a macro.
I'm working on creating a pivot table from your data and will post something soon.
 
Last edited:

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
In the linked-to spreadsheet below, there is a sheet (Sheet7) containing just a paste of your 2 tables in msg#6 above, minus table 2's header row (the headers are very similar). (I'm in the UK so I had to tweak the dates in column A to DMY - but now that these dates are real excel dates the file should work in all locales.)
There are 3 buttons on the sheet (but you should only really click the first one on this sheet - the other two are copied to the new sheet for use there). The topmost, Expand to new sheet, adds a new sheet to the workbook and expands the data to be more like a database - your column Deficiency Type contains multiple Categories and subcategories, and it's these that are split to multiple lines.
This new sheet has 2 buttons on it, the first, Create Pivot Table, does just that. You don't need a macro to do this, it's easy to do it manually - I just set up an example pivot table, close to what I think you want.
The second button, Toggle GRIs, expands and collapses all the GRI numbers in the pivot table. You don't need a button to do this, there is a little + sign on a miniature button next to each Deficiency detail in the pivot table which you can click with the mouse to show/hide the GRI numbers.
In addition, if you see a (big) number you're interested to see how it's made up, double click on that number and a new sheet will be added showing only the lines from which that data is created; this is standard pivot table stuff called drill down and that new sheet can be safely deleted.

Some points.
I haven't yet addded a percentage column.
You won't see Assets as there weren't any in either of those two tables.
On a given sheet, you can click the Create Pivot Table button multiple times to get additional tables to play with.
It shouldn't be difficult to incorporate all your tables, with or without a macro.
Pivot tables are extremely good at summarising data and you might be surpised at how quickly you can get useful information from them and how quickly you can change them.
There's a Sheet7 (2) in the workbook which is just a small pivottable - delete at will.
I note from your formula that you're only interested in rows where there is "Underwriting". I suspect this will only include rows where the Accountable Party is Underwriting on its own, however I made the pivot show details of rows where the Accountable Party has the word "Underwriting" contained anywhere within it. To adjust this manually, at the top of the pivot table you'll see Accountable Party and next to it (MultipleItems) with a dropdown arrow. Click this and have just Underwriting selected.
If you do the above, there won't be much data to see (only a couple of rows), you can, if you want, arrange for items with no data still to be displayed, for columns and rows independently.

Is this any use?
<embed src="https://www.box.com/embed/7ad3ty20axba3zg.swf" wmode="opaque" type="application/x-shockwave-flash" allowfullscreen="true" allowscriptaccess="always" height="400" width="466">
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,068
Messages
5,466,412
Members
406,480
Latest member
Leecorn

This Week's Hot Topics

Top