Sheet Tabs

R

RedFive

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
 
C

Chip Pearson

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

RedFive

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

Gord Dibben

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
 
Top