dynamic list based on cell value

[sedwardson]

I have a column of names and another to show if they have had work done on their computer. If work has been completed there will be some text in the cell, if not it will be empty. I would like a list that shows the systems that are outstanding and when I put an x in the cell next to the persons name the dynamic list removes their name so I know who is left. I hope this makes sense.
The table would look something like this:

Name	Work Completed	Users Left
Person 1	Computer and laptop	Person 2
Person 2		Person 3
Person 3
Person 4	x
Person 5	x

Thanks for all and any help

 

[Joe4]

Which version of Excel are you using for this one? You have both 2021 and 365 listed.

Excel 365 has a new Filter function that would do this perfectly.
See: FILTER function - Microsoft Support.

However, even in the older versions of Excel, the old Filter functionality should give you what you want.

 

[sedwardson]

Which version of Excel are you using for this one? You have both 2021 and 365 listed.

Excel 365 has a new Filter function that would do this perfectly.
See: FILTER function - Microsoft Support.

However, even in the older versions of Excel, the old Filter functionality should give you what you want.

Running 365, I'll see why it says both. Thanks for the link. I'll have a look and see where I get to
Sam

 

[Joe4]

Formula for cell D2:

=FILTER(A2:A6,B2:B6="")

 

[sedwardson]

View attachment 107697

Formula for cell D2:

=FILTER(A2:A6,B2:B6="")

Hi @Joe4 , Thanks for the reply. That worked ! thank you. It has presented another issue though. I didn't take into account that there are some gaps in my Name column (For reasons I won't go into, these have to stay). The formula works but where there is a gap, it results in a "0". Is there a way for the formula to ignore blank cells? Sorry for the extension of the question !

Kind regards

Sam

 

[Joe4]

Just add another condition to check to see that column A is not blank, i.e.

=FILTER(A2:A6,(A2:A6<>"")*(B2:B6=""))

 

[sedwardson]

Thats fantastic @Joe4 ! Thanks

 

[Joe4]

You are welcome.
Glad I was able to help!