1. ## Last entry (2003)

I'm trying to return the last (date) entry in a column from another worksheet, but I'm getting a "error in value" error
.
=INDEX('Avg Fuel Cost - CA'!A5:A1000,MAX((ROW('Avg Fuel Cost - CA'!A5:A1000)-4)*('Avg Fuel Cost - CA'!A5:A1000<>0)))

2. ## Re: Last entry (2003)

It should be entered as an array formula, i.e. confirm with Ctrl+Shift+Enter instead of just Enter.

3. ## Re: Last entry (2003)

Ooops!!
Thanks Hans!

This works for dates because they're progressive, but I also need to to this for column C (dollars) so "MAX" isn't going to return the last entry in the column; only the highest value in the column. How can I do this?

EDIT: My mistake, it does work with dollars, but not a lookup entry (i.e. Column E contains:
=IF(ISBLANK(C7),"",VLOOKUP(C7+0.000000001,FSCscale s!\$A\$3:\$C\$40,3))

4. ## Re: Last entry (2003)

What exactly is the problem with the lookup?

5. ## Re: Last entry (2003)

The lookup works fine, but the array won't return the results. Probably should just attach the spreadsheet; it should open in the cell with the problem (no error, but no results either).

6. ## Re: Last entry (2003)

The formula works correctly, it just doesn't do what you want - because you haven't told it to do what you want. Cells E8 through E300 on the 'AVG Fuel Cost - CA' sheet contain empty strings "" as the result of a formula. An empty string "" is not the same as 0, so the 'Avg Fuel Cost - CA'!E5:E1000<>0 part in your formula evaluates to TRUE for all those cells. Change it to 'Avg Fuel Cost - CA'!E5:E1000<>"":

=INDEX('Avg Fuel Cost - CA'!E5:E1000,MAX((ROW('Avg Fuel Cost - CA'!E5:E1000)-4)*('Avg Fuel Cost - CA'!E5:E1000<>"")))

7. ## Re: Last entry (2003)

You mean I have to actually TELL it what I want? What good is a computer if it can't just GUESS what I want?

Thanks friend, I didn't even know about the difference between an empty string and 0.
All is well!!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•