Reading last non-blank cell in each row at 3 column steps

**david55thomas** · 09-08-2009, 09:04 PM

I have rows of registered people using my charity and I record donations in a cell of their particular row, then calculated tax relief, then in the next column the donation date.

SMITH / ADDRESS etc / £50.00 / tax 12.50 / 07.09.2009 / NULL / NULL / NULL etc
JONES / ADDRESS etc / £20.00 / tax 5.00 / 01.09.2009 / £35.00 / tax 8.75 / 06.09.2009 / NULL / NULL / NULL / etc

I have a series of empty cells awaiting completion with this data as each person makes another donation.

I acknowledge each donation by letter in Word which accesses this data in Mailmerge.

I need to have a fixed reference cell at then end of each row, say at row ZZ which has a formula to identify the LATEST donation and insert the value of the donated sum on that date. The info in cell ZZ (latest donated sum) is inserted into the Word document.

I've thought about VLOOKUP, COUNT, etc but can't make these work in my head.

Any ideas please?

**JBeaucaire** · 09-08-2009, 09:54 PM

Put this formula in ZZ1 and copy it down:

=INDEX(A1:ZY1, MATCH(LOOKUP(2, 1 / (A1:ZY1<>"") - 2, A1:ZY1),(A1:ZY1), 0) - 2)

It works by finding the last cell with a value in it and returning the value from the cell two cells to the left, so the tax and date need to be there for every entry.

**Ron Coderre** · 09-08-2009, 09:59 PM

Here's the set-up:
Row_1 contains headings

Please Login or Register  to view this content.

Row_2 contains the values under the headings

This formula returns the last "Amt" in Row_2

Please Login or Register  to view this content.

Is that something you can work with?

**david55thomas** · 09-09-2009, 09:06 PM

That's brilliant ! I just had to tweak the last -2 to read -5 and I found the cell content I needed.

One more thing - how do I stop the result in ZZ reading #N/A when nothing is found?

Many thanks

**teylyn** · 09-09-2009, 09:26 PM

Hi David,

attached is my version of a solution with the formula

=OFFSET(D2,0,COUNTA($D2:$K2)-2)

You mentioned that you want to use the information to do a mail merge thank you for each donation. This data layout is not the best solution for that. In my attached workbook, please have a look at sheet 2 and sheet 3.

Sheet 2 is a list of your donors and their addresses
Sheet 3 is a list of donations, with one donation per line. It looks up the address from sheet 2 so you can use sheet 3 for the mail merge and also note for each donation whether a thank you note has been sent.

In your current layout it is quite difficult to keep track of which donations have been acknowledged and which have not. Also, as soon as you start gathering data, you'll soon want to run reports like, how many donations per donor, donations per month and other statistics like that. The table format as in my sheet 3 can easily be used for filtering and sorting or as the basis for a pivot table.

hope that helps.

cheers