calculating the name of a worksheet

[outlook help]

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.

 

[Max]

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

 

[Max]

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 !

 

[outlook help]

Max,

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

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 !

 

[Max]

You're welcome !
Always great to hear that it helped
Thanks for the feedback ..