references

I have a list of dates (consecutive weekdays - some holidays) in Column D in
Sheet2. In sheet1, I have a reference to those days. For example in A3 I
have the formula =Sheet2!D3 which displays the correct date. I'd like to
copy the formula to every 7th cell in Column A in Sheet1. What's happening
now is that the copied formula increments by 7 The formula copied into the
cell A10 is "=Sheet2!D10". How can I force it to increment only by 1, so
that it references the dates in Sheet2 correctly?
Thanks
Bert

Pete_UK
Fri Mar 14 20:40:00 CDT 2008

Put this in A3 of Sheet1:

=3DINDIRECT("Sheet2!D"&INT((ROW(A3)+4)/7)+2)

and then copy it to A10, A17, A24 etc.

Another way would be to enter your original formula and copy down
contiguous cells, then insert 6 new rows between each of the rows with
formulae in.

Hope this helps.

Pete

On Mar 15, 12:39=A0am, "Bert" <bertw...@theworld.com> wrote:
> I have a list of dates (consecutive weekdays - some holidays) in Column D =
in
> Sheet2. =A0In sheet1, I have a reference to those days. =A0For example in =
A3 I
> have the formula =3DSheet2!D3 which displays the correct date. =A0I'd like=
to
> copy the formula to every 7th cell in Column A in Sheet1. =A0What's happen=
ing
> now is that the copied formula increments by 7 =A0The formula copied into =
the
> cell A10 is "=3DSheet2!D10". =A0How can I force it to increment only by 1,=
so
> that it references the dates in Sheet2 correctly?
> Thanks
> Bert

Stan
Sun Mar 16 00:53:06 CDT 2008

Fri, 14 Mar 2008 20:39:06 -0400 from Bert <bertwhit@theworld.com>:
> I have a list of dates (consecutive weekdays - some holidays) in Column D in
> Sheet2. In sheet1, I have a reference to those days. For example in A3 I
> have the formula =Sheet2!D3 which displays the correct date. I'd like to
> copy the formula to every 7th cell in Column A in Sheet1. What's happening
> now is that the copied formula increments by 7 The formula copied into the
> cell A10 is "=Sheet2!D10". How can I force it to increment only by 1, so
> that it references the dates in Sheet2 correctly?

I don't know if this is the best way, but INDIRECT will get the job
done.

=indirect("Sheet2!D" & int(3+(row()-3)/7))

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...