Hi,
I need some help on a formula that will return the sheet name if a certain cell within other sheets is occupied, aswell as the same cell within the first sheet.
I do struggle to explain myself at times, so I have linked to a picture of my worksheet which has been simplified for ease. I am after a formula to replicate the manual text in column F.
https://imgur.com/a/j2IfLuS
This is for a sport and we have 4 players, but only 2 players play a week. Each player has a sheet, named here as player 1 to player 4. I am trying to auto populate the F columns based on if that player has played (shown by E being occupied) and finding the other occupied column E'S from other sheets. Obviously, I do not need the formula to return anything unless the E column is occupied.
So, on week 1, player 1 & player 3 both play, I need player 1's F4 cell to return 'Player 3'. Likewise, player 3's F4 cell needs to return 'Player 1'. F4 should stay blank for players 2 & 4. If it makes things easier, The player name can be taken from the sheet name or from A2.
I have searched for weeks and found very little, can anyone help with this formula? I assume that once I have a basic formula, I should be able to amend it to suit every individual player and circumstance.
Thanks for any input!
I need some help on a formula that will return the sheet name if a certain cell within other sheets is occupied, aswell as the same cell within the first sheet.
I do struggle to explain myself at times, so I have linked to a picture of my worksheet which has been simplified for ease. I am after a formula to replicate the manual text in column F.
https://imgur.com/a/j2IfLuS
This is for a sport and we have 4 players, but only 2 players play a week. Each player has a sheet, named here as player 1 to player 4. I am trying to auto populate the F columns based on if that player has played (shown by E being occupied) and finding the other occupied column E'S from other sheets. Obviously, I do not need the formula to return anything unless the E column is occupied.
So, on week 1, player 1 & player 3 both play, I need player 1's F4 cell to return 'Player 3'. Likewise, player 3's F4 cell needs to return 'Player 1'. F4 should stay blank for players 2 & 4. If it makes things easier, The player name can be taken from the sheet name or from A2.
I have searched for weeks and found very little, can anyone help with this formula? I assume that once I have a basic formula, I should be able to amend it to suit every individual player and circumstance.
Thanks for any input!
