Understand Indirect + Cell function combination

[Camel123]

Hi,

I watched a tutorial and tweaked my formula to make drop down lists in all cells searchable, rather than just 1x searchable drop list in cell F5 on the Forecast sheet. I changed the formula from,

=IF(ISNUMBER(SEARCH('Forecast'!$F$5;'Master data'!C2));MAX($D$1:D1)+1;0)

to

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

I am trying to understand what the INDIRECT and CELL function does here and I tried reading the internet but I am apparently not smart enough to understand it. Can anyone give an easy explanation to this?

 

[RoryA]

CELL("address")
returns the address of the last changed cell as text - e.g. "C3".

INDIRECT takes the address of a cell (or multiple cells) and returns an actual reference to that cell. So INDIRECT(CELL("address")) always returns a reference to the last changed cell.

 

[Camel123]

More clear, thank you mate. Now when I understand it I referred the indirect+cell combination to one column. Though, there is a problem. Let's say if I pick "McDonalds" from the drop-list in cell C3, then the drop-list in cell C4 only display's "McDonalds" since the dynamic range refers to the last changed cell. But since C4 is blank I would like it to show the whole range/list of values. How do I make it work this way? At the moment after picking McDonalds in the previous cell, I need to write "star" to generate values containing "starbucks", "star's café" etc. But it is not always that I know which value I am searching for.