| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use reserved word to specify current worksheet name
Hope i'm in right forum this time.
In the following statement i would like to replace the month name September 2008 with a word that would say use current worksheet name. (ie. sort of like how Column() and Row() work, but with a worksheet name. Of course just as soon as i learn how to do this, i'll want to do the same with the workbook name. =IF(ISNA(VLOOKUP($A40,'F:\BILLING\[2008 Error ReportN.xlsx]September 2008'!$A$3:$J$70,COLUMN(),FALSE)),8881000000,IF(IS NUMBER(VLOOKUP($A40,'F:\BILLING\[2008 Error ReportN.xlsx]September 2008'!$A$3:$J$70,COLUMN(),FALSE)),VLOOKUP($A40,'F: \BILLING\[2008 Error ReportN.xlsx]September 2008'!$A$3:$J$70,COLUMN(),FALSE),0))+IF(ISNA(VLOOK UP($A40,'F:\BILLING\[2008 Error ReportS.xlsx]September 2008'!$A$3:$J$70,COLUMN(),FALSE)),8881000000,IF(IS NUMBER(VLOOKUP($A40,'F:\BILLING\[2008 Error ReportS.xlsx]September 2008'!$A$3:$J$70,COLUMN(),FALSE)),VLOOKUP($A40,'F: \BILLING\[2008 Error ReportS.xlsx]September 2008'!$A$3:$J$70,COLUMN(),FALSE),0)) -- Dec |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use reserved word to specify current worksheet name
Where is the month name located, in a worksheet?
Paul "decdec" wrote: Hope i'm in right forum this time. In the following statement i would like to replace the month name September 2008 with a word that would say use current worksheet name. (ie. sort of like how Column() and Row() work, but with a worksheet name. Of course just as soon as i learn how to do this, i'll want to do the same with the workbook name. =IF(ISNA(VLOOKUP($A40,'F:\BILLING\[2008 Error ReportN.xlsx]September 2008'!$A$3:$J$70,COLUMN(),FALSE)),8881000000,IF(IS NUMBER(VLOOKUP($A40,'F:\BILLING\[2008 Error ReportN.xlsx]September 2008'!$A$3:$J$70,COLUMN(),FALSE)),VLOOKUP($A40,'F: \BILLING\[2008 Error ReportN.xlsx]September 2008'!$A$3:$J$70,COLUMN(),FALSE),0))+IF(ISNA(VLOOK UP($A40,'F:\BILLING\[2008 Error ReportS.xlsx]September 2008'!$A$3:$J$70,COLUMN(),FALSE)),8881000000,IF(IS NUMBER(VLOOKUP($A40,'F:\BILLING\[2008 Error ReportS.xlsx]September 2008'!$A$3:$J$70,COLUMN(),FALSE)),VLOOKUP($A40,'F: \BILLING\[2008 Error ReportS.xlsx]September 2008'!$A$3:$J$70,COLUMN(),FALSE),0)) -- Dec |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use reserved word to specify current worksheet name
The name of the worksheet is not in any particular cell. Just want to pick
up the name of the current worksheet that the formula is in. -- Dec "Paul" wrote: Where is the month name located, in a worksheet? Paul "decdec" wrote: Hope i'm in right forum this time. In the following statement i would like to replace the month name September 2008 with a word that would say use current worksheet name. (ie. sort of like how Column() and Row() work, but with a worksheet name. Of course just as soon as i learn how to do this, i'll want to do the same with the workbook name. =IF(ISNA(VLOOKUP($A40,'F:\BILLING\[2008 Error ReportN.xlsx]September 2008'!$A$3:$J$70,COLUMN(),FALSE)),8881000000,IF(IS NUMBER(VLOOKUP($A40,'F:\BILLING\[2008 Error ReportN.xlsx]September 2008'!$A$3:$J$70,COLUMN(),FALSE)),VLOOKUP($A40,'F: \BILLING\[2008 Error ReportN.xlsx]September 2008'!$A$3:$J$70,COLUMN(),FALSE),0))+IF(ISNA(VLOOK UP($A40,'F:\BILLING\[2008 Error ReportS.xlsx]September 2008'!$A$3:$J$70,COLUMN(),FALSE)),8881000000,IF(IS NUMBER(VLOOKUP($A40,'F:\BILLING\[2008 Error ReportS.xlsx]September 2008'!$A$3:$J$70,COLUMN(),FALSE)),VLOOKUP($A40,'F: \BILLING\[2008 Error ReportS.xlsx]September 2008'!$A$3:$J$70,COLUMN(),FALSE),0)) -- Dec |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use reserved word to specify current worksheet name
Perhaps I'm missing something, but why do you need a name for the
current worksheet? Any range you specify that does not include a sheet reference will refer to the sheet in which the reference occurs. E.g., =VLOOKUP(123,A1:C10,2,FALSE) will refer to A1:C10 on the sheet that contains the formula, so no reference to that sheet is required. That said, if you need a name, put the following code in the ThisWorkbook code module: Private Sub Workbook_Open() Dim WS As Worksheet For Each WS In ThisWorkbook.Worksheets WS.Names.Add "SheetName", WS.Name Next WS ThisWorkbook.Names.Add "BookName", ThisWorkbook.Name End Sub This will create a sheet-level name called "SheetName" on each worksheet and referencing that name will return the name of the worksheet that references it. That is, if you reference SheetName on Sheet1 it will resolve to "Sheet1". Reference it on Sheet2 and it resolves to "Sheet2". Then, you can use the INDIRECT function to reference the actual sheet. E.g., =VLOOKUP(123,INDIRECT(SheetName&"!A1:C10"),2,FALSE ) In general, the INDIRECT function can be used to convert any string to an actual cell reference that can be used in a formula. Note, though, that if you use INDIRECT with a workbook name, that workbook must be open. Cordially, Chip Pearson Microsoft MVP Excel Product Group Pearson Software Consulting, LLC www.cpearson.com The San Diego Project Group, LLC (email is on the web site) USA Central Daylight Time (-5:00 GMT) On Thu, 9 Oct 2008 12:05:00 -0700, decdec wrote: The name of the worksheet is not in any particular cell. Just want to pick up the name of the current worksheet that the formula is in. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use reserved word to specify current worksheet name
I am going to assume you are still in the wrong forum. You want a formula
that uses teh tab name of the corrent sheet to access a cell from the same tab name in another workbook??? Am I close??? Here is a formula that will get you the tab name =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) You will need to use a variation of the indirect function as indirect can not operat on a closed workbook... You can download an add-in called Morefunc it has a function called INDIRECT.EXT that will work if the source book is closed http://xcell05.free.fr/morefunc/english/index.htm there is no built in function that can take a string referring to another workbook that is not open and make it into a valid formula -- HTH... Jim Thomlinson "decdec" wrote: Hope i'm in right forum this time. In the following statement i would like to replace the month name September 2008 with a word that would say use current worksheet name. (ie. sort of like how Column() and Row() work, but with a worksheet name. Of course just as soon as i learn how to do this, i'll want to do the same with the workbook name. =IF(ISNA(VLOOKUP($A40,'F:\BILLING\[2008 Error ReportN.xlsx]September 2008'!$A$3:$J$70,COLUMN(),FALSE)),8881000000,IF(IS NUMBER(VLOOKUP($A40,'F:\BILLING\[2008 Error ReportN.xlsx]September 2008'!$A$3:$J$70,COLUMN(),FALSE)),VLOOKUP($A40,'F: \BILLING\[2008 Error ReportN.xlsx]September 2008'!$A$3:$J$70,COLUMN(),FALSE),0))+IF(ISNA(VLOOK UP($A40,'F:\BILLING\[2008 Error ReportS.xlsx]September 2008'!$A$3:$J$70,COLUMN(),FALSE)),8881000000,IF(IS NUMBER(VLOOKUP($A40,'F:\BILLING\[2008 Error ReportS.xlsx]September 2008'!$A$3:$J$70,COLUMN(),FALSE)),VLOOKUP($A40,'F: \BILLING\[2008 Error ReportS.xlsx]September 2008'!$A$3:$J$70,COLUMN(),FALSE),0)) -- Dec |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| what is a worksheet reserved name? | Excel Discussion (Misc queries) | |||
| Use reserved word to specify current worksheet name | Excel Programming | |||
| Ascertaining whether a search in VBA code returns a string or a reserved word | Excel Programming | |||
| time between table in word and current date | Excel Programming | |||
| Renaming Worksheet: Error message: "Reserved Name" | Excel Discussion (Misc queries) | |||
Linear Mode
