Results 1 to 7 of 7
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    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)))
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Last entry (2003)

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

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    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))
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Last entry (2003)

    What exactly is the problem with the lookup?

  5. #5
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    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).
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    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!!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Posting Permissions

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