Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula (Excel 2003)
Hi there,
I am looking for help with a spreadsheet formula, I have 54 Worksheets and on Line C6 of each worksheet is the Description and on E6 of each worksheet is a Name. I would like set up another worksheet that will list the information on E6 from all the other worksheets to one locations. Sheet 1 C6 D6 E6 Manager Bill Taylor Assistant Manager Brenda Davidson Sheet 2 C6 D6 E6 Manager Eric Bradley Assistant Manager Monica Davis There is a lot of other information on each worksheet, but what I am looking for is to have the names of all the Managers from the 54 worksheets on one worksheet, and also the names of all the Assistant Manager from the 54 worksheets on one worksheet. Is this possible. Thank you Newfie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula (Excel 2003)
In E6 of Sheet1 thru Sheet4 I have these names: Brown, Smith, Jones
On summary sheet in A3 (but it could be in any cell) I entered =INDIRECT("Sheet"&ROW(A1)&"!E6") I copied this down the column to get Brown Smith Jones It get a bit more complex if the sheets do not have the names Sheet1, Sheet2 ..... best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Newfie809" wrote in message ... Hi there, I am looking for help with a spreadsheet formula, I have 54 Worksheets and on Line C6 of each worksheet is the Description and on E6 of each worksheet is a Name. I would like set up another worksheet that will list the information on E6 from all the other worksheets to one locations. Sheet 1 C6 D6 E6 Manager Bill Taylor Assistant Manager Brenda Davidson Sheet 2 C6 D6 E6 Manager Eric Bradley Assistant Manager Monica Davis There is a lot of other information on each worksheet, but what I am looking for is to have the names of all the Managers from the 54 worksheets on one worksheet, and also the names of all the Assistant Manager from the 54 worksheets on one worksheet. Is this possible. Thank you Newfie |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula (Excel 2003)
Thank you for the information, I see what you mean if the sheets have a
different name which they do. I will keep working on it. Thanks again. -- Newfie "Bernard Liengme" wrote: In E6 of Sheet1 thru Sheet4 I have these names: Brown, Smith, Jones On summary sheet in A3 (but it could be in any cell) I entered =INDIRECT("Sheet"&ROW(A1)&"!E6") I copied this down the column to get Brown Smith Jones It get a bit more complex if the sheets do not have the names Sheet1, Sheet2 ..... best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Newfie809" wrote in message ... Hi there, I am looking for help with a spreadsheet formula, I have 54 Worksheets and on Line C6 of each worksheet is the Description and on E6 of each worksheet is a Name. I would like set up another worksheet that will list the information on E6 from all the other worksheets to one locations. Sheet 1 C6 D6 E6 Manager Bill Taylor Assistant Manager Brenda Davidson Sheet 2 C6 D6 E6 Manager Eric Bradley Assistant Manager Monica Davis There is a lot of other information on each worksheet, but what I am looking for is to have the names of all the Managers from the 54 worksheets on one worksheet, and also the names of all the Assistant Manager from the 54 worksheets on one worksheet. Is this possible. Thank you Newfie |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula (Excel 2003)
If the sheet name end with a number all is OK
=INDIRECT("'My Datasheet'"&ROW(A1)&"!E6") that is =INDIRECT( double-quote single-quote My Datasheet single-quote double quote &ROW(A1)&"!E6") Otherwise list the sheet names in A1:A10 (for example) In B1 =INDIRECT(A1&"!E6") copy down column -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Newfie809" wrote in message ... Thank you for the information, I see what you mean if the sheets have a different name which they do. I will keep working on it. Thanks again. -- Newfie "Bernard Liengme" wrote: In E6 of Sheet1 thru Sheet4 I have these names: Brown, Smith, Jones On summary sheet in A3 (but it could be in any cell) I entered =INDIRECT("Sheet"&ROW(A1)&"!E6") I copied this down the column to get Brown Smith Jones It get a bit more complex if the sheets do not have the names Sheet1, Sheet2 ..... best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Newfie809" wrote in message ... Hi there, I am looking for help with a spreadsheet formula, I have 54 Worksheets and on Line C6 of each worksheet is the Description and on E6 of each worksheet is a Name. I would like set up another worksheet that will list the information on E6 from all the other worksheets to one locations. Sheet 1 C6 D6 E6 Manager Bill Taylor Assistant Manager Brenda Davidson Sheet 2 C6 D6 E6 Manager Eric Bradley Assistant Manager Monica Davis There is a lot of other information on each worksheet, but what I am looking for is to have the names of all the Managers from the 54 worksheets on one worksheet, and also the names of all the Assistant Manager from the 54 worksheets on one worksheet. Is this possible. Thank you Newfie |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula (Excel 2003)
Use this macro to get a list of sheets into column A of Summary sheet.
Sub CreateListOfSheetsOnSummarySheet() Dim ws As Worksheet For I = 1 To Worksheets.Count With Worksheets("Summary") Set ws = Worksheets(I) .Cells(I, 1).Value = ws.Name End With Next I End Sub In B1 enter =INDIRECT(A1&"!C6") and copy down. In C1 enter =INDIRECT(A1&"!E6") and copy down. Gord Dibben MS Excel MVP On Tue, 23 Jun 2009 10:56:01 -0700, Newfie809 wrote: Thank you for the information, I see what you mean if the sheets have a different name which they do. I will keep working on it. Thanks again. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula (Excel 2003)
A handy one to file away for the future
Thanks best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Use this macro to get a list of sheets into column A of Summary sheet. Sub CreateListOfSheetsOnSummarySheet() Dim ws As Worksheet For I = 1 To Worksheets.Count With Worksheets("Summary") Set ws = Worksheets(I) .Cells(I, 1).Value = ws.Name End With Next I End Sub In B1 enter =INDIRECT(A1&"!C6") and copy down. In C1 enter =INDIRECT(A1&"!E6") and copy down. Gord Dibben MS Excel MVP On Tue, 23 Jun 2009 10:56:01 -0700, Newfie809 wrote: Thank you for the information, I see what you mean if the sheets have a different name which they do. I will keep working on it. Thanks again. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula (Excel 2003)
Hi,
You may also try this. Go to Insert Name Define and in the name box, type sheets. in the refers to box, type =TRANSPOSE(GET.WORKBOOK(1))&T(NOW()). Now select a range of cells (select as many cells as there are sheets) and enter the following array formula (Ctrl+Shift+Enter) =MID(sheets,FIND("]",sheets)+1,255). This will give you all the sheet names in the workbook. Please note that this will ask you to Enable macros - click on yes. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Newfie809" wrote in message ... Thank you for the information, I see what you mean if the sheets have a different name which they do. I will keep working on it. Thanks again. -- Newfie "Bernard Liengme" wrote: In E6 of Sheet1 thru Sheet4 I have these names: Brown, Smith, Jones On summary sheet in A3 (but it could be in any cell) I entered =INDIRECT("Sheet"&ROW(A1)&"!E6") I copied this down the column to get Brown Smith Jones It get a bit more complex if the sheets do not have the names Sheet1, Sheet2 ..... best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Newfie809" wrote in message ... Hi there, I am looking for help with a spreadsheet formula, I have 54 Worksheets and on Line C6 of each worksheet is the Description and on E6 of each worksheet is a Name. I would like set up another worksheet that will list the information on E6 from all the other worksheets to one locations. Sheet 1 C6 D6 E6 Manager Bill Taylor Assistant Manager Brenda Davidson Sheet 2 C6 D6 E6 Manager Eric Bradley Assistant Manager Monica Davis There is a lot of other information on each worksheet, but what I am looking for is to have the names of all the Managers from the 54 worksheets on one worksheet, and also the names of all the Assistant Manager from the 54 worksheets on one worksheet. Is this possible. Thank you Newfie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 - SUM formula | Excel Discussion (Misc queries) | |||
Excel 2003 formula | Excel Worksheet Functions | |||
Help with Formula (Excel 2003) | Excel Discussion (Misc queries) | |||
Excel (2003) formula | Excel Discussion (Misc queries) | |||
How do I get the formula bar in excel 2003 | Excel Discussion (Misc queries) |