Returning sheet name from formula

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
If a have a formula such as: -

=Sheet2!A1

is there a way of returning which sheet is referenced by the formula (ie in a separate cell I would like to have Sheet2 returned)?

Cheers for any help.
 
Upvote 0
Another option. Why not specify the sheet in another cell and pull it for the formula. So in cell A1 is the value:
Sheet2
and in cell A2 is the formula:
=INDIRECT(A1 & "!B17")
 
Upvote 0
Thanks, Rob, I did think of trying that, but thought it would be easier just to Copy the data from the source file and Paste it as a link (there are four spreadsheets, each with 3 sheets and I wanted around a 1000 rows by 20 columns from each sheet linked so I could summarise the source data via a pivot table). Might create another sheet, using your suggestion and see if that can be done easily (sigh) :).
 
Upvote 0
How about using VBA:
Code:
Dim formula As String
Dim ExlLoc As Integer
formula = ActiveCell.formula
ExlLoc = Application.WorksheetFunction.Search("!", formula)
ActiveCell.Offset(0, 1) = Mid(formula, 2, ExlLoc - 2)
 
Upvote 0
Couldn't you just use one of Aladin's or Mark W's formulas that use =LEFT(A1...blah blah blah. I think I saw something like this posted in the last day or so.
 
Upvote 0
Mark: You mean

=LEFT(C20,FIND("!",C20,1)-1)
and that will give Sheet2
This message was edited by Yogi Anand on 2002-04-11 14:13
 
Upvote 0
I may be wrong here, but I don't think that formula works. Doesn't find only search the value of the cell and not the formula?
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top