Pull cell from formula

**ministe2003** · 01-19-2010, 07:04 AM

Hello,
I have a spreadsheet with two worksheets which refer to each other in formulas.
What I want to do is find the title of the column of which each formula refers.

For example, I might have sheet 1 and 2. In sheet 2 I have a formula that refers to cell A12 in sheet 1, which contains a table of data. I want to, in another cell in sheet 2, pull out the title at the top of column A.

The formulas are sporadically spread around the pages, not following any pattern and the data in sheet 1 is not unique, so lookups wouldnt necessarily work. I need to literally pull out the cell coordinates from the formula in sheet 2.

=F64*'Sheet1'!$G$11

Now, I can pull out the whole formula as a string using VBA, is it possible to just pull out the cell coordinates?

Hmm, quite a complicated explanation so sorry if its confusing, but I hope someone can help me.

Thanks!

Steven

**Richard Buttrey** · 01-19-2010, 07:17 AM

Hi,

If I've understood correctly, and assuming the formula you mention is in say A1 then the st variable from the following procedure will return the address G11. No doubt you already have a variable so you should be able to modify this to use the one you already have.

Code:

Dim st As String, i As Integer
st = Range("A1").Formula
i = InStr(st, "!")
st = Right(st, Len(st) - i)

HTH

**ministe2003** · 01-19-2010, 07:22 AM

Nice thanks, so that gives me the value $A$12 (for example) as text.
How can I put this into a formula for use with some kind of offset? for instance, now I have $A$12 as text, I want to pull the value from $A$8 because this is where the title of that column is. This means I'll need to pull the cell value as you have told me and put this is a formula with an offset in order to pull the column title.

Thanks for the help and quick reply!

Steven

**Richard Buttrey** · 01-19-2010, 07:33 AM

Hi,

Not sure whether you're wanting this information in VBA or as a cell formula so I've catered for both in the following.
Assumes the column title is 4 rows above the address found by the st variable. These additional two lines will put the column title in range("B1"), and the stTitle variable will return the same thing.

Code:

Dim st As String, i As Integer, stTitle As String
st = Range("A1").Formula
i = InStr(st, "!")
st = Right(st, Len(st) - i)

Range("B1") = "=offset(" & st & ",-4,0)"
stTitle = Range(st).Offset(-4, 0)

HTH

**ministe2003** · 01-19-2010, 07:54 AM

thanks, I've managed to crack it myself only mine isnt as nice as yours! The table in sheet 1 goes horizontally across the sheet and is split at one point and continued below. For this reason I have to convert the cell from text to a number to test if im pulling the value from the top or bottom section of the table then apply that to a formula in my vba, here is what I've got:

Code:

Sub checkFormula()
Dim form, coords, row, col As String
Dim vEval As Variant
Dim colTitle As Integer

form = Cells(44, 8).Formula

i = InStr(form, "!")                    'find where cell starts in formula
coords = Right(form, Len(form) - i)     'pull cell coords   eg $A$12
col = Left(coords, Len(coords) - 2)     'pull column        eg $A$
row = Right(coords, Len(coords) - 3)    'pull row           eg 12

colTitle = Val(row)                     'store row as ingeter
If colTitle < 15 Then
    colTitle = 4
Else                                    'set title column depending on
    colTitle = 16                       'where we're pulling data from
End If

coords = col + CStr(colTitle)           'set new coords to eg $A$4 or $A$A16

form = "'Sheet1'!" + coords             'append form with new coords

vEval = Application.Evaluate(form)      'pull data from form's location

Cells(44, 15).Value = vEval             'write into our display cell

End Sub