M
MikeF
Thought I 'd re-post the following question. Hopefully this is not extremely
bad etiquette within this forum.
The idea seems to be one that many could use in numerous variations once
solidified - but the construct is apparently quite challenging [certainly for
me..!!], and am hoping someone in this forum will take a fresh crack at it.
Indeed, Charles' initial idea of simply creating individual range-names and
using Choose() does work, and I constructed that as an interim. Quite
possibly his latter suggestion of a UDF would be the trick, but am
inexperienced in anything more than a rudimentary UDF build at present.
So ... any strategies to accomplish the following would be sincerely
appreciated.
Am looking for a multiple named-range solution that contains formulas in each
cell.
*** All in same Workbook:
This is the range stored in a table on the source worksheet [let's call it
WSR]
that is 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 destination worksheet [let's
call it WSC].
Accordingly, when my lookup becomes "TaxCalc" & "2", another range/row of
cells from the source table on WSR - named of course TaxCalc2 - then drop
into the same L7:N26 on WSC.
And so on with TaxCalc3, TaxCalc4, etc as required.
Keeping in mind that a named array range can contain constants that populate
multiple cells, ie ....
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.
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
bad etiquette within this forum.
The idea seems to be one that many could use in numerous variations once
solidified - but the construct is apparently quite challenging [certainly for
me..!!], and am hoping someone in this forum will take a fresh crack at it.
Indeed, Charles' initial idea of simply creating individual range-names and
using Choose() does work, and I constructed that as an interim. Quite
possibly his latter suggestion of a UDF would be the trick, but am
inexperienced in anything more than a rudimentary UDF build at present.
So ... any strategies to accomplish the following would be sincerely
appreciated.
Am looking for a multiple named-range solution that contains formulas in each
cell.
*** All in same Workbook:
This is the range stored in a table on the source worksheet [let's call it
WSR]
that is 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 destination worksheet [let's
call it WSC].
Accordingly, when my lookup becomes "TaxCalc" & "2", another range/row of
cells from the source table on WSR - named of course TaxCalc2 - then drop
into the same L7:N26 on WSC.
And so on with TaxCalc3, TaxCalc4, etc as required.
Keeping in mind that a named array range can contain constants that populate
multiple cells, ie ....
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.
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