Please I need help with a formula and have tried to outline what Im trying to do below

Hobo

Thanks!!!!!

isabonita

Hi @Hobo

You can do this with a JOIN COLLECT...

=JOIN(COLLECT([B - Official 1]@row:[D - Official 3]@row), ",")

Let me know if you end up with extra commas.. and I can help get rid of those, too.

If you want each person on a new line - rather than comma-separated - you just use the CHAR(10) for the separator (no quotes around it) rather than the comma.

Hobo

No this didn't work at all in sheet 2 the list of officials is already pre defined and what i'm trying to do is add what venue they have been allocated to in the previous sheet 1. Some officials in the predefined sheet 2 list may not be allocated at all

Paul Newcome

I would insert a multi-select dropdown into sheet 1 and then use this formula:

=JOIN(COLLECT([B - Official 1]@row:[D - Official 3]@row, [B - Official 1]@row:[D - Official 3]@row, @cell <> ""), CHAR(10))

Then in sheet 2 you can use something like this:

=IFERROR(INDEX(COLLECT({Sheet 1 Venue Column}, {Sheet 1 Helper Column}, HAS(@cell, [B - Official]@row)), 1), "")