Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formulas in named ranges
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named ranges and pasting formulas with named references | Excel Programming | |||
Named Ranges in Array Formulas | Excel Worksheet Functions | |||
Can you create dynamic named ranges containing array formulas? | Excel Programming | |||
Looking up named ranges as an array | Excel Programming | |||
Array and Named Ranges | Excel Programming |