Indirectly referencing a formula on a different sheet

A

Andy

I'm not sure if this is possible, so any suggestions welcome.

I have a sheet that I use as a template which I then copy to create
instances of this sheet. What I would like to do is to reference a
formula for a cell on the template sheet rather than have a copy of the
formula. This would allow the formula to be modified on the template
and for all the instances to use the new formula.

I had a look at the indirect function, but this only seems to allow
strings to be converted to references. What i'm trying to do is
indirectly use the referenced formula.

Thanks
 
D

David McRitchie

Hi Andy,
Without providing an example of what you want, your question is
very vague with an answer ranging from a simple assignment to
obtain the value of the formula on the other page to something
involving the INDIRECT Worksheet Function which somehow
ties into a formula on another page.

By first searching websites via Google or newsgroups
via Google Groups you would either find an answer or at least be
able to ask a more focused question.
 
A

Andy

Sorry, that was an attempt to try and keep the question simple as my
application is rather complicated to explain using automation between
visio and excel.

If i have a sheet, called Sheet1, with cells

A1 "No"
A2 "Yes"
A3 =AND(A1="Yes",A2="Yes")

What I would like now is a sheet, called Sheet2 with it's cell A3
formula referencing the formula in Sheet1, A3. So any changes to the A3
cell formula in Sheet1 will automatically be picked up by the other
sheets.

Using INDIRECT only gives me the value of Sheet1!A3 call.
 
D

David McRitchie

Hi Andy,
Place an = sign into the receiving cell on sheet2
then click on cell A3 on sheet1 hit enter and you
will return to sheet2 with the completed formula
it will look like =sheet1!A3
if sheet1 were actually sheet one you
would see ='sheet one'!A3
 
A

Andy

Thats not what I am trying to acheive. The formula on sheet 1
references cells on sheet1. What I was trying to do was have sheet 2
reference cells on sheet 2, but using the same formula as on Sheet 1.
Rather than have a copy of the formula, I wanted to reference it. Sheet
1 will be copied lots of times. I was hoping if the formula on Sheet 1
was changed, then the other sheets would then use that same changed
formula. At the moment they have copies, so have to be updated
individually.
 
D

David McRitchie

Sorry that isn't correct, it is taking the activesheet and not the
sheet that the formula is on.
 
H

Harlan Grove

David McRitchie said:
Sorry that isn't correct, it is taking the activesheet and not the
sheet that the formula is on.
Click to expand...

?

You mean it's taking values for cell references from the active sheet? Isn't
that what it should be doing? As for your 'cell' argument, you should define
it explicitly as a range object. It's specific to the worhsheet in which the
range lies, not to the active sheet.
 
D

David McRitchie

Thanks Harlan, Corrected function uses parent "range object".
Now use of Ctrl+Alt+F9 (Recalculate all cells on all worksheets in
all open workbooks) can be used from any sheet without adverse effects.

UseSameAs will use the same Formula or Constant as used in the
referenced cell of another worksheet.

Function UseSameAs(cell As Range)
'-- Use the same Formula as used in the referenced cell
'-- http://mvps.org/dmcritchie/formula.htm#usesameas 2005-09-03 .excel
UseSameAs = Parent.Application.Evaluate(cell.Formula)
End Function

sheet1!B4: 77
sheet1!C4: =5*B4 [displays 385]
sheet2!B4: 88
sheet2!C4: =personal.xls!UseSameAs(sheet1!B4) [displays 440]
 
D

Dave Peterson

I think that that's still a problem.

I created a workbook with 3 worksheets.

In sheet1!a1, I put: 11

In Sheet2!b1, I put: 333
In sheet2!A1, I put: =Sheet1!A1+B1

In Sheet3!a1, I put: =usesameas(Sheet2!A1)
In Sheet3!b1, I put: 33

I added application.volatile to the UDF.

I put =rand() in C1 of both sheet2 and sheet3

I did a window|new window so I could show both Sheet2 and Sheet3.

I selected C1 of sheet2 and hit F2|enter (to force a recalc of =rand() and a
recalc of the UDF).

Sheet2!a1 returned 344
Sheet3!a1 returned 344

I selected C1 of sheet3 and did the same:

Sheet2!a1 returned 344
but this time sheet3!a1 returned 44 (what I wanted).

If I changed the UDF to this:

Option Explicit
Function UseSameAs(cell As Range)

