Name of named formula used in an INDIRECT function

[Werner Rohrmoser]

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("Bridge*",ProdCal.Data!
O2,1))),0,IF(WEEKDAY(ProdCal.Data!L2,2)=PM_Day_7,ShiftsOnPMDay,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

 

[T. Valko]

"ShiftPattern_1" looks like this:

"=IF(AND(ISTEXT(ProdCal.Data!O2),ISERROR(SEARCH("Bridge*",ProdCal.Data!
O2,1))),0,IF(WEEKDAY(ProdCal.Data!L2,2)=PM_Day_7,ShiftsOnPMDay,3))

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

 

[Héctor Miguel]

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(prodcal.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 __

 

[Werner Rohrmoser]

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

Regards
Werner

 

[Werner Rohrmoser]

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