Need a macro or formula to return the value below the looked up value

[NICBOOTH]

Starting an empty cell I would like to lookup the value in the cell above, in a different worksheet in the same workbook, and then return the value below this, but only when the cell in the column next to the looked up value is empty.
Ideally I would like this to then move down a cell and keep filling until the conditions arent met- but even one cell at a time would help.
Have tried lots of things but not sure where I'm going wrong......
Thanks
nic

[Sindhus]

Hi Nicbooth, Welcome to the forum!

Can you upload a sample workbook?

[boothy]

File attached as requested. some specific information....
in sheet ADT, the information in C17 is used to find the data to go in C18, by looking up in UNITS. The value that should end up in C18 should be the value below the looked up value

[Sindhus]

What should be looked in "Unit" sheet. And as per your previous mail, if tasks is not empty, it should look in Units sheet rite?

[boothy]

Sorry, have attached new version of sheet, there is an error in previous version.
In sheet 'ADT', to find a value for C18, take the value in C17, look it up in sheet 'Units', in this case that will return cell D173, then look at the value in C174, if its empty then return D174 to cell C18 in 'ADT' and then repeat the process on cell C18 to get a value for C19, if it has a value in C174 then stop.

[Sindhus]

Hi Check the formula in ADT sheet. Hope that helps.

[boothy]

Thanks Sindhus. It does what I asked, much appreciated, but I think I may have asked the wrong question...
So I have reposted as a new thread... Thanks again...