Sorting Master Database & Tracking Contact Dates

**adawg** · 04-18-2012, 04:12 PM

If anyone can help, I have a few other things I need help on with this data:

1) Is there a quick way to sort the Master Database Sheet by (a) Maturity Date and/or (b) Issue Date? As loans are maturing I would like to contact owners. Also, as my database expands I would like to reach out to people with new loans.

2) I have several columns on Sheets A, B, & C that I use to track contact dates. I usually fill this in, color coat, and then have it auto filled into last contact column. Does anyone have suggestions on a better way to track my contact attempts?

I also have some comments filled in the excel where I’m having issues. I picture the Master Database Sheet as just a way for me to keep track of each individual database. I will then go into each database and edit/fill/log-in information on each loan.

Thanks,
A-dawg

Original Post: http://www.excelforum.com/excel-gene...html?p=2762805

Attachment: Edited Loan Master Database 4.17.12.xlsx

**Pete_UK** · 04-18-2012, 05:45 PM

Hello again,

a simple way of getting rid of those zeroes is to actually put a single space in a cell in sheets A, B or C instead of leaving it blank. However, if you want a formula solution then you basically want to do:

if(cell="","",cell)

Translating this to M9 of your Master sheet, for example, you could change the formula to this:

=IF(OR($C9="",$C9="-"),"",IF(E9="A",IF(INDEX(A!P:P,RIGHT(C9,LEN(C9)-2))="","",INDEX(A!P:P,RIGHT(C9,LEN(C9)-2))),IF(E9="B",INDEX(B!K:K,RIGHT(C9,LEN(C9)-2)),"-")))

You'll notice that for the part that gets the data from sheet A I've inserted another IF along the lines given above, but I've not changed the B part.

For the cell P9, where you had ??? showing, you could change the formula to this:

=IF(OR($C9="",$C9="-"),"",IF($E9="A",IF(INDEX(A!O:O,RIGHT($C9,LEN($C9)-2))="???","",INDEX(A!O:O,RIGHT($C9,LEN($C9)-2))),IF($E9="B",INDEX(B!N:N,RIGHT($C9,LEN($C9)-2)),INDEX('C'!O:O,RIGHT($C9,LEN($C9)-2)))))

Again, I've only shown the changes necessary for getting the data from sheet A - you will need to make similar changes for the other INDEX expressions which get the data from sheets B and C. In Q9 you can do this:

=IF(P9="","",DAYS360($Q$2,P9))

to avoid the error message. If, as I suggested above, you put single spaces in your source sheets rather than change all the formulae, then that last one will have to become:

=IF(P9=" ","",DAYS360($Q$2,P9))

Hopefully from this commentary you'll be able to work out what to do for the other formulae.

As regards sorting the master sheet, this can be done by formulae. I would suggest you use one of the cells in the top row (eg M1 at present) to contain a drop-down that would enable you to select the sort order, i.e. by Maturity Date, by Issue Date (and even "unsorted"). You would need to use some helper columns in the master sheet in order to extract the relevant data and then sort the pointers to the records in accordance with the sort order chosen in M1, and you might need other helper columns in the individual sheets. That is quite an undertaking, so I'm not sure if anyone would be prepared to take it on here. A simple way for you to accomplish it might be to have another sheet into which you can copy and paste special/values from the master sheet, and then to manually sort that copy of the data in a form that you wish.

Hope this helps.

Pete