Copying down question

[Mel Smith]

Hello folks,

I have a large spreadsheet with player's details on three rows each. i.e. player one's figures are on rows 3, 4 and 5 and then the next player's figures on rows 6, 7 and 8 etc.

The contents of each cell are "formula filled" e.g. =IF('First Class Records'!CS3=0,"",'First Class Records'!CS3) on the first row,
with the second row: =IF('List A Records'!BV3=0,"",'List A Records'!BV3)
and the third row: =IF('T20 Records'!BV3=0,"",'T20 Records'!BV3)

I would like to copy these rows down (there are 60 players) so that each row above increments by +1 every third row rather than copy a row at a time and then have to edit each row. Is this at all possible?

Mel

 

[jasonb75]

Try this in the first row and fill down

=IFERROR(1/(1/INDEX(('First Class Records'!CS:CS,'List A Records'!BV:BV,'T20 Records'!BV:BV),INT((ROWS(B$2:B2)-1)/3+1),1,MOD((ROWS(B$2:B2)-1),3)+1)),"")

Although not essential, the references to B$2:B2 should ideally point to the cell containing the first formula in order to prevent errors if rows are inserted or deleted.

 

[Mel Smith]

Thanks Jason,

Although I didn't say so in my post, there are some cells that contain two or three references. how might these be handled e.g.

=IF('First Class Records'!M3+'First Class Records'!X3+C14=0,"",'First Class Records'!M3+'First Class Records'!X3+C14)
or
=IF('First Class Records'!CX3+'First Class Records'!CY3=0,"",'First Class Records'!CX3+'First Class Records'!CY3)
or
=IF('First Class Records'!M3+'First Class Records'!X3+'First Class Records'!AI3=0,"",'First Class Records'!M3+'First Class Records'!X3+'First Class Records'!AI3)

The first column in each record starts a D and ends at AF

Where should I enter your formula - C14 (the first empty cell of the first record.

Mel

 

[jasonb75]

If each formula is different then it's going to make it more complex. Are all of the formulas returning numbers or are some of them text?

Noting that you have office 365, do you have the =LET() function? If you do then that will help.

 

[Mel Smith]

Hi Jason,,

The formulas all return either a number or ""

Yes, I have the Let function.

Mel

 

[jasonb75]

With let it is easier, you don't need to do =IF(long formula = 0,"", long formula), instead you can do = LET(f,long formula,IF(f=0,"",f)) so you only need the long part once, making it easier to write.

The index method that I used earlier was meant to make copying down a bit easier by giving you one formula for all 3 rows rather than 3 individual formulas, however you may find it easier to do 3 individual formulas then copy them down together if that is what you have done before. Index will still be needed but in a slightly simpler form. The basic syntax for each formula would be

=LET(f,INDEX('First Class Records'!CS:CS,'List A Records'!BV:BV,'T20 Records'!BV:BV,INT((ROWS(B$2:B2)-1)/3+3),IF(f=0,"",f))

The section in bold needs to be made up for each cell to refer that should move down 1 for every 3 rows. The red 3 at the end dictates the first row in the source data, so would refer to BV3 in the example above. For formulas where 2 cells are added together you would use INDEX(....)+INDEX(....) if both cells have to increase 1 row for every 3, or INDEX(....)+A1 if A1 had to fill down as normal.

Hope that makes sense.

 

[Mel Smith]

Hi Jason,

Phew! I'll need to study this and try to understand it. Thank you for pointing me in the right direction.

Mel