Join collect

Erin W

The formula works on my test sheet when I refer to columns as A, B, C etc. when I use the actual column names on my in production sheet the formula is not working - please help!!!

This works -

=JOIN(COLLECT(A@row:F@row, A@row:F@row, NOT(ISBLANK(@cell))), " - ")

These don't

=JOIN(COLLECT([BASEMENT SOUTH-NORTH]@row:[2ND FLOOR SOUTH]@row, [2ND FLOOR SOUTH]@row:[14]@row, NOT(ISBLANK(@cell))), " - ")

=JOIN(COLLECT([7]@row:[12]@row, [7]@row:[12]@row, NOT(ISBLANK(@cell))), " - ")

Columns -

Room # for Report

Drop downs columns for each region of the building.

many thanks EW

Jason Tarpinian

It works for me when the range & criterion range are the same for COLLECT:

=JOIN(COLLECT([BASEMENT SOUTH-NORTH]@row:[2ND FLOOR SOUTH]@row, [BASEMENT SOUTH-NORTH]@row:[2ND FLOOR SOUTH]@row, NOT(ISBLANK(@cell))), " - ")

And I can't see anything wrong with your second formula that isn't working - appears to be working on my test sheet

Erin W

thank you - trying again - must be something on the sheet that isn't matching. thank you thank you!

Erin W

I must have been missing a comma or something because it is working - Many thanks!! EW