Indirect + cell("address"... stopped working (?)

[Camel123]

Hi,

Using the following function among others for my searchable drop down lists:
=IF(ISNUMBER(SEARCH(INDIRECT(CELL("address";Forecast!$F$5:$F$1048576));'Master data'!C2));MAX($D$1:D1)+1;0)

For some reason it stopped working. I have no previous versions. 'Forecast'!$F$5:$F$1048576 I can see changed to Forecast!$F$5:$F$1048576 and is not colored anymore in the formula field. The formula works only when entering something on F5 in the forecast sheet.

Pretty urgent to solve this and I would really appreciate your support, what is the problem and how do I solve it?

 

[tyija1995]

CELL("address";*insert range here*) returns the address of the first cell in the range I believe, are you copying this formula down?

 

[Camel123]

CELL("address";*insert range here*) returns the address of the first cell in the range I believe, are you copying this formula down?

Yes, I am! I removed the range and it worked, so there is no way of referring to only one range (F column in this case)?

 

[tyija1995]

Yes, I am! I removed the range and it worked, so there is no way of referring to only one range (F column in this case)?

I believe if you are using
IF(ISNUMBER(SEARCH(INDIRECT(CELL("address";Forecast!$F$5:$F$1048576));'Master data'!C2));MAX($D$1:D1)+1;0)

It is the same as:
IF(ISNUMBER(SEARCH(INDIRECT(CELL("address";Forecast!$F$5));'Master data'!C2));MAX($D$1:D1)+1;0)

Regardless of whether you drag it down or not, do you need it to go through the F column as you drag, so it will see F5 then F6 then F7 etc etc...?

IF(ISNUMBER(SEARCH(INDIRECT(CELL("address";Forecast!$F5));'Master data'!C2));MAX($D$1:D1)+1;0)

Should do that.

I assume you are checking the F5 value in row 5, F6 in row 6, ... F1000 in row 1000 etc?