Reading and displaying part of a formula

  • Thread starter Thread starter melvin
  • Start date Start date
M

melvin

Hi folks,

Is it possible to get a cell to read part of a formula from anothe
cell?

For instance,

I have the formula:

=OFFSET('[ExcelBook.xls]Scotland'!$F$10,0,ROW()-123)

in cell B1 and I would like cell A1 to read and display the "Scotland
part of that cell.

Is it possible to set this up so that if the formula changed to:

=OFFSET('[ExcelBook.xls]England'!$F$10,0,ROW()-123)

it would show "England" in the adjacent cell?

Any input is greatly appreciated

cheer
 
Hi
try the following user defined function:
Function get_formula_part(rng As Range)
Dim form_str As String
Dim parse_sheet As String

If Not rng.HasFormula Then
get_formula_part = ""
Exit Function
End If
form_str = rng.Formula
form_str = Replace(form_str, "'", "")
parse_sheet = Mid(form_str, InStr(form_str, "]") + 1, _
InStr(form_str, "!") - InStr(form_str, "]") - 1)

get_formula_part = parse_sheet
End Function
 
Back
Top