I have a sheet which has a cell every 7 rows which gives me my weekly TSS total. I want to take these values, put them on another sheet and force this sheet to autopopulate based on a formula. Just can’t get it to work.
=PLAN!Q2004
=PLAN!Q2011
=PLAN!Q2018
I want to be able to ‘drag’ this formula so the final 4 digits increase by 7 each time.
Anyone?
Thanks
You can use the “indirect” function, which takes a cell address and returns the value in that cell. Thus, you can use a formula to calculate the address you want–here, multiply by seven to get the increase in row numbers you’re looking for.
E.g., in column A, you list out the week numbers (counting up from 1):
1
2
3
…
In column B, you put your indirect formula… should be something like this:
=indirect(“PLAN!Q”&value(1997+7*A1))
Mmm, getting a parse error when I copy that formula direct and swap the “&value” for the cell reference, need to work this out. Thanks…so far
That’s not quite the way to use the formula. The indirect function takes a string and tries to find the cell it’s referring to. For example, just a straight up cell reference would be =indirect(“Plan!Q100”) which would give the same result as =Plan!Q100. The benefit of using it is that you can change parts of the string according to other cells.
So to use the above suggestion have one column heading, say Week Number, in A1 and another, like Week TSS in B1. Then, in A2 onwards have 1,2,3 etc. Put in B2 =indirect(“PLAN!Q”&(2004+(A2-1)*7)) exactly and copy down. Adjust the 2004 if that isn’t where the first value is…
Still getting a parse error
=indirect(“PLAN!Q”&(1997+(A2-1)*7)) is what is in the cell… cell Q1997 on a sheet called PLAN is the first value I want and the the value of what is in the cell 7 rows below next and so on
So, from elsewhere, the correct formula is
=INDIRECT(“PLAN!$Q”&(ROW(A1)*7-7+2))
=INDIRECT(“PLAN!$Q”&(ROW(A2)*7-7+2))
and then drag down