Array formula[S] in named range[S]

[MikeF]

Am looking for a multiple named-range solution that contains formulas in each
cell.

This is the range stored in a table on another worksheet [let's call it WSR]
that would be named TaxCalc1 ...
=-$H7*Tax1PHd =+Tax2Other =-($J7+$L7)*Tax3Mult
What should happen is when my lookup becomes "TaxCalc" & "1", the above
formulas drop from WSR into cells L7:N26 on the relevant worksheet [let's
call it WSC].

Accordingly, when my lookup becomes "TaxCalc" & "2", another set of cells
that are in the same source table on WSR, named of course TaxCalc2, which
have slightly different formulas, then drop into L7:N26 on WSC.

And so on with TaxCalc3, TaxCalc4, etc as required.
There will be at least half a dozen.

Keeping in mind that a named array range can contain constants....
1stQtr = Array("Jan", "Feb", "Mar")
2ndQtr = Array("Apr", "May", "Jun")

.... Am hoping that someone knows how to substitute the constants in the
above Qtr example for formulas.

I have tried and tried various methods/syntaxes to no avail, and am now
stumped enough to type all of this [!!].

Thanx in advance to anyone who can assist.
Regards,
- Mike

 

[Charles Williams]

I think you need to have separate Named Formulae for each TaxCalc,
and then use a CHOOSE() function to select which TaxCalc Name to return
instead of your Lookup formula.

(or get Lookup to return a number which you then feed to the Choose
function).


Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[MikeF]

Have actually done a bit of that, and am admittedly looking for a way to
avoid all those named ranges, as well as [obviously] making things easier on
the front end.
So there's no way to incorporate multiple formulas into a named range?
Thanx.
- Mike

I think you need to have separate Named Formulae for each TaxCalc,
and then use a CHOOSE() function to select which TaxCalc Name to return
instead of your Lookup formula.

(or get Lookup to return a number which you then feed to the Choose
function).


Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

Am looking for a multiple named-range solution that contains formulas in
each
cell.

This is the range stored in a table on another worksheet [let's call it
WSR]
that would be named TaxCalc1 ...
=-$H7*Tax1PHd =+Tax2Other =-($J7+$L7)*Tax3Mult
What should happen is when my lookup becomes "TaxCalc" & "1", the above
formulas drop from WSR into cells L7:N26 on the relevant worksheet [let's
call it WSC].

Accordingly, when my lookup becomes "TaxCalc" & "2", another set of cells
that are in the same source table on WSR, named of course TaxCalc2, which
have slightly different formulas, then drop into L7:N26 on WSC.

And so on with TaxCalc3, TaxCalc4, etc as required.
There will be at least half a dozen.

Keeping in mind that a named array range can contain constants....
1stQtr = Array("Jan", "Feb", "Mar")
2ndQtr = Array("Apr", "May", "Jun")

... Am hoping that someone knows how to substitute the constants in the
above Qtr example for formulas.

I have tried and tried various methods/syntaxes to no avail, and am now
stumped enough to type all of this [!!].

Thanx in advance to anyone who can assist.
Regards,
- Mike

 

[Charles Williams]

Although a named range (Defined Name) is really a named array formula,
trying to make formulas that generate formulas does not strike me as a good
idea, let alone trying to do it inside a Defined Name

If you really want a better solution then you why not write an array
function UDF?
I would have thought that would be a lot simpler and cleaner.

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

Have actually done a bit of that, and am admittedly looking for a way to
avoid all those named ranges, as well as [obviously] making things easier
on
the front end.
So there's no way to incorporate multiple formulas into a named range?
Thanx.
- Mike

I think you need to have separate Named Formulae for each TaxCalc,
and then use a CHOOSE() function to select which TaxCalc Name to return
instead of your Lookup formula.

(or get Lookup to return a number which you then feed to the Choose
function).


Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

Am looking for a multiple named-range solution that contains formulas
in
each
cell.

This is the range stored in a table on another worksheet [let's call it
WSR]
that would be named TaxCalc1 ...
=-$H7*Tax1PHd =+Tax2Other =-($J7+$L7)*Tax3Mult
What should happen is when my lookup becomes "TaxCalc" & "1", the above
formulas drop from WSR into cells L7:N26 on the relevant worksheet
[let's
call it WSC].

Accordingly, when my lookup becomes "TaxCalc" & "2", another set of
cells
that are in the same source table on WSR, named of course TaxCalc2,
which
have slightly different formulas, then drop into L7:N26 on WSC.

And so on with TaxCalc3, TaxCalc4, etc as required.
There will be at least half a dozen.

Keeping in mind that a named array range can contain constants....
1stQtr = Array("Jan", "Feb", "Mar")
2ndQtr = Array("Apr", "May", "Jun")

... Am hoping that someone knows how to substitute the constants in the
above Qtr example for formulas.

I have tried and tried various methods/syntaxes to no avail, and am now
stumped enough to type all of this [!!].

Thanx in advance to anyone who can assist.
Regards,
- Mike

 

[MikeF]

I don't know how to do that.
Any assistance would be sincerely appreciated.
Thanx,
- Mike

Although a named range (Defined Name) is really a named array formula,
trying to make formulas that generate formulas does not strike me as a good
idea, let alone trying to do it inside a Defined Name

If you really want a better solution then you why not write an array
function UDF?
I would have thought that would be a lot simpler and cleaner.

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

Have actually done a bit of that, and am admittedly looking for a way to
avoid all those named ranges, as well as [obviously] making things easier
on
the front end.
So there's no way to incorporate multiple formulas into a named range?
Thanx.
- Mike

I think you need to have separate Named Formulae for each TaxCalc,
and then use a CHOOSE() function to select which TaxCalc Name to return
instead of your Lookup formula.

(or get Lookup to return a number which you then feed to the Choose
function).


Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

Am looking for a multiple named-range solution that contains formulas
in
each
cell.

This is the range stored in a table on another worksheet [let's call it
WSR]
that would be named TaxCalc1 ...
=-$H7*Tax1PHd =+Tax2Other =-($J7+$L7)*Tax3Mult
What should happen is when my lookup becomes "TaxCalc" & "1", the above
formulas drop from WSR into cells L7:N26 on the relevant worksheet
[let's
call it WSC].

Accordingly, when my lookup becomes "TaxCalc" & "2", another set of
cells
that are in the same source table on WSR, named of course TaxCalc2,
which
have slightly different formulas, then drop into L7:N26 on WSC.

And so on with TaxCalc3, TaxCalc4, etc as required.
There will be at least half a dozen.

Keeping in mind that a named array range can contain constants....
1stQtr = Array("Jan", "Feb", "Mar")
2ndQtr = Array("Apr", "May", "Jun")

... Am hoping that someone knows how to substitute the constants in the
above Qtr example for formulas.

I have tried and tried various methods/syntaxes to no avail, and am now
stumped enough to type all of this [!!].

Thanx in advance to anyone who can assist.
Regards,
- Mike