calculating the name of a worksheet

is there a way to create a formula that changes the name of a sheet within
another formula. In other words, the following is a reference to an external
workbook within an IF Function -- '[SDS.xls]we121'!$L$3. The sheet name we121
will change on a wkly basis by 7 days. I would like to have the formula
automatically look at the worksheet that is we121+7 OR we128. Is it possible?

Thank you.

Use the INDIRECT() function

INDIRECT("'[SDS.xls]we" & NUMBER & "'!$L$3")

replace 'NUMBER' with your own cell reference or calculation.
INDIRECT does need the workbook it is referencing to be open.

To avoid this, view this websites right-on on three ways of referencing closed workbooks. The PULL() function seems best to me but beware that is takes a while to calculate if you use it a lot or on a big range.

http://www.*****-blog.com/archives/2004/12/01/

HTH

That should be write-up, not right-on

One way to consider

Earmark a cell say, A1
to contain the numbers: 121, 128, etc

Then we could put
in say, A2: =INDIRECT("[SDS.xls]we"&A1&"!$L$3")

If A1 contains: 121
A2 will return the same as: =[SDS.xls]we121!$L$3

If A1 contains: 128
A2 will return the same as: =[SDS.xls]we128!$L$3

So you can control what's returned in A2
via easily changing the input in A1

And you could also easily create a Data Validation (DV)
to select the week#s in A1 (instead of inputting)

Just select A1
Click Data > Validation
Settings:
Select under "Allow:" : List
Put in "Source:" : 121,128,135,142,149,156, etc
Click OK

Or, use a named range as the DV source

In another sheet, say Sheet2
------------------
Put in A1: 121
Put in A2: 128
Select A1:A2, fill down to say, A20
Name the range A1:A20 as : MyList

Then do the same DV steps for A1 above,
with the exception for step "Put in "Source:"

Replace with:
Put in "Source:" : =MyList
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"outlook help" <[email protected]> wrote in message
news:[email protected]...
> is there a way to create a formula that changes the name of a sheet within
> another formula. In other words, the following is a reference to an
external
> workbook within an IF Function -- '[SDS.xls]we121'!$L$3. The sheet name
we121
> will change on a wkly basis by 7 days. I would like to have the formula
> automatically look at the worksheet that is we121+7 OR we128. Is it
possible?
>
> Thank you.

From OP's email note:
....
>
=IF($A9=[SDS2005CR.xls]we"&BU7&"!$L$3,INDIRECT("[SDS2005CR.xls]we"&BU7&"!N"&
COLUMNS($A$1:A6)+7),"") but got an ERROR.

Think you forgot to wrap the INDIRECT(...) around the 1st part of the
formula in the implementation:
.... $A9=[SDS2005CR.xls]we"&BU7&"!$L$3 ...

Try instead:

=IF($A9=INDIRECT("[SDS2005CR.xls]we"&BU7&"!$L$3"),INDIRECT("[SDS2005CR.xls]w
e"&BU7&"!N"&COLUMNS($A$1:A6)+7),"")

If the reference cell: BU7
(which presumably houses the week#'s: 121,128, etc)
needs to remain *constant*
when you copy the formula across,
change BU7 to $BU$7
(the dollar signs will make the cell ref absolute)

Hope the above helps !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Max,

Thank you so much for all of your help. You have been wonderful and have
helped me move along with this.

"Max" wrote:

> From OP's email note:
> ....
> >
> =IF($A9=[SDS2005CR.xls]we"&BU7&"!$L$3,INDIRECT("[SDS2005CR.xls]we"&BU7&"!N"&
> COLUMNS($A$1:A6)+7),"") but got an ERROR.
>
> Think you forgot to wrap the INDIRECT(...) around the 1st part of the
> formula in the implementation:
> .... $A9=[SDS2005CR.xls]we"&BU7&"!$L$3 ...
>
> Try instead:
>
> =IF($A9=INDIRECT("[SDS2005CR.xls]we"&BU7&"!$L$3"),INDIRECT("[SDS2005CR.xls]w
> e"&BU7&"!N"&COLUMNS($A$1:A6)+7),"")
>
> If the reference cell: BU7
> (which presumably houses the week#'s: 121,128, etc)
> needs to remain *constant*
> when you copy the formula across,
> change BU7 to $BU$7
> (the dollar signs will make the cell ref absolute)
>
> Hope the above helps !
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>
>

You're welcome !
Always great to hear that it helped
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"outlook help" <[email protected]> wrote in message
news:[email protected]...
> Max,
>
> Thank you so much for all of your help.
> You have been wonderful and have
> helped me move along with this.