I'm trying to create a drop down list in Excel that updates automatically as more data is added to the source data column. I do this by looking for the last cell that isn't blank with the following formula.
=CELL("address",INDEX(A:A,MATCH(LOOKUP(2,1/(A:A<>""),A:A),A:A,0)))
Using this formula in a cell of the spreadsheet itself works perfect, but when I try to use it to create a list in the Data Validation menu I get the following:
Any ideas of where how to get this to work?
Any help is appreciated. Thank you!
Edit:
As BigBen suggested, I tried removing CELL("address", ) from my formula leaving just index and what's inside, but I get the following error:
