Results 1 to 13 of 13
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Last Date Data Is Entered (2000)

    I have a table of data that I enter each weekday, but not Saturdays, Sundays or holidays. I am trying to find a function that will return the last day data has been entered. For example, I have the month of July formatted out in a worksheet, days 1-31 entered in B2:AF2. I have data entered data for the 1st through the 3rd in cells B33 and F3, but not in E3 (i. e., a holiday) or G3:H3, i. e., Saturday and Sunday. Instead, in cells E3 and G3:H3, I have "N/A". I would like a formula, say in AE2, that would tell me the last day data has been entered, but not a "N/A". In the instant case, the formula in cell AE2 would return the value in cell F2 since this is the last day numerical (as opposed to "N/A") data has been entered.
    Any thoughts?
    Thanks,
    Jeff

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

    Re: Last Date Data Is Entered (2000)

    Try the following formula, entered as an array formula (confirm with Ctrl+Shift+Enter):

    =MAX(IF(ISNUMBER(B3:AF3),B2:AF2,""))

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Last Date Data Is Entered (2000)

    Doesn't seem to work for me. In my table, I have data entered in cells B33, and F3, corresponding to the 1st-3rd, and 5th of July. Your formula seems to return a "4" for me, that would in turn reference column E (I think!), and that in turn would correspond to the 4th (i. e., in E2), where I have a "N/A" entered in cell E3!
    Any other thoughts?
    Thanks,
    Jeff

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

    Re: Last Date Data Is Entered (2000)

    I had taken your remark "days 1-31 entered in B2:AF2" literally. See attached spreadsheet.
    Attached Files Attached Files

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Last Date Data Is Entered (2000)

    Well, I must not have been holding my mouth the right way! LOL!!!
    Anyway, when I tried the formula again for the last date, it worked. However, when I tried to insert it and get the data that corresponds to the last date, it returned instead the first date's data.
    I have attached my worksheet.
    Any ideas?
    Thanks again,
    Jeff
    Attached Files Attached Files

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

    Re: Last Date Data Is Entered (2000)

    Originally, you asked for the last day data had been entered. Now you want the data entered on the last day.
    See attached spreadsheet again.
    Attached Files Attached Files

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Last Date Data Is Entered (2000)

    Hans,
    That's right. Originally I asked for the last date. However, in the meantime, I realized I need the last data as well. I erroneously assumed I could plug in your solution for the last date to get the last data as well, but to no avail. Anyway, thanks-it works fine!
    Regards,
    Jeff

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Last Date Data Is Entered (2000)

    Hans,
    Just for my education-what does putting the respective formulas in an array-type format do, as opposed to another way? The reason I ask is that I know absolutely zero about arrays!
    Thanks,
    Jeff

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

    Re: Last Date Data Is Entered (2000)

    Jeff,

    Array formulas enable you - among other things - to do a calculation in one formula that would otherwise take a whole lot of intermediate cells.

    Have a look at the links provided in the replies to <!post=post 123094,123094>post 123094<!/post>. Chip Pearson and Bob Umlas explain it far better than I ever could. You can also do a search for "array formula" (entire phrase) on this Forum.

    Regards,
    Hans

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Last Date Data Is Entered (2000)

    Thanks again, Hans.

  11. #11
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last Date Data Is Entered (2000)

    Jeff,

    Is the N/A you mention just N/A, a text value, or #N/A, the error value, we have in Excel?

    Aladin
    Microsoft MVP - Excel

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Last Date Data Is Entered (2000)

    Aladin,
    It is a text entry I make (or will make) manually. Does that make a difference?
    Thanks,
    Jeff

  13. #13
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last Date Data Is Entered (2000)

    Yes, it makes lots of diiference Jeff. #N/A would make impossible to use the following efficient formula, while N/A as text entry is just OK:

    =OFFSET(A4,0,MATCH(9.99999999999999E+307,A4:AF4)-1,1,1)

    will give you the last (non-N/A) entry from A4:AF4 (see the attachment ).

    Note that this is not an array-formula.

    Aladin
    Attached Files Attached Files
    Microsoft MVP - Excel

Posting Permissions

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