Unexplainable Formulas
 

=INDIRECT("B"&(ROW($G$2)+6+(ROW(F2)-2)*7))
=INDIRECT("B"&(ROW($G$2)+(ROW(G2)-2)*7))
=INDIRECT("B"&(ROW($G$2)+1+(ROW(H2)-2)*7))
=INDIRECT("B"&(ROW($G$2)+2+(ROW(I2)-2)*7))
=INDIRECT("B"&(ROW($G$2)+3+(ROW(J2)-2)*7))
=INDIRECT("B"&(ROW($G$2)+4+(ROW(K2)-2)*7))
=INDIRECT("B"&(ROW($G$2)+5+(ROW(L2)-2)*7))

Atm I am using the above to list take all data from column B in Day
order andlist into a new row giving weekly totals.

I can manipulate this formula to take data from different columns.

However, if I dont understands what the following sections of the
forumula relate to and what/how I can chagne them to my advantage. i.e
fortnightly lists, monthly lists etc etc.

&(ROW($G$2)+3+(ROW(J2)-2)*7))

^^^ all of the above i do not understand anyone able to advise?

Thanks
SP.


--
samprince
------------------------------------------------------------------------
samprince's Profile: http://www.excelforum.com/member.php...o&userid=34168
View this thread: http://www.excelforum.com/showthread...hreadid=553219

Unexplainable Formulas
 

All that I can see happening with these formulas, is that they're returning
the contents of cell:
B8
B2
B3
B4
B5
B6
B7
From their original entry cell.

When each formula is individually copied down, the second half,
+(ROW(F2)-2)*7
increments the row to return in Column B by 7.

So, the first formula copied down would return the contents of:
B8, B15, B22, B29, ... etc.

The second returns:
B2, B9, B16, B23, ... etc.

The third:
B3, B10, B17, B24, ... etc.

And the same for the rest of them.

Perhaps these formulas might be more intuitive:

=INDEX(B:B,7*ROWS($1:1)-6)
=INDEX(B:B,7*ROWS($1:1)-5)
=INDEX(B:B,7*ROWS($1:1)-4)
=INDEX(B:B,7*ROWS($1:1)-3)
=INDEX(B:B,7*ROWS($1:1)-2)
=INDEX(B:B,7*ROWS($1:1)-1)
=INDEX(B:B,7*ROWS($1:1))
=INDEX(B:B,7*ROWS($1:1)+1)


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===



"samprince" wrote in
message ...

=INDIRECT("B"&(ROW($G$2)+6+(ROW(F2)-2)*7))
=INDIRECT("B"&(ROW($G$2)+(ROW(G2)-2)*7))
=INDIRECT("B"&(ROW($G$2)+1+(ROW(H2)-2)*7))
=INDIRECT("B"&(ROW($G$2)+2+(ROW(I2)-2)*7))
=INDIRECT("B"&(ROW($G$2)+3+(ROW(J2)-2)*7))
=INDIRECT("B"&(ROW($G$2)+4+(ROW(K2)-2)*7))
=INDIRECT("B"&(ROW($G$2)+5+(ROW(L2)-2)*7))

Atm I am using the above to list take all data from column B in Day
order andlist into a new row giving weekly totals.

I can manipulate this formula to take data from different columns.

However, if I dont understands what the following sections of the
forumula relate to and what/how I can chagne them to my advantage. i.e
fortnightly lists, monthly lists etc etc.

&(ROW($G$2)+3+(ROW(J2)-2)*7))

^^^ all of the above i do not understand anyone able to advise?

Thanks
SP.


--
samprince
------------------------------------------------------------------------
samprince's Profile:
http://www.excelforum.com/member.php...o&userid=34168
View this thread: http://www.excelforum.com/showthread...hreadid=553219