Hi Guys.

I want to populate a range of cells using a formula array and a named range.

I have a cell(A) that changes depending on other selections.

The cell(A) value will always equal the name of a separate named range. For example, cell(A) value is "AU_ARE_22_18_AL3_AL3_R_13_3__ANSI_61_". This corresponds with a range(B) named "AU_ARE_22_18_AL3_AL3_R_13_3__ANSI_61_".
This range is made up of two columns, (1)part number and (2)quantity and can have up to 20 rows.

I want to use the formula array function to populate another set of cells (two columns also ^20 rows) based on the named range(B) derived from cell(A) e.g. "AU_ARE_22_18_AL3_AL3_R_13_3__ANSI_61_"

This is a bill of materials so i need it to change based on the cell(A) value.

Is there a way for the formula array to update automatically when cell(A) value changes to correspond with a different named range?

I cant figure it out in excel and I'm getting stuck with VBA also. I can use the .FormulaArray function but only with the value hard coded, e.g. Range.FormulaArray = "=AU_ARE_22_18_AL3_AL3_R_13_3__ANSI_61_". But I want the value to change based on the cell(A) value.

I hope this makes sense.....