Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference Worksheet Name
OK, I was able to use the indirect function to help. I'm half of the way to
where I want to be. In A1, I hard coded Sheet1 for the worksheet name. Then, in B1, I use =INDIRECT(A1&"!b10") to get a value from Sheet1. However, I'd like to be able to change the tab name from Sheet1 to PlantTest and have the text value in A1 change to PlantTest automatically. Is there a way to do this? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference Worksheet Name
Leann
I am not aware how this can be done using Excel's built-in funcitons. However it can be done programmatically using VBA. (1) Open Excel and press ALT + F11. This launches VB Editor. (2) Select <Insert<Module (3) In the module cut and paste: Function GetName() As String Application.Volatile GetName = ActiveSheet.Name End Function (4) Now close VB Editor (5) In cell A1 type =GetName( ) (6) This will return the name of the worksheet. Each time you change the worksheet name this will update automatically. By the way, by adding the VBA code (as above) when you come to open the excel workbook next time you will get a dialog box telling you that the workbook contains a macro. Select <Enable Macros in order for the formula above to function. Any problems, please write back. Alex "Leann" wrote: OK, I was able to use the indirect function to help. I'm half of the way to where I want to be. In A1, I hard coded Sheet1 for the worksheet name. Then, in B1, I use =INDIRECT(A1&"!b10") to get a value from Sheet1. However, I'd like to be able to change the tab name from Sheet1 to PlantTest and have the text value in A1 change to PlantTest automatically. Is there a way to do this? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference Worksheet Name
Wonderful..now I need to make myself more clear. It's been a long time since
I've done programming. Here's my notes on what I'd like to do, but definitely not in correct syntax: Function GetName() As String Application.Volatile Index = 3 Do Until Index = 27 GetName = Worksheets(Index).Name 'Put the answer in the cell 'Move to the next cell down 'Add one to Index End Function Do you see what I'm trying to do? I don't want the ActiveSheet's name. I want Sheets 3 through 27 names in Column A on Sheet1. Any help is appreciated! Thanks! Leann "Alex" wrote: Leann I am not aware how this can be done using Excel's built-in funcitons. However it can be done programmatically using VBA. (1) Open Excel and press ALT + F11. This launches VB Editor. (2) Select <Insert<Module (3) In the module cut and paste: Function GetName() As String Application.Volatile GetName = ActiveSheet.Name End Function (4) Now close VB Editor (5) In cell A1 type =GetName( ) (6) This will return the name of the worksheet. Each time you change the worksheet name this will update automatically. By the way, by adding the VBA code (as above) when you come to open the excel workbook next time you will get a dialog box telling you that the workbook contains a macro. Select <Enable Macros in order for the formula above to function. Any problems, please write back. Alex "Leann" wrote: OK, I was able to use the indirect function to help. I'm half of the way to where I want to be. In A1, I hard coded Sheet1 for the worksheet name. Then, in B1, I use =INDIRECT(A1&"!b10") to get a value from Sheet1. However, I'd like to be able to change the tab name from Sheet1 to PlantTest and have the text value in A1 change to PlantTest automatically. Is there a way to do this? Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference Worksheet Name
Leann wrote: Wonderful..now I need to make myself more clear. It's been a long time since I've done programming. Here's my notes on what I'd like to do, but definitely not in correct syntax: Function GetName() As String Application.Volatile Index = 3 Do Until Index = 27 GetName = Worksheets(Index).Name 'Put the answer in the cell 'Move to the next cell down 'Add one to Index End Function Do you see what I'm trying to do? I don't want the ActiveSheet's name. I want Sheets 3 through 27 names in Column A on Sheet1. Any help is appreciated! Thanks! Leann "Alex" wrote: Leann I am not aware how this can be done using Excel's built-in funcitons. However it can be done programmatically using VBA. (1) Open Excel and press ALT + F11. This launches VB Editor. (2) Select <Insert<Module (3) In the module cut and paste: Function GetName() As String Application.Volatile GetName = ActiveSheet.Name End Function (4) Now close VB Editor (5) In cell A1 type =GetName( ) (6) This will return the name of the worksheet. Each time you change the worksheet name this will update automatically. By the way, by adding the VBA code (as above) when you come to open the excel workbook next time you will get a dialog box telling you that the workbook contains a macro. Select <Enable Macros in order for the formula above to function. Any problems, please write back. Alex "Leann" wrote: OK, I was able to use the indirect function to help. I'm half of the way to where I want to be. In A1, I hard coded Sheet1 for the worksheet name. Then, in B1, I use =INDIRECT(A1&"!b10") to get a value from Sheet1. However, I'd like to be able to change the tab name from Sheet1 to PlantTest and have the text value in A1 change to PlantTest automatically. Is there a way to do this? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet reference | Excel Discussion (Misc queries) | |||
Using cell contents to reference worksheet names | Excel Discussion (Misc queries) | |||
Worksheet reference behaving funny | Excel Discussion (Misc queries) | |||
Indirect reference from one worksheet to another | Excel Worksheet Functions | |||
Reference a cell to get worksheet name | Excel Worksheet Functions |