using logic to copy a cell

Question

Hi, I'm trying to use logic to automate a team table for a sports club and I understand how logic works but not how you "program it" in excel.

Quite simply I'd like the player name from the list on the left to copy down into the subs list if the cell in the games section is blank (or could be if it is zero, if that would make it simpler...

So for example

if sum cell c8 (player 5 in game 1 first half = zero then copy cell b8 to c15

I just don't know how to do this with excel logic.

I hope you can help and thank you in Advance.

Answer 1

The only difference is that I hardcoded the 4 instead of using ROW($C$4) - so if you insert a row above C4 then mine will break.

Answer 2

Hi Bernie,

I used the first response and it worked.

whats the difference between the 2?

I'd  like to be able to add a list of all the players and then assign them a team which breaks them out into separate sheets with substitutes automated.

Thanks for the help

Answer 3

Thank you worked a treat!

Answer 4

In cell C15, array-enter (enter using Ctrl-Shift-Enter) the formula

=INDEX($B:$B,LARGE(IF(C$4:C$12="",ROW(C$4:C$12)),4-ROW(A1)))

and copy down to C16:C17, then copy C15:C17 across to D15:whatver17)

Answer 5

Put following Array Formula in C15 and drag right and down -

=IFERROR(INDEX($B$4:$B$12,SMALL(IF(C$4:C$12="",ROW($C$4:$C$12)-ROW($C$4)+1),ROWS($1:1))),"")

Note - Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.