*Excel* reference cell to increase by 1 every 6th cell, Cant figure this out for the life of me :(-CodePudding

Autofill is messing me up here, i currently have this in Column A:

A1 =IF((money!$G$7-(A!$A4))>=0, 'C'!$B$4, "" )
A2 =IF((money!$G$7-(A!$A4))>=0, 'C'!$B$4, "" )
A3 =IF((money!$G$7-(A!$A4))>=0, 'C'!$B$4, "" )
A4 =IF((money!$G$7-(A!$A4))>=0, 'C'!$B$4, "" )
A5 =IF((money!$G$7-(A!$A4))>=0, 'C'!$B$4, "" )
A6 =IF((money!$G$7-(A!$A4))>=0, 'C'!$B$4, "" )
A7 =IF((money!$G$7-(A!$A5))>=0, 'C'!$B$4, "" )
A8 =IF((money!$G$7-(A!$A5))>=0, 'C'!$B$4, "" )
A9 =IF((money!$G$7-(A!$A5))>=0, 'C'!$B$4, "" )
A10 =IF((money!$G$7-(A!$A5))>=0, 'C'!$B$4, "" )
A11 =IF((money!$G$7-(A!$A5))>=0, 'C'!$B$4, "" )
A12 =IF((money!$G$7-(A!$A5))>=0, 'C'!$B$4, "" )

And i need to repeat this for 20,000 cells where the same formula is repeated 20,000 times but the reference number increases by 1 every 6 cells. But when i highlight all 12 rows and autofill it i get something like this:

A1 =IF((money!$G$7-(A!$A4))>=0, 'C'!$B$4, "" )
A2 =IF((money!$G$7-(A!$A4))>=0, 'C'!$B$4, "" )
A3 =IF((money!$G$7-(A!$A4))>=0, 'C'!$B$4, "" )
A4 =IF((money!$G$7-(A!$A4))>=0, 'C'!$B$4, "" )
A5 =IF((money!$G$7-(A!$A4))>=0, 'C'!$B$4, "" )
A6 =IF((money!$G$7-(A!$A4))>=0, 'C'!$B$4, "" )
A7 =IF((money!$G$7-(A!$A5))>=0, 'C'!$B$4, "" )
A8 =IF((money!$G$7-(A!$A5))>=0, 'C'!$B$4, "" )
A9 =IF((money!$G$7-(A!$A5))>=0, 'C'!$B$4, "" )
A10 =IF((money!$G$7-(A!$A5))>=0, 'C'!$B$4, "" )
A11 =IF((money!$G$7-(A!$A5))>=0, 'C'!$B$4, "" )
A12 =IF((money!$G$7-(A!$A5))>=0, 'C'!$B$4, "" )
A13 =IF((money!$G$7-(A!$A13))>=0, 'C'!$B$4, "" )
A14 =IF((money!$G$7-(A!$A13))>=0, 'C'!$B$4, "" )
A15 =IF((money!$G$7-(A!$A13))>=0, 'C'!$B$4, "" )
A16 =IF((money!$G$7-(A!$A13))>=0, 'C'!$B$4, "" )
A17 =IF((money!$G$7-(A!$A13))>=0, 'C'!$B$4, "" )
A18 =IF((money!$G$7-(A!$A13))>=0, 'C'!$B$4, "" )
A19 =IF((money!$G$7-(A!$A19))>=0, 'C'!$B$4, "" )
A20 =IF((money!$G$7-(A!$A19))>=0, 'C'!$B$4, "" )
A21 =IF((money!$G$7-(A!$A19))>=0, 'C'!$B$4, "" )
A22 =IF((money!$G$7-(A!$A19))>=0, 'C'!$B$4, "" )
A23 =IF((money!$G$7-(A!$A19))>=0, 'C'!$B$4, "" )
A24 =IF((money!$G$7-(A!$A19))>=0, 'C'!$B$4, "" )

Instead of my reference # (A!$A#) increasing by 1 every time from 4 to 5 to 6 to 7 it increases by 6 every single time. Please help me, ive been stuck on this all day and there is no way i can manually do this for 20,000 cells :(

CodePudding user response：

I can give you a simple and stupid solution that will make the job, but for sure there's some right way to do it.

Create a column with numbers that would be correct for you, autofill till the end, numbers will increase by one as you need. (Decrease decimals if needed)

After that just use INDIRECT

In my case column M contains those numbers. In this way you'll create references to cells dynamically with values from this column.

=IF((money!$G$7-(INDIRECT("A!$A"&M1)))>=0, 'C'!$B$4, "" )

CodePudding user response：

In cell A1, enter:

 =INDIRECT("IF((money!$G$7-(A!$A$"& 
 (ROW(OFFSET(B1,6-MOD(ROW(),6),0))-2)&"))>=0, 
 'C'!$B$4, "")")

In cell A2, enter the same formula:

 =INDIRECT("IF((money!$G$7-(A!$A$"& 
 (ROW(OFFSET(B1,6-MOD(ROW(),6),0))-2)&"))>=0, 
 'C'!$B$4, "")")

and then copy cell A2 and paste it in the remaining cells, A3 through A20000.