Hello,

I searched this group for an answer, but I couldn't find it.
I would like to use the name of a named formula in the INDIRECT
function,
so I can change the used formula by changing the referenced cell.
For example in Range("A1") I will write the name of the named formula
like "ShiftPattern_1", "ShiftPattern_2" or "ShiftPattern_3".
In the cells, where the formulas are used I'd like to use something
like
"INDIRECT("A1") in order to use the formula which I have put in
Range("A1").
The formula behind the name "ShiftPattern_1" looks like this:
"=IF(AND(ISTEXT(ProdCal.Data!O2),ISERROR(SEARCH("B ridge*",ProdCal.Data!
O2,1))),0,IF(WEEKDAY(ProdCal.Data!L2,2)=PM_Day_7,S hiftsOnPMDay,3))
Some standard formulas and some named ranges like "PM_Day_7" or
"ShiftsOnPMDay".

I hope I've described my problem good enough to get an answer whether
it's possible or not.
Thanks.

Excel XP SP3
WIN XP SP3

"ShiftPattern_1" looks like this:
"=IF(AND(ISTEXT(ProdCal.Data!O2),ISERROR(SEARCH("B ridge*",ProdCal.Data!
O2,1))),0,IF(WEEKDAY(ProdCal.Data!L2,2)=PM_Day_7,S hiftsOnPMDay,3))

ShiftPattern_1 has to resolve to a *text representation of a valid
reference*. The above formula doesn't meet that requirement.


--
Biff
Microsoft Excel MVP


"Werner Rohrmoser" wrote in message
...
Hello,

I searched this group for an answer, but I couldn't find it.
I would like to use the name of a named formula in the INDIRECT
function,
so I can change the used formula by changing the referenced cell.
For example in Range("A1") I will write the name of the named formula
like "ShiftPattern_1", "ShiftPattern_2" or "ShiftPattern_3".
In the cells, where the formulas are used I'd like to use something
like
"INDIRECT("A1") in order to use the formula which I have put in
Range("A1").
The formula behind the name "ShiftPattern_1" looks like this:
"=IF(AND(ISTEXT(ProdCal.Data!O2),ISERROR(SEARCH("B ridge*",ProdCal.Data!
O2,1))),0,IF(WEEKDAY(ProdCal.Data!L2,2)=PM_Day_7,S hiftsOnPMDay,3))
Some standard formulas and some named ranges like "PM_Day_7" or
"ShiftsOnPMDay".

I hope I've described my problem good enough to get an answer whether
it's possible or not.
Thanks.

Excel XP SP3
WIN XP SP3

hi, Werner !

instead of using indirect(... function, you could use another defined name using xl4 macro-function evaluate(...)

1) your named-formulae (i.e. ShiftPattern_1) shall return a "formula-text" (i.e. define "the name" using)
="if(and(istext(prodcal.data!o2),iserror(search("" bridge*"",prodcal.data!o2,1))),0,if(weekday(prodca l.data!l2,2)=pm_day_7,shiftsonpmday,3))"

2) "call" the name (i.e.) in cell [A1] =shiftpattern_1
you could format [A1] as blank text or any other (conditional)format as desired/needed

3) create another formula-name (i.e. myFormula) using: =evaluate(!$a$1)+0*now()

4) "call" this last name where you need the resulting evaluation: =myformula

you can "call" another pattern-name in cell [A1] and see the changes where you used =myformula

note: be sure you use this procedure in xl/2002 or above (just in case copying to other worksheets/workbooks)

hth,
hector.

__ OP __
I searched this group for an answer, but I couldn't find it.
I would like to use the name of a named formula in the INDIRECT function,
so I can change the used formula by changing the referenced cell.
For example in Range("A1") I will write the name of the named formula
like "ShiftPattern_1", "ShiftPattern_2" or "ShiftPattern_3".
In the cells, where the formulas are used I'd like to use something like
"INDIRECT("A1") in order to use the formula which I have put in Range("A1").
The formula behind the name "ShiftPattern_1" looks like this:
"=IF(AND(ISTEXT(ProdCal.Data!O2),ISERROR(SEARCH("B ridge*",ProdCal.Data!
O2,1))),0,IF(WEEKDAY(ProdCal.Data!L2,2)=PM_Day_7,S hiftsOnPMDay,3))
Some standard formulas and some named ranges like "PM_Day_7" or "ShiftsOnPMDay".
I hope I've described my problem good enough to get an answer whether it's possible or not.

Thanks, now I don't need to try any more, and that's good to know,
because it's time saving.

Regards
Werner

Hi Miguel,

thank you for this approach, I'm going to try it.
Some of these "xl4 macro-function" are very useful, would be good to
have them in Excel standard.

Regards
Werner