I am trying to manage movement of equipment. I am looking for a formula that can show where the current location is. I have tried to use the IF function but once I go beyond 2 choices, it returns a 0 value.



Can anyone assist or provide some alternative formula.

Regards

Attached Files

Book2.xlsx (12.5 KB, 9 views)

Hi Brian..Can u pls give me a brief explanation of ur issue, so i can give u the exact result.
thanks..

Seems to work OK for me( see attached)

The logic of your formula is a bit weird. If H3="" and none of the other conditions is satisfied, return H3?

Also the +signs before the cell ref are not necessary

Attached Files

Book2.xlsx (12.2 KB, 13 views)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

What I want the spreadsheet to do is identify the current location J3. As equipment is moved to new locations, the new location is inserted into the cell.
For example at the start of the year, the equipment was in Loc A (cell D3), it was then moved to locations as indicated by cells E3, F3 G3 etc
The spreadsheet when populated will have a few hundred rows, some items will not have moved some will have moved once while some will have moved numerous times. This is the reason for column J, ie the current location
So my formula should read.
What is in cell H3 -
IF H3 is blank then what is in G3
IF G3 is blank then what is in F3 etc.

J3 currently shows Loc E which is correct
IF I blank out H3 then J3 will show Loc D which is correct
If I blank out G3 then J3 shows 0 (This where my problem is as the answer should be Loc C)

Can you advise??

Attached Files

Copy of Book2.xlsx (12.3 KB, 7 views)

There is a flaw in your logic

=IF(H3="";G3;IF(G3="";F3;IF(F3="";E3;IF(E3="";D3;I F(D3="";55;H3))))) always returns the value of G3 when H3 is empty, the value of G3 being LocD or 0.
If I understand correctly you need the content of the last non empty cell for a given SN

Try

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thanks, much appreciated.

If this answered your question please mark it " solved" - (under thread tools) - Thx

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post