| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sheet renaming and use as data
I have a file with about 80 worksheets. How can I create a list in another
sheet that reflects all the sheet names |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sheet renaming and use as data
Hi,
Right click the sheet tab of the 'other' sheet, View code and paste this in and run it Sub standard() Dim x As Long For x = 1 To Worksheets.Count Cells(x, 1).Value = Sheets(x).Name Next End Sub Mike "Ernst - EXE Graphics" wrote: I have a file with about 80 worksheets. How can I create a list in another sheet that reflects all the sheet names |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sheet renaming and use as data
Thanks Mike. That does the trick. Another question:
Now I have a list of names and I want to use the list with a formula to calculate values from the sheet in the list: sheet1 =+'sheet1'!E19+'sheet1'!G19 sheet2 =+'sheet2'!E19+'sheet2'!G19 How can I get the formula to get the sheet name from the list that was created? "Mike H" wrote: Hi, Right click the sheet tab of the 'other' sheet, View code and paste this in and run it Sub standard() Dim x As Long For x = 1 To Worksheets.Count Cells(x, 1).Value = Sheets(x).Name Next End Sub Mike "Ernst - EXE Graphics" wrote: I have a file with about 80 worksheets. How can I create a list in another sheet that reflects all the sheet names |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sheet renaming and use as data
Try,
=SUM(INDIRECT(A1 & "!" & "E19:G19")) Drag down as required Mike "Ernst - EXE Graphics" wrote: Thanks Mike. That does the trick. Another question: Now I have a list of names and I want to use the list with a formula to calculate values from the sheet in the list: sheet1 =+'sheet1'!E19+'sheet1'!G19 sheet2 =+'sheet2'!E19+'sheet2'!G19 How can I get the formula to get the sheet name from the list that was created? "Mike H" wrote: Hi, Right click the sheet tab of the 'other' sheet, View code and paste this in and run it Sub standard() Dim x As Long For x = 1 To Worksheets.Count Cells(x, 1).Value = Sheets(x).Name Next End Sub Mike "Ernst - EXE Graphics" wrote: I have a file with about 80 worksheets. How can I create a list in another sheet that reflects all the sheet names |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sheet renaming and use as data
Try this:
=INDIRECT("'"&A1&"'!E19")+INDIRECT("'"&A1&"'!G19") in B1 and then copy down. An alternative is to amend the macro and do it in there. Hope this helps. Pete On Jul 25, 11:19*am, Ernst - EXE Graphics wrote: Thanks Mike. That does the trick. Another question: Now I have a list of names and I want to use the list with a formula to calculate values from the sheet in the list: sheet1 * * * *=+'sheet1'!E19+'sheet1'!G19 sheet2 * * * *=+'sheet2'!E19+'sheet2'!G19 How can I get the formula to get the sheet name from the list that was created? "Mike H" wrote: Hi, Right click the sheet tab of the 'other' sheet, View code and paste this in and run it Sub standard() Dim x As Long For x = 1 To Worksheets.Count Cells(x, 1).Value = Sheets(x).Name Next End Sub Mike "Ernst - EXE Graphics" wrote: I have a file with about 80 worksheets. How can I create a list in another sheet that reflects all the sheet names- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sheet renaming and use as data
Ah,
You wanted E19+G19 not E19:G19 Try this instead =INDIRECT("'"&A1&"'!E19")+INDIRECT("'"&A1&"'!G19") Mike "Mike H" wrote: Try, =SUM(INDIRECT(A1 & "!" & "E19:G19")) Drag down as required Mike "Ernst - EXE Graphics" wrote: Thanks Mike. That does the trick. Another question: Now I have a list of names and I want to use the list with a formula to calculate values from the sheet in the list: sheet1 =+'sheet1'!E19+'sheet1'!G19 sheet2 =+'sheet2'!E19+'sheet2'!G19 How can I get the formula to get the sheet name from the list that was created? "Mike H" wrote: Hi, Right click the sheet tab of the 'other' sheet, View code and paste this in and run it Sub standard() Dim x As Long For x = 1 To Worksheets.Count Cells(x, 1).Value = Sheets(x).Name Next End Sub Mike "Ernst - EXE Graphics" wrote: I have a file with about 80 worksheets. How can I create a list in another sheet that reflects all the sheet names |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sheet renaming and use as data
Hi Mike
Do I use this formula as is? Distell Winery R 28.00 SAB Miller R 358.36 The value of R28.00 is the formula and Distell Winery is cell "A3" from the macro to list sheet names "Mike H" wrote: Try, =SUM(INDIRECT(A1 & "!" & "E19:G19")) Drag down as required Mike "Ernst - EXE Graphics" wrote: Thanks Mike. That does the trick. Another question: Now I have a list of names and I want to use the list with a formula to calculate values from the sheet in the list: sheet1 =+'sheet1'!E19+'sheet1'!G19 sheet2 =+'sheet2'!E19+'sheet2'!G19 How can I get the formula to get the sheet name from the list that was created? "Mike H" wrote: Hi, Right click the sheet tab of the 'other' sheet, View code and paste this in and run it Sub standard() Dim x As Long For x = 1 To Worksheets.Count Cells(x, 1).Value = Sheets(x).Name Next End Sub Mike "Ernst - EXE Graphics" wrote: I have a file with about 80 worksheets. How can I create a list in another sheet that reflects all the sheet names |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sheet renaming and use as data
Hi Pete.
Works like a charm. Thanks "Pete_UK" wrote: Try this: =INDIRECT("'"&A1&"'!E19")+INDIRECT("'"&A1&"'!G19") in B1 and then copy down. An alternative is to amend the macro and do it in there. Hope this helps. Pete On Jul 25, 11:19 am, Ernst - EXE Graphics wrote: Thanks Mike. That does the trick. Another question: Now I have a list of names and I want to use the list with a formula to calculate values from the sheet in the list: sheet1 =+'sheet1'!E19+'sheet1'!G19 sheet2 =+'sheet2'!E19+'sheet2'!G19 How can I get the formula to get the sheet name from the list that was created? "Mike H" wrote: Hi, Right click the sheet tab of the 'other' sheet, View code and paste this in and run it Sub standard() Dim x As Long For x = 1 To Worksheets.Count Cells(x, 1).Value = Sheets(x).Name Next End Sub Mike "Ernst - EXE Graphics" wrote: I have a file with about 80 worksheets. How can I create a list in another sheet that reflects all the sheet names- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sheet renaming and use as data
Ernst,
I'm a bit lost on this latest question. I can understand the sheet names (Distell winery etc) in column A but where does R28.0 come from? You say it's a formula but it looks more like a strange formula result to me. Mike I tink from the 2 examles I gave you that you can see how to refer to another sheet using the sheet name f "Ernst - EXE Graphics" wrote: Hi Mike Do I use this formula as is? Distell Winery R 28.00 SAB Miller R 358.36 The value of R28.00 is the formula and Distell Winery is cell "A3" from the macro to list sheet names "Mike H" wrote: Try, =SUM(INDIRECT(A1 & "!" & "E19:G19")) Drag down as required Mike "Ernst - EXE Graphics" wrote: Thanks Mike. That does the trick. Another question: Now I have a list of names and I want to use the list with a formula to calculate values from the sheet in the list: sheet1 =+'sheet1'!E19+'sheet1'!G19 sheet2 =+'sheet2'!E19+'sheet2'!G19 How can I get the formula to get the sheet name from the list that was created? "Mike H" wrote: Hi, Right click the sheet tab of the 'other' sheet, View code and paste this in and run it Sub standard() Dim x As Long For x = 1 To Worksheets.Count Cells(x, 1).Value = Sheets(x).Name Next End Sub Mike "Ernst - EXE Graphics" wrote: I have a file with about 80 worksheets. How can I create a list in another sheet that reflects all the sheet names |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sheet renaming and use as data
Hi mike
Problem is solved when the value of R28.00 is calculated with: =INDIRECT("'"&A1&"'!E19")+INDIRECT("'"&A1&"'!G19") Thanks "Mike H" wrote: Ernst, I'm a bit lost on this latest question. I can understand the sheet names (Distell winery etc) in column A but where does R28.0 come from? You say it's a formula but it looks more like a strange formula result to me. Mike I tink from the 2 examles I gave you that you can see how to refer to another sheet using the sheet name f "Ernst - EXE Graphics" wrote: Hi Mike Do I use this formula as is? Distell Winery R 28.00 SAB Miller R 358.36 The value of R28.00 is the formula and Distell Winery is cell "A3" from the macro to list sheet names "Mike H" wrote: Try, =SUM(INDIRECT(A1 & "!" & "E19:G19")) Drag down as required Mike "Ernst - EXE Graphics" wrote: Thanks Mike. That does the trick. Another question: Now I have a list of names and I want to use the list with a formula to calculate values from the sheet in the list: sheet1 =+'sheet1'!E19+'sheet1'!G19 sheet2 =+'sheet2'!E19+'sheet2'!G19 How can I get the formula to get the sheet name from the list that was created? "Mike H" wrote: Hi, Right click the sheet tab of the 'other' sheet, View code and paste this in and run it Sub standard() Dim x As Long For x = 1 To Worksheets.Count Cells(x, 1).Value = Sheets(x).Name Next End Sub Mike "Ernst - EXE Graphics" wrote: I have a file with about 80 worksheets. How can I create a list in another sheet that reflects all the sheet names |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sheet renaming and use as data
You're welcome - thanks for feeding back.
Pete On Jul 25, 12:05*pm, Ernst - EXE Graphics wrote: Hi Pete. Works like a charm. Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Prefix .xls] after renaming a sheet? | Excel Discussion (Misc queries) | |||
| Renaming a sheet | Excel Discussion (Misc queries) | |||
| Get macro to run on any worksheet without renaming the sheet? | Excel Discussion (Misc queries) | |||
| renaming sheet tabs in a sequence | Excel Worksheet Functions | |||
| Naming & renaming a sheet tab | Excel Worksheet Functions | |||
Linear Mode
