Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting tab names
If this question is out here twice, I apoligize. I submitted it once, but
don't think the question got posted. I have 6 tabs in an Excel worksheet. I create a 7th tab by consolidating all the data from the other 6 tabs. Is it possible (by using a function) to display which tab name the data came from? For example, if 6 rows of data came from tab 1, I would like to display the tab name with those 6 rows, and so on. Thanks, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting tab names
If (for example) a cell on your consolidating sheet is pulling its data from
Sheet1, cell A4, the following formula will return the sheet name (Sheet1): =RIGHT(CELL("Filename",Sheet1!A4),LEN(CELL("Filena me",Sheet1!A4))-FIND("]",CELL("Filename",Sheet1!A4),1)) Alternatively, you could put a formula like the following in a cell (say, E4): =CELL("Filename",Sheet1!A4) In F5 on the same row, enter: =RIGHT(E4,LEN(E4)-FIND("]",E4,1)) Hide column E. Hope this helps, Hutch "MLK" wrote: If this question is out here twice, I apoligize. I submitted it once, but don't think the question got posted. I have 6 tabs in an Excel worksheet. I create a 7th tab by consolidating all the data from the other 6 tabs. Is it possible (by using a function) to display which tab name the data came from? For example, if 6 rows of data came from tab 1, I would like to display the tab name with those 6 rows, and so on. Thanks, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting tab names
You have two options:
Either you do it manually or you somehow automate the process with VBA. In the latter case you would somehow select the cells to move to the consolidation sheet and VBA would add the source sheet name next to the transferred data. HTH Kostis Vezerides |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting tab names
"In F5 on the same row" should be "In column F, in the same row"
Regards, Hutch "Tom Hutchins" wrote: If (for example) a cell on your consolidating sheet is pulling its data from Sheet1, cell A4, the following formula will return the sheet name (Sheet1): =RIGHT(CELL("Filename",Sheet1!A4),LEN(CELL("Filena me",Sheet1!A4))-FIND("]",CELL("Filename",Sheet1!A4),1)) Alternatively, you could put a formula like the following in a cell (say, E4): =CELL("Filename",Sheet1!A4) In F5 on the same row, enter: =RIGHT(E4,LEN(E4)-FIND("]",E4,1)) Hide column E. Hope this helps, Hutch "MLK" wrote: If this question is out here twice, I apoligize. I submitted it once, but don't think the question got posted. I have 6 tabs in an Excel worksheet. I create a 7th tab by consolidating all the data from the other 6 tabs. Is it possible (by using a function) to display which tab name the data came from? For example, if 6 rows of data came from tab 1, I would like to display the tab name with those 6 rows, and so on. Thanks, |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting tab names
Excellent! I just tried this out and it works great. Many thanks.
"Tom Hutchins" wrote: If (for example) a cell on your consolidating sheet is pulling its data from Sheet1, cell A4, the following formula will return the sheet name (Sheet1): =RIGHT(CELL("Filename",Sheet1!A4),LEN(CELL("Filena me",Sheet1!A4))-FIND("]",CELL("Filename",Sheet1!A4),1)) Alternatively, you could put a formula like the following in a cell (say, E4): =CELL("Filename",Sheet1!A4) In F5 on the same row, enter: =RIGHT(E4,LEN(E4)-FIND("]",E4,1)) Hide column E. Hope this helps, Hutch "MLK" wrote: If this question is out here twice, I apoligize. I submitted it once, but don't think the question got posted. I have 6 tabs in an Excel worksheet. I create a 7th tab by consolidating all the data from the other 6 tabs. Is it possible (by using a function) to display which tab name the data came from? For example, if 6 rows of data came from tab 1, I would like to display the tab name with those 6 rows, and so on. Thanks, |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting tab names
I'm doing this with functions at the moment - but would like to eventually
switch to VBA ... I'm currently a newbie to VBA. Thanks. "vezerid" wrote: You have two options: Either you do it manually or you somehow automate the process with VBA. In the latter case you would somehow select the cells to move to the consolidation sheet and VBA would add the source sheet name next to the transferred data. HTH Kostis Vezerides |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Local Names to Global Names | Excel Worksheet Functions | |||
Populate a column by extracting unique values from another column? | Excel Worksheet Functions | |||
Extracting worksheet names.... | Excel Worksheet Functions | |||
Extracting names in a cell | Excel Discussion (Misc queries) | |||
Extracting File Names and Properties | Excel Worksheet Functions |