Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Validating last cell in varying length columns (Excel 2K, Win 2K)

    Dear All

    I have a spreadsheet that is populated with a varying dataset. The data is related to delivery performance and one of the cells validates the average performance and the most recent delivery. All worked well until we started to use some new suppliers, as they do not have 12 months of history the 'last delivery cell' is empty......

    How do I persuade Excel to look for the last populated cell and validate that??

    The attachment is the spreadsheet in question.

    Thanks

    Ian

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

    Re: Validating last cell in varying length columns (Excel 2K, Win 2K)

    Your spreadsheet doesn't contain any data at all. What exactly do you mean by "last delivery cell"?

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validating last cell in varying length columns (Excel 2K, Win 2K)

    Apologies Hans, the spreadsheet is the template. The cell D32 has the validation criteria in it, this criteria is applied to two cells (I29 and I30), the problem is that where the data used to populate the spreadsheet has less than 12 records (a new supplier) I29 is not populated, this means the criteria defaults to a 'fail' for the supplier. What I would like to be able to do is get Excel to check that I29 is not null, if I29 is null then work up column I until it finds the first populated cell and apply the validation to this cell.

    I have a vague idea what the VBA would need, but no idea at all how to make it work with a spreadsheet populated automatically.

    Thanks

    Ian

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

    Re: Validating last cell in varying length columns (Excel 2K, Win 2K)

    Try this formula in D32:

    =IF(LOOKUP(9.99999999999999E+307,I18:I29)<0.8,"Yes ",IF(I30<0.75,"Yes","No"))

    9.99999999999999E+307 is the largest number Excel can handle. This value obviously won't be found, so LOOKUP returns the last non-blank value in I18:I29.

Posting Permissions

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