Increasing cell reference (columns) each 10 rows. How to?

[jangoux]

Hello everyone,

I have a bunch of formulas that all need to have their columns letter increased each a given number of cells. i.e:

=INDEX(INDIRECT(E4&"!E4:E30"),MATCH(L4,INDIRECT(E4&"!I4:I30"),0))

=MAX(INDIRECT($E4&"!I4"):INDIRECT($E4&"!I30"))

Both those formulas, each 10 rows that the same formula is repeated, needs to have the "!I4:I30"part changed to M4, Q4, U4 and so on...

How can I do this without manually changing each formula ?

 

[Domenic]

For your first formula, let's say that you're going to enter the formula in M4 and copy it across your columns. Try...

M4, copied across:

=INDEX(INDIRECT(ADDRESS(4,5,,,$E4)&":"&ADDRESS(30,5)),MATCH($L4,INDIRECT(ADDRESS(4,9+COLUMNS($M4:M4)*4-4,,,$E4)&":"&ADDRESS(30,9+COLUMNS($M4:M4)*4-4)),0))

If you want to copy the formula down the column as well, enter the formula in M4, copy across and down. Note that in this case E4 will change to E5, E6, etc., and L4 will change to L5, L6, etc. So you may need to change the absolute/relative reference for these cells depending on your actual requirement.

And, you can use the same approach for your second formula.

Hope this helps!

 

[jangoux]

Thanks for the answer!

But it didn't actually work. Instead of referencing the next four cells after the code is pasted 10 cells down, it is still doing its INDEX/MATCH thing in the first four columns.

 

[jangoux]

To make myself clearer, let's say:

1
2
3
4
5
6
7
8
9
10
11
12
13

and so on..

From columns 1 to 10 I want to reference to I4:I30, and then from 11 to 20, M4:M30, 21 to 30, Q4:Q30 and so on...

 

[Domenic]

You said "columns 1 to 10", but I think you meant row 1 to 10. Is this correct? If so, let's say that you're entering your formula in A1, try...

A1, copied down:

=INDEX(INDIRECT(ADDRESS(4,5,,,$E$4)&":"&ADDRESS(30,5)),MATCH($L$4,INDIRECT(ADDRESS(4,9+4*INT((ROWS(A$1:A1)-1)/10),,,$E$4)&":"&ADDRESS(30,9+4*INT((ROWS(A$1:A1)-1)/10))),0))

Does this return the desired result?

 

[jangoux]

You said "columns 1 to 10", but I think you meant row 1 to 10. Is this correct? If so, let's say that you're entering your formula in A1, try...

A1, copied down:

=INDEX(INDIRECT(ADDRESS(4,5,,,$E$4)&":"&ADDRESS(30,5)),MATCH($L$4,INDIRECT(ADDRESS(4,9+4*INT((ROWS(A$1:A1)-1)/10),,,$E$4)&":"&ADDRESS(30,9+4*INT((ROWS(A$1:A1)-1)/10))),0))

Does this return the desired result?

Yup, now it works, awesome. Thanks a lot!

Btw I can't figure out doing the same for the other formula. I suspect it is something like

match(indirect( ADDRESS(4,9+4*INT((ROWS(K$4:K4)-1)/10)):indirect(ADDRESS(30,9+4*INT((ROWS(K$4:K4)-1)/10)))))

but excel says the formula constains an error.

 

[Domenic]

You're very welcome!

For your second formula, let's say you're going to enter the formula in B1, and then copy down, try...

=MAX(INDIRECT(ADDRESS(4,9+4*INT((ROWS(B$1:B1)-1)/10),,,$E4)&":"&ADDRESS(30,9+4*INT((ROWS(B$1:B1)-1)/10))))

 

[jangoux]

Perfect! Thanks!