Absolute cells and worksheets

  • Thread starter Thread starter Louise
  • Start date Start date
L

Louise

Hi all

I have a formula in a cell which reads ='Week 23'!$E$10.
When I copy the formula down to the next row, I would
like it to look at the following week's worksheet, ie.
Week 24 and the same cell, hence the absolute reference.
However, when I drag the formula down, it still refers to
Week 23 - how can I get it to automatically go to the
next worksheet?

Thanks.

Louise
 
A workaround since sheets don't use relative referencing


=INDIRECT("'Week "&ROW(23:23)&"'!$E$10")



--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
thank you for your reply, however, it doesn't seem to
have worked and I'm not quite sure why. It simply
returns a 'REF' error. Any idea where im going wrong???
Thanks.

Louise
 
Yes, I am sure where you are wrong.
My assumption is that your sheet names are

Week<space>23

and so on, if they are my formula will work, if not it won't work
So if the name is

Week23

then change formula to

=INDIRECT("'Week"&ROW(23:23)&"'!$E$10")

Of course if you have other hidden spaces in your sheet names it won't work
either

Put this in the Week 23 sheet and see what you get

=LEN(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,34))

if you get 7 my formula should work, if you get 6 the amended formula
without the space should
work, if neither you have extra spaces in your sheet name, fix that.


--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Back
Top