Finally got to trying this tonight before bedtime. And VOILA!! I pulled a new fresh export, opened it and used CTRL-A/CTRL-C to copy it all to the clipboard. I opened the US version of your spreadsheet which we were using this afternoon, right-clicked within the My Loans tab, chose Paste Special, and left the defaults as presented (Paste: All, Operation: None were the only two radio buttons selected). It worked.
[snip]
I declare complete success... at least enough so I can go to bed now, LOL! Well done, sir, and now I will have FUN exploring my portfolio again!!
(1) My 3 most recent loans marked PAID BACK under status column S, have a "1" in the "Projected finish" column K rather than "Finished". One of them was projected to end Nov. 21, and two Nov. 26. All three are paid off. You might want to arrange it so if the PAID BACK designator is there, column K switched to "Finished". I don't know why they should still think there'll be paying off within the month, when all 3 dates have passed and they've already been marked PAID BACK.
(2) I thought of another sort I would dearly love to do on the data, but cannot now because of the column separations. Maybe you have hidden columns that I could use? What I want to do is sort all my loans by anniversary day-of-month, regardless of actual month or year. That is, I want all the loans scheduled to make a payment on the 27th of the month to sort together, followed by all schedlued to pay on a 28th, etc. I like to look and see which loans should be "active" in some way today, or the next day. And that comes irrespective of which month it is, so only the day-of-month value would be needed for that sort. Any way to do that with the data that's already there?
This is really fab, thanks SO much for the work you've put into it!! I'm glad I could help debug somewhat.
Diane, Very happy to hear the original (US) version worked for you when redoing a cut and paste special.
Will have a look at when it highlights a finished - I suspect it is due to rounding and days of month (30 v 31) but I could just get it to report Finished if Paid Back is the status.
I think it would be possible to break the date down so we can order and filter by the day of the month so that you have a similar summary as to the months due for days. Might be a bit strange as it might report something due on the 31st in a month with only 30 or 28 days.
Have also worked out how to handle refunds so will update that - anyone had a defaulted loan and willing to share their data?
Anyone up for further testing tonight in "Debugging hell"?
Cheers
C
