Re: Absolute cells and worksheets

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Peo Sjoblom (terre08_at_mvp.org)
Date: 07/09/04 

Date: Fri, 9 Jul 2004 11:55:04 -0400

Yes, I am sure where you are wrong.
My assumption is that your *** 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 *** names it won't work
either

Put this in the Week 23 *** 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 *** name, fix that. 

-- 
Regards,
Peo Sjoblom
(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
"Louise" <anonymous@discussions.microsoft.com> wrote in message
news:2a70b01c465c2$9d870970$a301280a@phx.gbl...
> 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
>
> >-----Original Message-----
> >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)
> >
> >
> >"Louise" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:2903101c464de$938b6540$a401280a@phx.gbl...
> >> 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 work***, 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 work***?
> >>
> >> Thanks.
> >>
> >> Louise
> >
> >
> >.
> >
Quantcast