Extract Sheet Name From Formula (Office 2000-2003)

Topic

New Reply

Is there a way to extract the sheet name from a formula such as +’Sheetname’!A1? I’ve tried variations of =Left, Right, and Mid but I always end up with the portion of the solution rather than the text embedded in the formula.

TIA

Reply | Quote

Replies

Can I ask the purpose?

Reply | Quote

I have a sheet that summarizes a cell from many tabs. The tab name happens to be the Dept. # – which is information I need.

Reply | Quote

You would have to use a User Defined Function. A UDF can extract the formula from a cell and work on it. The formula in the cell would have to be consistent enough for the UDF to be able to find the sheet name in the formula. I could help with the UDF, but would have to know exactly what the formula looked like.

Reply | Quote

The formula is +’Sheetname’!A1 where sheetname is 5-digits – i.e. 10100, 15000, 23100 etc. I would like to end up with a column next to the formula showing the text 10100 etc. Am I making sense? 10100 etc. is a department name/number to me and I need that info along with the $ for that department.

Reply | Quote

Does this give you what you want:


Public Function GetDept(oCell As Range) As String
Dim strWk As String
If oCell.HasFormula Then
strWk = oCell.Formula
If InStr(strWk, "!") > 0 Then
GetDept = Mid(strWk, InStr(strWk, "!") - 6, 5)
Else
GetDept = CVErr(2001)
End If
Else
GetDept = CVErr(2001)
End If
End Function


Use like this:


=GETDEPT(A1)


where A1 is the cell containing the formula.

Reply | Quote

Yes, that works great! Thanks much! Can you explain the CVErr(2001)?

Reply | Quote

That will return a #Value error if the cell does not contain a formula, or if the formula does not contain a sheetname.

Reply | Quote

Got it! Thanks again.

Reply | Quote