Sheet Tabs
 

I am trying to consolidate cells from different sheets into one master sheet.
Was wondering if there is a way to have a column that displays what sheet
the information came from. Thanks
--
RedFive

Sheet Tabs
 

I don't really understand what you are trying to do. You can use the
sheet name as a variable to specify what sheet to pull the data from.
Suppose cell C2 contains the name of an existing worksheet. The
following formula will return the value in cell A1 from whatever sheet
is named in C2.

=INDIRECT("'"&C2&"'!A1")


Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
The San Diego Project Group, LLC
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)


On Thu, 25 Sep 2008 10:19:04 -0700, RedFive
wrote:

I am trying to consolidate cells from different sheets into one master sheet.
Was wondering if there is a way to have a column that displays what sheet
the information came from. Thanks

Sheet Tabs
 

Well, I am trying to create a column in a master sheet that will specify
which sheet in the workbook the corresponding information in the same row was
pulled from. So if the information in the row A1:F1 was linked from Sheet1,
I would like a column that displays "Sheet1"
Does that make sense or make it worse?
--
RedFive


"Chip Pearson" wrote:

I don't really understand what you are trying to do. You can use the
sheet name as a variable to specify what sheet to pull the data from.
Suppose cell C2 contains the name of an existing worksheet. The
following formula will return the value in cell A1 from whatever sheet
is named in C2.

=INDIRECT("'"&C2&"'!A1")


Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
The San Diego Project Group, LLC
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)


On Thu, 25 Sep 2008 10:19:04 -0700, RedFive
wrote:

I am trying to consolidate cells from different sheets into one master sheet.
Was wondering if there is a way to have a column that displays what sheet
the information came from. Thanks

Sheet Tabs
 

See GetFormula in
http://www.mvps.org/dmcritchie/excel/formula.htm

It displays the entire formula, avoids the problem of
what you would do if the formula use parts from multiple worksheets.
You could take a a substring if you knew the exact format of the formulas.
--
HTH,
David McRitchie, former Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm



"RedFive" wrote in message ...
Well, I am trying to create a column in a master sheet that will specify
which sheet in the workbook the corresponding information in the same row was
pulled from. So if the information in the row A1:F1 was linked from Sheet1,
I would like a column that displays "Sheet1"
Does that make sense or make it worse?
--
RedFive


"Chip Pearson" wrote:

I don't really understand what you are trying to do. You can use the
sheet name as a variable to specify what sheet to pull the data from.
Suppose cell C2 contains the name of an existing worksheet. The
following formula will return the value in cell A1 from whatever sheet
is named in C2.

=INDIRECT("'"&C2&"'!A1")


Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
The San Diego Project Group, LLC
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)


On Thu, 25 Sep 2008 10:19:04 -0700, RedFive
wrote:

I am trying to consolidate cells from different sheets into one master sheet.
Was wondering if there is a way to have a column that displays what sheet
the information came from. Thanks

Sheet Tabs
 

You could use this UDF to show the sheet and address

Function showfn(myCell)
If myCell.HasFormula Then
showfn = Mid(myCell.Formula, 2)
Else
showfn = ""
End If
End Function

i.e. =showfn(A1) would return Sheetname!cellref

Alternative...........

=LEFT(showfn(A1),FIND("!",showfn(A1))-1) would return just the Sheetname


Gord Dibben MS Excel MVP

On Thu, 25 Sep 2008 11:16:18 -0700, RedFive
wrote:

Well, I am trying to create a column in a master sheet that will specify
which sheet in the workbook the corresponding information in the same row was
pulled from. So if the information in the row A1:F1 was linked from Sheet1,
I would like a column that displays "Sheet1"
Does that make sense or make it worse?