Increment Cell Reference

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

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
>
>
>

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
>>
>>
>>

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
>>>
>>>
>>>
>
>