Link to a formula.

  • Thread starter Thread starter AFB
  • Start date Start date
A

AFB

Hello,

I hope I can explain what I'm looking for.

Is it possible to link a formula to another sheet??

I'm trying to use a similiar formula to the "If" function,
but to create all the possibilities, it's almost
impossible because it has lots of arguments. What I'm
trying is using or the "offset" function or the "lookup"
get the correspondent formula for the given situation.
Since this can happen in any row, I solved part of the
problem using the "Indirect" and the "Row" function, as
given the following example in sheet1:

INDIRECT(ADDRESS(ROW();3))*INDIRECT(ADDRESS(ROW();5))
*INDIRECT(ADDRESS(ROW();9))

This will according to the row you are (for example row 3)
give:

C3*E3*I3

or C7*E7*I7, if you copy the formula to a cell in row 7.

What I'm asking is if it's possible using the "Offset" or
the "Lookup" function get the correspondent formula, like
the previous one, since depending on the argument the
formula is different, in another sheet.
I'm trying to link a formula and not the result in it.

If for example I have in Sheet3!D4 the formula below

"=INDIRECT(ADDRESS(ROW();3))*INDIRECT(ADDRESS(ROW();5))
*INDIRECT(ADDRESS(ROW();9))"

In cell Sheet1!L15, I have a link or the "Offset" or
the "Lookup" that returns me cell Sheet3!D4, it will
return me the value of:

Sheet3!C4*Sheet3!E3*Sheet3!I3

If I use in Sheet3!D4 the formula below

"INDIRECT(ADDRESS(ROW();3))*INDIRECT(ADDRESS(ROW();5))
*INDIRECT(ADDRESS(ROW();9))"


without the "=", it will return as text:

"INDIRECT(ADDRESS(ROW();3))*INDIRECT(ADDRESS(ROW();5))
*INDIRECT(ADDRESS(ROW();9))"


Can anybody help me to link formulas??

Thank you for any help.

AFB
 
...
...
Is it possible to link a formula to another sheet?? ...
What I'm asking is if it's possible using the "Offset" or
the "Lookup" function get the correspondent formula, like
the previous one, since depending on the argument the
formula is different, in another sheet.
I'm trying to link a formula and not the result in it.

If for example I have in Sheet3!D4 the formula below

"=INDIRECT(ADDRESS(ROW();3))*INDIRECT(ADDRESS(ROW();5))
*INDIRECT(ADDRESS(ROW();9))"

In cell Sheet1!L15, I have a link or the "Offset" or
the "Lookup" that returns me cell Sheet3!D4, it will
return me the value of:

Sheet3!C4*Sheet3!E3*Sheet3!I3
Click to expand...

You can use VBA to pull formulas from other cells. There are several versions of
a function called GetFormula that have been posted in this newsgroup. See

http://www.google.com/[email protected]

If Sheet3!D4 were as you describe, then GetFormula(Sheet3!D4) would return

=INDIRECT(ADDRESS(ROW();3))*INDIRECT(ADDRESS(ROW();5))
*INDIRECT(ADDRESS(ROW();9))

(with the equal sign). Translating this so that it returns cell addresses will
be more difficult. It requires parsing the formula and replacing INDIRECT calls
with the corresponding cell addresses. Here's one approach. It has one BIG
potential bug: it assumes 'INDIRECT(' (without the single quotes) will never
appear within a double-quoted string constant.


Function foo(r As Range) As String
Dim f As String, t As String, qs As Boolean
Dim i As Long, j As Long, k As Long, n As Long, pl As Long

Set r = r.Areas(1).Cells(1, 1)
If Not r.HasFormula Then Exit Function 'return ""

f = Mid(r.Formula, 2)
n = Len(f)

k = 1
Do While k < n
j = InStr(k, f, "INDIRECT(")

If j > 0 Then
foo = foo & Mid(f, k, j - k)
pl = 1
i = j + 9

Do While i <= n And pl > 0
t = Mid(f, i, 1)

If qs And Mid(f, i, 2) = """""" Then
i = i + 1

ElseIf qs And t = """" Then
qs = False

ElseIf Not qs And t = "(" Then
pl = pl + 1

ElseIf Not qs And t = ")" Then
pl = pl - 1

End If

i = i + 1

Loop

t = Evaluate("=" & Mid(f, j, i - j)).Address(0, 0, xlA1, 1)
foo = foo & Mid(t, InStr(1, t, "]") + 1)

k = i

Else
foo = foo & Mid(f, k)
k = n + 1

End If

Loop

End Function
 
-----Original Message-----
...
...
Is it possible to link a formula to another sheet?? ...
What I'm asking is if it's possible using the "Offset" or
the "Lookup" function get the correspondent formula, like
the previous one, since depending on the argument the
formula is different, in another sheet.
I'm trying to link a formula and not the result in it.

If for example I have in Sheet3!D4 the formula below

"=INDIRECT(ADDRESS(ROW();3))*INDIRECT(ADDRESS(ROW();5))
*INDIRECT(ADDRESS(ROW();9))"

In cell Sheet1!L15, I have a link or the "Offset" or
the "Lookup" that returns me cell Sheet3!D4, it will
return me the value of:

Sheet3!C4*Sheet3!E3*Sheet3!I3
Click to expand...

You can use VBA to pull formulas from other cells. There are several versions of
a function called GetFormula that have been posted in this newsgroup. See

http://www.google.com/groups?selm=uLiAWJ8dDHA.3584% 40tk2msftngp13.phx.gbl

If Sheet3!D4 were as you describe, then GetFormula(Sheet3! D4) would return

=INDIRECT(ADDRESS(ROW();3))*INDIRECT(ADDRESS(ROW();5))
*INDIRECT(ADDRESS(ROW();9))

(with the equal sign). Translating this so that it returns cell addresses will
be more difficult. It requires parsing the formula and replacing INDIRECT calls
with the corresponding cell addresses. Here's one approach. It has one BIG
potential bug: it assumes 'INDIRECT(' (without the single quotes) will never
appear within a double-quoted string constant.


Function foo(r As Range) As String
Dim f As String, t As String, qs As Boolean
Dim i As Long, j As Long, k As Long, n As Long, pl As Long

Set r = r.Areas(1).Cells(1, 1)
If Not r.HasFormula Then Exit Function 'return ""

f = Mid(r.Formula, 2)
n = Len(f)

k = 1
Do While k < n
j = InStr(k, f, "INDIRECT(")

If j > 0 Then
foo = foo & Mid(f, k, j - k)
pl = 1
i = j + 9

Do While i <= n And pl > 0
t = Mid(f, i, 1)

If qs And Mid(f, i, 2) = """""" Then
i = i + 1

ElseIf qs And t = """" Then
qs = False

ElseIf Not qs And t = "(" Then
pl = pl + 1

ElseIf Not qs And t = ")" Then
pl = pl - 1

End If

i = i + 1

Loop

t = Evaluate("=" & Mid(f, j, i - j)).Address(0, 0, xlA1, 1)
foo = foo & Mid(t, InStr(1, t, "]") + 1)

k = i

Else
foo = foo & Mid(f, k)
k = n + 1

End If

Loop

End Function

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.
.
Click to expand...
 
Back
Top