When certain combination of cells have data flag up in another cell

[BigMcBen]

So we have 7 members of staff who update their absences on a spreadsheet but certain staff can't be off at the same time, so I want to find a way that, if certain cells have something in them it flags up in another cell

So I have A1, B1, C1, D1, E1, F1 and G1. If B1 and D1 both have something in them then J1 states Bob & Keith Off, if A1, D1 and G1 have something in those cells then K1 states Anne, Keith and Rueben Off.

If someone can give me the building block of the formulas then I can change them depending on the combination of the cells, the destination cell and the names.

Thanks in advance

[6StringJazzer]

It's almost always better to provide your actual file to get an actual solution, but since you asked for a conceptual solution, here is one.

Values as displayed

	A	B	C	D	E	F
1	Bob	Keith	Fred	Anne	Janice
2	x		x			Bob, Fred, Off

Underlying formulas

	A	B	C	D	E	F
1	Bob	Keith	Fred	Anne	Janice
2	x		x			=IF(A2<>"",A1&", ","")&IF(B2<>"",B1&", ","")&IF(C2<>"",C1&", ","")&IF(D2<>"",D1&", ","")&IF(E2<>"",E1&", ","")&IF(COUNTA(A2:E2)>1," Off")

[BigMcBen]

Thanks very much for your reply. I’ve taken what you suggested and tweaked it to meet the various permutations of staff but when one of the two cells has info in it, that name is displayed in the cell, what I need is only when both cells have data in them should both names appear in the cell. Can you advise how I would amend this so that happens?

=IF(E89<>"",E1&" & ","")&IF(H89<>"",H1&" ","")

Also when I’m trying to fill downwards, the E89 and H89 change to the next row but the E1 and H1 increase by one as well. Is there a way that you can downward fill so that only the row number updates?

Thanks again.

[6StringJazzer]

So I have A1, B1, C1, D1, E1, F1 and G1

What do you mean "both"?

I'm not sure what you mean by permutations of staff. I provided one formula to cover everything.

When filling downward, if you want to continue to refer to row 1 use $1. This is the most fundamental feature of Excel.

You asked for building blocks for formulas. I fear we will go back and forth as you add more requirements. If you really need a complete solution, you need to provide a sample file.

[Marvo]

Thanks very much for your reply. I’ve taken what you suggested and tweaked it to meet the various permutations of staff but when one of the two cells has info in it, that name is displayed in the cell, what I need is only when both cells have data in them should both names appear in the cell. Can you advise how I would amend this so that happens?

=IF(E89<>"",E1&" & ","")&IF(H89<>"",H1&" ","")

Also when I’m trying to fill downwards, the E89 and H89 change to the next row but the E1 and H1 increase by one as well. Is there a way that you can downward fill so that only the row number updates?

Thanks again.

I don't know much but I do know if you put a dollar sign between E1 & H1 so, E$1 & H$1 it wont change.