Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 <atyahoo<dotcom ---- "outlook help" wrote in message ... 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. |
#3
|
|||
|
|||
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 <atyahoo<dotcom ---- |
#4
|
|||
|
|||
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 <atyahoo<dotcom ---- |
#5
|
|||
|
|||
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 <atyahoo<dotcom ---- "outlook help" wrote in message ... Max, Thank you so much for all of your help. You have been wonderful and have helped me move along with this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How To Refresh Chart Data Without Calculating The Worksheet | Charts and Charting in Excel | |||
Weekly Transaction Processing | Excel Worksheet Functions | |||
Indirect reference from one worksheet to another | Excel Worksheet Functions | |||
Reference Data in Moved Worksheet | Setting up and Configuration of Excel | |||
Calculating using data from another worksheet | Excel Worksheet Functions |