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,

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

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

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

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

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

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
>
>