Don't you need an exclamation mark after the sheet name in your reference?
You were talking about 1991ABC$L$268 but didn't you mean 1991ABC!$L$268 ?
--
David Biddulph


"Flipper" wrote in message
...
when I use it a written I get "REF" so something is not right.
--
Allan


"David Biddulph" wrote:

No. You use the formula as given.
.... or you could use =Indirect("1991ABC$L$26"&month(today())) instead of
=Indirect("1991ABC$L$26"&month(now()))

If there are functions which you don't understand, look them up in Excel
help.
now() returns current date and time
today() returns current date
month(...) returns the month number for the date given in the argument,
so
for a date in May, month(...) returns 5.
--
David Biddulph

"Flipper" wrote in message
...
Sean

I'm daft. I don't understand how to use indirect. At the point that
you
insert "month(now()))" what does that mean? Would I actually type in,
for
example, May 2009, and then now, and don't get it. Can you help me
with
that
too.

Thanks
--
Allan


"Sean Timmons" wrote:

Oh! OK.. Then

=Indirect("1991ABC$L$26"&month(now()))

Would return the value in 1991ABC$L$265 so.. if you wanted 268
instead,
just
make it
=Indirect("1991ABC$L$26"&month(now())+3)

"Flipper" wrote:

That won't work as the destination cell remains the same from month
to
month
--
Allan


"Sean Timmons" wrote:

I presume your destination data is straight down the row.

In that case, delete the $ prior to your row number, and the row
number will
automatically increase as you go down your rows.

"Flipper" wrote:

I produce a monthly report on investments where the ending
balance
of an
account is reported in one column and the interest earned is
reported in
another column. The data for this report comes from another
workbook, so
each entry is linked to this other workbook. Since I produce
this
report
monthly, I would like to be able to update those links
automatically rather
than manually. For example, in month 1, the link would be
1991ABC$L$268. In
month 2 the link would need to be udpated to 1991ABC$L$269,
month 3
1991ABC$L$270, etc.

Thanks
--
Allan