Data Validation - Indirect formula quit working

[melodramatic]

I have a data validation formula that has worked non-stop, and overnight, all of a sudden, it doesn't work. I keep the sheet locked, so no users can change it, but when I was told this morning the drop-down list was blank, I went in to try to re-build the formula, and am getting errors.

The set-up: I want a drop-down list of all current employees, but want to make sure the list doesn't expand past the current # (in row M). Thus, with a list starting at cell M6, I have a calculation in cell M2 that shows what row contains the last employee (currently at 76).

In the data validation for my table, the formula I have written is:

=indirect("$M$6:$M$"&$M$2)

When I click on OK, I get "The Source currently evaluates to an error. Do you want to continue?

Why am I getting an error, when I never had one before? There are no errors anywhere in the list that is created by the indirect statement, and nothing else that I can see as a problem.

Help?

 

[Toadstool]

Hi Melodramatic,

I can't diagnose without seeing your sheet but this OFFSET should give the result you seek:

Melodramatic.xlsx
	M	N	O
1			LoV
2	76
3
4
5
6	Fred
7	Bert
8	John
9	Sue
10	Bill
11
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
O2	List	=OFFSET($M$6,,,$M$2)

 

[melodramatic]

The change from indirect to offset in the data validation worked perfectly. Thank you!

 

[Toadstool]

You're welcome!