Returning sheet name from formula

is this what you're looking for? it returns the sheet name:
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1))
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
anno,

I think Mudface wants wants to pull the sheet name out of a formula. Your code will return the name of the sheet that contains the cell.
This message was edited by Al Chara on 2002-04-11 15:45
 
Upvote 0
Yes, Al, you have it right, I want the sheet name from the formula. I can do it in VB (thanks for your example too Al), but I just wondered if there was another way, as people here tend to get put off when a workbook contains macros.
 
Upvote 0
Mudface,

The key is getting a function that will look at the formula and not the value. What about writing a UDF?
This message was edited by Al Chara on 2002-04-12 08:41
 
Upvote 0
Hi Mudface,

How about this?

----------------
Function GetSheetfromFormula(refcell As Range)

Dim findexclaim As Integer
If refcell.Cells.Count <> 1 Or Not refcell.HasFormula Then
GetSheetfromFormula = CVErr(xlErrValue)
Exit Function
End If

On Error Resume Next
findexclaim = WorksheetFunction.Search("!", refcell.Formula, 1)
On Error GoTo 0
If findexclaim = 0 Then
GetSheetfromFormula = Application.Caller.Parent.Name
Else
GetSheetfromFormula = Mid(refcell.Formula, 2, findexclaim - 2)
End If
End Function
---------------------

I imagine that this could be expanded to handle 3-D references and for finding multiple sheetnames within a complex formula.

Bye,
Jay
 
Upvote 0
Cosmos,

We know how to do this in VBA. But the real question is how do you do it with just a formula in the worksheet. I am going to start a new thread with that question.
 
Upvote 0
This seems to have been shunted down to the bottom of the board by the date error that was mentioned and I've only just found it :). Thanks to all who replied, especially Al, I'll give this a go. Cheers :).
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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