Indirectly referencing a formula on a different sheet

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

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.

---
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

"Andy" <[email protected]> wrote in message news:[email protected]...
> 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
>

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.

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
---
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

"Andy" <[email protected]> wrote in message news:[email protected]...
> 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.
>

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.

Hi Andy,
To invoke the same formula used on another sheet.

Function UseSameAs(cell)
'-- http://mvps.org/dmcritchie/formula.htm#usesameas 2005-09-03 .excel
UseSameAs = Application.Evaluate(cell.Formula)
End Function</font>

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

To install the above user defined function (UDF) see
http://www.mvps.org/dmcritchie/excel/getstarted.htm
---
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

"Andy" <andy.wagg,,@,,gb.schneider-electric.com> wrote...
> Thats not what I am trying to achieve. 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.
>