Extracting tab names

[MLK]

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,

 

[Tom Hutchins]

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("Filename",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

 

[vezerid]

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

 

[Tom Hutchins]

"In F5 on the same row" should be "In column F, in the same row"

Regards,

Hutch

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("Filename",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

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,

 

[MLK]

Excellent! I just tried this out and it works great. Many thanks.

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("Filename",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

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,

 

[MLK]

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.