Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Name of worksheets in one worksheet
I know how to obtain name of worksheets in the same worksheet.
Following function will do this. MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) I want to obtain name of all worksheets in one sheet. How can I do it. Sheet names are not: [sheet]+numbers. Finally I want to perform: counta("sheetname",A:A). If there is any easy way to do this command, let me know. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Name of worksheets in one worksheet
I followed your instruction but couldn't get the answer.
In define name dialog windows, there is two boxes, one in top which asks for name and if I uderstand you correctly, I type "Sheets" here. Shall I type "Sheet" or real sheet name? Second box is in buttom and asks for reference cell and I type "=GET.WORKBOOK(1)", which sounds strange for me. Anyway I click OK and now in a arbitrary cell I type: "=Sheets" but I get: GET.WORKBOOK(1)! I think there is something missing in the steps I perform. Any comment, is hilghy appreciated. Please let me what is relation between worksheet name and define name? "Lori" wrote in message ups.com... 1. Choose Insert Name Define and type Sheets and then =GET.WORKBOOK(1) in the refers to box. (If you now type =Sheets in a cell highlight it and press F9 an array of sheet names is returned in the format "[WorkbookName]SheetName") 2. To enter the list into a range of horizontal cells, select the cells and execute the formula =Sheets with Ctrl+Shift+Enter. Then you can copy and choose pastespecial values transpose in another cell and replace[*] with nothing to get a vertical list of sheetnames 3. For the last part maybe you mean =COUNTIF(A:A,Sheetnames) where sheetnames is the vertical list created in step2. Hope this makes sense! Rasoul Khoshravan wrote: I know how to obtain name of worksheets in the same worksheet. Following function will do this. MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) I want to obtain name of all worksheets in one sheet. How can I do it. Sheet names are not: [sheet]+numbers. Finally I want to perform: counta("sheetname",A:A). If there is any easy way to do this command, let me know. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Name of worksheets in one worksheet
Thanks it worked and it is very nice. Only two minor questions:
I note that this MAcro makes the list in the first worksheet. So if I have data on it, they will be over written. If I want to have the list of sheets in a sheet other than 1st one, how should I amend the MAcro? 2- Also the sequense of names is from 1st to last sheet. IS it possible to have them reverse (from last to first)? PS) What is CodeName? "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Sub CreateListOfSheetsOnFirstSheet() Dim ws As Worksheet For i = 1 To Worksheets.Count With Worksheets(1) Set ws = Worksheets(i) .Cells(i, 1).Value = ws.Name .Cells(i, 2).Value = ws.CodeName End With Next i End Sub Gord Dibben MS Excel MVP On Thu, 19 Oct 2006 19:23:27 +0900, "Rasoul Khoshravan" wrote: I know how to obtain name of worksheets in the same worksheet. Following function will do this. MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) I want to obtain name of all worksheets in one sheet. How can I do it. Sheet names are not: [sheet]+numbers. Finally I want to perform: counta("sheetname",A:A). If there is any easy way to do this command, let me know. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Name of worksheets in one worksheet
1. Choose Insert Name Define and type Sheets and then
=GET.WORKBOOK(1) in the refers to box. (If you now type =Sheets in a cell highlight it and press F9 an array of sheet names is returned in the format "[WorkbookName]SheetName") 2. To enter the list into a range of horizontal cells, select the cells and execute the formula =Sheets with Ctrl+Shift+Enter. Then you can copy and choose pastespecial values transpose in another cell and replace[*] with nothing to get a vertical list of sheetnames 3. For the last part maybe you mean =COUNTIF(A:A,Sheetnames) where sheetnames is the vertical list created in step2. Hope this makes sense! Rasoul Khoshravan wrote: I know how to obtain name of worksheets in the same worksheet. Following function will do this. MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) I want to obtain name of all worksheets in one sheet. How can I do it. Sheet names are not: [sheet]+numbers. Finally I want to perform: counta("sheetname",A:A). If there is any easy way to do this command, let me know. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Name of worksheets in one worksheet
Sub CreateListOfSheetsOnFirstSheet()
Dim ws As Worksheet For i = 1 To Worksheets.Count With Worksheets(1) Set ws = Worksheets(i) .Cells(i, 1).Value = ws.Name .Cells(i, 2).Value = ws.CodeName End With Next i End Sub Gord Dibben MS Excel MVP On Thu, 19 Oct 2006 19:23:27 +0900, "Rasoul Khoshravan" wrote: I know how to obtain name of worksheets in the same worksheet. Following function will do this. MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) I want to obtain name of all worksheets in one sheet. How can I do it. Sheet names are not: [sheet]+numbers. Finally I want to perform: counta("sheetname",A:A). If there is any easy way to do this command, let me know. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Name of worksheets in one worksheet
Just add a new sheet......here is amended code.
Sub CreateListOfSheetsOnFirstSheet() Dim ws As Worksheet Worksheets.Add For i = 1 To Worksheets.Count With Worksheets(1) Set ws = Worksheets(i) .Cells(i, 1).Value = ws.Name .Cells(i, 2).Value = ws.CodeName End With Next i End Sub To get them in reverse order is beyond me currently, but I will work on it and hope someone else jumps in and saves me the brain-drain. Codename is how Excel numbers your sheet, which can be different than your actual sheetnames. This is how Excel keeps track of sheets. ALT + F11 to open VB Editor. Find your workbook/project and expand Excel Objects. You will see the codenames and sheetnames. If you want a list without the codesnames use this macro. Private Sub ListSheets() Dim rng As Range Dim i As Integer Worksheets.Add Set rng = Range("A1") For Each Sheet In ActiveWorkbook.Sheets rng.Offset(i, 0).Value = Sheet.Name i = i + 1 Next Sheet End Sub Gord On Fri, 20 Oct 2006 00:17:38 +0900, "Rasoul Khoshravan" wrote: Thanks it worked and it is very nice. Only two minor questions: I note that this MAcro makes the list in the first worksheet. So if I have data on it, they will be over written. If I want to have the list of sheets in a sheet other than 1st one, how should I amend the MAcro? 2- Also the sequense of names is from 1st to last sheet. IS it possible to have them reverse (from last to first)? PS) What is CodeName? "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . Sub CreateListOfSheetsOnFirstSheet() Dim ws As Worksheet For i = 1 To Worksheets.Count With Worksheets(1) Set ws = Worksheets(i) .Cells(i, 1).Value = ws.Name .Cells(i, 2).Value = ws.CodeName End With Next i End Sub Gord Dibben MS Excel MVP On Thu, 19 Oct 2006 19:23:27 +0900, "Rasoul Khoshravan" wrote: I know how to obtain name of worksheets in the same worksheet. Following function will do this. MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) I want to obtain name of all worksheets in one sheet. How can I do it. Sheet names are not: [sheet]+numbers. Finally I want to perform: counta("sheetname",A:A). If there is any easy way to do this command, let me know. Gord Dibben MS Excel MVP |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Name of worksheets in one worksheet
Thanks a lot.
"Gord Dibben" <gorddibbATshawDOTca wrote in message ... Just add a new sheet......here is amended code. Sub CreateListOfSheetsOnFirstSheet() Dim ws As Worksheet Worksheets.Add For i = 1 To Worksheets.Count With Worksheets(1) Set ws = Worksheets(i) .Cells(i, 1).Value = ws.Name .Cells(i, 2).Value = ws.CodeName End With Next i End Sub To get them in reverse order is beyond me currently, but I will work on it and hope someone else jumps in and saves me the brain-drain. Codename is how Excel numbers your sheet, which can be different than your actual sheetnames. This is how Excel keeps track of sheets. ALT + F11 to open VB Editor. Find your workbook/project and expand Excel Objects. You will see the codenames and sheetnames. If you want a list without the codesnames use this macro. Private Sub ListSheets() Dim rng As Range Dim i As Integer Worksheets.Add Set rng = Range("A1") For Each Sheet In ActiveWorkbook.Sheets rng.Offset(i, 0).Value = Sheet.Name i = i + 1 Next Sheet End Sub Gord On Fri, 20 Oct 2006 00:17:38 +0900, "Rasoul Khoshravan" wrote: Thanks it worked and it is very nice. Only two minor questions: I note that this MAcro makes the list in the first worksheet. So if I have data on it, they will be over written. If I want to have the list of sheets in a sheet other than 1st one, how should I amend the MAcro? 2- Also the sequense of names is from 1st to last sheet. IS it possible to have them reverse (from last to first)? PS) What is CodeName? "Gord Dibben" <gorddibbATshawDOTca wrote in message . .. Sub CreateListOfSheetsOnFirstSheet() Dim ws As Worksheet For i = 1 To Worksheets.Count With Worksheets(1) Set ws = Worksheets(i) .Cells(i, 1).Value = ws.Name .Cells(i, 2).Value = ws.CodeName End With Next i End Sub Gord Dibben MS Excel MVP On Thu, 19 Oct 2006 19:23:27 +0900, "Rasoul Khoshravan" wrote: I know how to obtain name of worksheets in the same worksheet. Following function will do this. MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) I want to obtain name of all worksheets in one sheet. How can I do it. Sheet names are not: [sheet]+numbers. Finally I want to perform: counta("sheetname",A:A). If there is any easy way to do this command, let me know. Gord Dibben MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display data from multiple worksheets' cells in one worksheet | Excel Worksheet Functions | |||
I need to compare two worksheets and create a new worksheet | Excel Discussion (Misc queries) | |||
Consolidate different worksheets into one worksheet | Excel Discussion (Misc queries) | |||
Linking Several Worksheets to One Worksheet | Excel Discussion (Misc queries) | |||
how do i link a number of worksheets to one master worksheet? | Excel Worksheet Functions |