How to make Excel follow a formula algorithm on every 3 rows-CodePudding

I want to make Excel follow a formula pattern on every 3 rows, such that after an increase of three in the conditional if statement, there should be an increment of one on return value "TRUE". So that every time I drag it down, the algorithm gets applied to succeeding 3 rows that it follows. I have this simple formula of:

=IF(B11="RTR",RTR!G11:Z11,IF(B11="DHG2",'DHG2'!G11:Z11,IF(B11="JQV",JQV!G11:Z11,""))) =IF(B12="RTR",RTR!G11:Z11,IF(B12="DHG2",'DHG2'!G11:Z11,IF(B12="JQV",JQV!G11:Z11,""))) =IF(B13="RTR",RTR!G11:Z11,IF(B13="DHG2",'DHG2'!G11:Z11,IF(B13="JQV",JQV!G11:Z11,""))) =IF(B14="RTR",RTR!G12:Z12,IF(B14="DHG2",'DHG2'!G12:Z12,IF(B14="JQV",JQV!G12:Z12,""))) =IF(B15="RTR",RTR!G12:Z12,IF(B15="DHG2",'DHG2'!G12:Z12,IF(B15="JQV",JQV!G12:Z12,""))) =IF(B16="RTR",RTR!G12:Z12,IF(B16="DHG2",'DHG2'!G12:Z12,IF(B16="JQV",JQV!G12:Z12,"")))

Following the formula I wanted, it's supposed to be followed by:

=IF(B17="RTR",RTR!G13:Z13,IF(B17="DHG2",'DHG2'!G13:Z13,IF(B17="JQV",JQV!G13:Z13,"")))

I tried dragging down the formula to apply it to below rows but the pattern doesn't follow to what I wanted. Instead, the row below goes into like:

=IF(B17="RTR",RTR!G17:Z17,IF(B17="DHG2",'DHG2'!G17:Z17,IF(B17="JQV",JQV!G17:Z17,"")))

Need a little bit of help here.

Thank you.

CodePudding user response：

So, an example which you can expand:

VLOOKUP(A1,INDIRECT("'RTR'!"&"G11:Z11",1),1,0)

Not sure where you increment of 3 occurs as B11 goes to B12 and even the last two B16 goes to B17 and the target range matches the row numbers.

As I have shown you can build the target range so adding the row numbers is trivial now. They can be taken from cells, C1 could be 3, C2 6 etc.

CodePudding user response：

Here is a solution that doesn't require helper cells:

This formula will increment by 1 every 3 rows:

=CEILING.MATH(ROW()/3)

If you want the sequence to start on a different row, say row 10, you can just subtract that number - 1 from the ROW(), so for starting on row 10 from 1, subtract 9:

=CEILING.MATH((ROW()-9)/3)

And if you wanted the sequence to start from a different number at a different row, you can just add that number -1 to this whole thing, this example will start at 11 on row 10:

=CEILING.MATH((ROW()-9)/3) 10

You can then incorporate it in your formula together with INDIRECT, this formula

*there is a much better solution at the bottom, you don't need the IFs

=IF(B11="RTR",INDIRECT("RTR!G" & (CEILING.MATH(ROW()/3) 10) & ":Z" & (CEILING.MATH(ROW()/3) 10)),IF(B11="DHG2",INDIRECT("DHG2!G" & (CEILING.MATH(ROW()/3) 10) & ":Z" & (CEILING.MATH(ROW()/3) 10)),IF(B11="JQV",INDIRECT("RTR!G" & (CEILING.MATH(ROW()/3) 10) & ":Z" & (CEILING.MATH(ROW()/3) 10)),"")))

Should produce this:

=IF(B11="RTR",RTR!G11:Z11,IF(B11="DHG2",'DHG2'!G11:Z11,IF(B11="JQV",JQV!G11:Z11,"")))

If put anywhere in the first row of any sheet. When expanded down, it should behave like you want it to.

*you can reduce the length of the formula with INDIRECT significantly if you have access to LET, but I don't :(

EDIT: Much better solution proposed by P.b:

=INDIRECT(B11 & "!G" & (CEILING.MATH(ROW()/3) 10) & ":Z" & (CEILING.MATH(ROW()/3) 10))