'-- Use the same Formula as used in the referenced cell
'-- http://mvps.org/dmcritchie/formula.htm#usesameas 2005-09-03 .excel

Application.Volatile
UseSameAs = Application.Caller.Parent.Evaluate(cell.Formula)

End Function

I got what what I wanted:

Sheet2!a1 returned 344
sheet3!a1 returned 44
No matter what the activesheet was when I recalced.

(I like the application.volatile line in the UDF.)

ps.

I think you wanted this as your link:

http://mvps.org/dmcritchie/excel/formula.htm#usesameas



David said:
Thanks Harlan, Corrected function uses parent "range object".
Now use of Ctrl+Alt+F9 (Recalculate all cells on all worksheets in
all open workbooks) can be used from any sheet without adverse effects.

UseSameAs will use the same Formula or Constant as used in the
referenced cell of another worksheet.

Function UseSameAs(cell As Range)
'-- Use the same Formula as used in the referenced cell
'-- http://mvps.org/dmcritchie/formula.htm#usesameas 2005-09-03 .excel
UseSameAs = Parent.Application.Evaluate(cell.Formula)
End Function

sheet1!B4: 77
sheet1!C4: =5*B4 [displays 385]
sheet2!B4: 88
sheet2!C4: =personal.xls!UseSameAs(sheet1!B4) [displays 440]

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
Click to expand...
 
D

David McRitchie

Hi Dave and Stephen,
I'll go along with the Volatile it was getting too weird even for me
since the formulas were on another page. Then I also made another
change back to what I had originally tested with, as it otherwise gets a
circular error if the reference is to a constant that looks like a cell address.

Function UseSameAs(cell As Range)
'-- Use the same Formula as used in the referenced cell
'-- http://mvps.org/dmcritchie/excel/formula.htm#usesameas 2005-09-03 .excel
Application.Volatile
If cell.HasFormula Then
UseSameAs = Parent.Application.Evaluate(cell.Formula)
Else '-- needed if constant looks like a cell address
UseSameAs = cell.Value
End If
End Function

sheet1!B4: 77
sheet1!C4: =5*B4 [displays 385]
sheet2!B4: 88
sheet2!C4: =personal.xls!UseSameAs(sheet1!B4) [displays 440]
 
D

Dave Peterson

I still think you need this version:

UseSameAs = Application.Caller.Parent.Evaluate(cell.Formula)

The unqualified Parent reference evaluated to the Microsoft Excel object when I
set a breakpoint and added a watch.

Application.Caller will represent the cell containing the =UseSameAs() formula.

Application.caller.parent will be the worksheet that contains that cell.

Application.evaluate() (same as parent.application.evaluate()) will use the
activesheet for any range that isn't fully qualified in the formula.

But worksheets("x").evaluate() (same as application.caller.evaluate()) will use
that worksheet as the "home" for those unqualified ranges.

I do like the check for the .formula, though.


David said:
Hi Dave and Stephen,
I'll go along with the Volatile it was getting too weird even for me
since the formulas were on another page. Then I also made another
change back to what I had originally tested with, as it otherwise gets a
circular error if the reference is to a constant that looks like a cell address.

Function UseSameAs(cell As Range)
'-- Use the same Formula as used in the referenced cell
'-- http://mvps.org/dmcritchie/excel/formula.htm#usesameas 2005-09-03 .excel
Application.Volatile
If cell.HasFormula Then
UseSameAs = Parent.Application.Evaluate(cell.Formula)
Else '-- needed if constant looks like a cell address
UseSameAs = cell.Value
End If
End Function

sheet1!B4: 77
sheet1!C4: =5*B4 [displays 385]
sheet2!B4: 88
sheet2!C4: =personal.xls!UseSameAs(sheet1!B4) [displays 440]
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
Click to expand...
 
D

David McRitchie

Hi Dave,
Sorry I missed that change, thought it was just the volatile.
Thanks for the correction and explanation don't know how much of
it sunk in but it's another step in the right direction and having
correct code is what counts. Thanks
 
D

Dave Peterson

Glad we got it close to right!

David said:
Hi Dave,
Sorry I missed that change, thought it was just the volatile.
Thanks for the correction and explanation don't know how much of
it sunk in but it's another step in the right direction and having
correct code is what counts. Thanks
Click to expand...
 
D

David McRitchie

No, I think it's perfect. I just meant I'm getting closer to
understanding it -- though I don't know it yet -- another step
along the way.
 
Top