Increment Cell Reference

[Guest]

I have a worksheet that starts with daily data, a date, then a dollar value,
then there is weekly data that needs to reference the daily data and
increment 7 rows each time. Every way I ahve come up with wants to increment
by 1, I used OFFSET to move down 7 rows, but then the next weekly cell OFFSET
increments the original reference by 1.

The daily data references the previous day and then adds any changes. So
for the weekly data, I need to just move my reference down 7 rows each week.

Thanks,
Perry

 

[Biff]

Hi!

Is this a macro procedure you're describing or do you need help with a
formula?

I can probably help with a formula. (I'd need to see what you're using now)

Biff

 

[Guest]

It is not a macro procedure and there is no formula currently. The person
before simply added 7 (in his head) to each subsequent cell reference. This
is what I am trying to accomplish as example:
A1 1/7/2006 B1 $1000
A2 1/8/2006 B2 $1050 ...
A8 1/14/2006 B8 $2025 ...
A15 1/21/2006 B15$3000

A100 =A1 B100 =B1
A101 =A100+7 B101 should refer to B8
A102 = A101+7 B102 should refer to B15

For B101, OFFSET worked to move down 7 rows from B1, but I couldn't figure
out how to then get down another 7 rows for B102 and contnue down 7 rows each
time.

Hoe that makes it more clear.

Thanks,
Perry

 

[Biff]

Try one of these in B100 and copy down as needed:

=INDEX(B:B,(ROWS($1:1)-ROW($1:$1))*7+1)

=OFFSET(B$1,(ROWS($1:1)-ROW($1:1))*7,,)

B100 will equal B1
B101 will equal B8
B102 will equal B15
B103 will equal B22
etc
etc

Biff

 

[Biff]

Ooops!

Hold on there a second!

This will work just fine:

=OFFSET(B$1,(ROWS($1:1)-ROW($1:1))*7,,)

This will cause a circular reference since it's being entered in column B:

=INDEX(B:B,(ROWS($1:1)-ROW($1:$1))*7+1)

Just change the range size of the INDEX function to something else:

=INDEX(B1:B99,(ROWS($1:1)-ROW($1:$1))*7+1)

Biff