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

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote