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

"Biff" <biffinpitt@comcast.net> wrote in message
news:uZKXMYoQGHA.3192@TK2MSFTNGP09.phx.gbl...
> 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
>
> "Perry" <Perry@discussions.microsoft.com> wrote in message
> news:BC3CE04F-CB62-4BCC-ACC4-614210146C68@microsoft.com...
>> 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" wrote:
>>
>>> 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
>>>
>>> "Perry" <Perry@discussions.microsoft.com> wrote in message
>>> news:51E394A9-06A3-4C41-9DB0-FEC55F38EE3B@microsoft.com...
>>> >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
>>>
>>>
>>>
>
>