Dynamic Formula Array

[barrymac20]

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.....

[Pete_UK]

You will probably need to use the INDIRECT function in your formula, as this turns a range which is a text value into a range reference that Excel can make use of.

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as this does not work on this forum.

Hope this helps.

Pete

[barrymac20]

Pete, No need. the INDIRECT function has done the trick.
I had thought about it but it wouldn't work for me. Following your suggestion I tried again and it works perfect. Appreciate your help on this!!
Barry

[Pete_UK]

Well, glad to help, Barry.

If that takes care of your query, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark this thread as SOLVED.

Pete

[barrymac20]

Well, glad to help, Barry.

If that takes care of your query, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark this thread as SOLVED.

Pete

Will do. Cheers