Use reserved word to specify current worksheet name

[decdec]

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(ISNUMBER(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(VLOOKUP($A40,'F:\BILLING\[2008
Error ReportS.xlsx]September
2008'!$A$3:$J$70,COLUMN(),FALSE)),8881000000,IF(ISNUMBER(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))

 

[Paul]

Where is the month name located, in a worksheet?

Paul

 

[decdec]

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

Where is the month name located, in a worksheet?

Paul

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(ISNUMBER(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(VLOOKUP($A40,'F:\BILLING\[2008
Error ReportS.xlsx]September
2008'!$A$3:$J$70,COLUMN(),FALSE)),8881000000,IF(ISNUMBER(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))

 

[Chip Pearson]

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)

 

[Jim Thomlinson]

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