# Populate Dynamic Range based on Criteria in Master Table

1. ## Populate Dynamic Range based on Criteria in Master Table

Hi all,

I don't know if this is possible, but I would like to auto populate a dynamic ranged based on a given criteria in another table. I have the dynamic range built and it is working, but I don't know how to auto populate it. I know that I could use a single cell array formula and copy it down but I would like to avoid that approach.

I have a worksheet that serves as a master list off all equipment. I need a formula that will populate a named range, called UnitProjectionRange, with all Unit #'s that are set to Calculate for Depreciation with a "YES" value in the master list. Please see attached example. Thanks.

2. ## Re: Populate Dynamic Range based on Criteria in Master Table

In C2, try this:

=IFERROR(INDEX(EquipmentMasterList[Unit '#],SMALL(IF(EquipmentMasterList[Calculate for Depreciation (YES/NO)]="YES",ROW(EquipmentMasterList[Unit '#])-(ROW(A\$2)-1)),ROWS(A\$1:A1))),"") Ctrl Shift Enter

3. ## Re: Populate Dynamic Range based on Criteria in Master Table

Did not return any values.

4. ## Re: Populate Dynamic Range based on Criteria in Master Table

That's interesting... see the attached workbook with the formula in place.

5. ## Re: Populate Dynamic Range based on Criteria in Master Table

Don't know what happened there, must have missed something. But, I want to automatically populate the dynamic range called "UnitProjectionRange" with these values. I know that I could drag this formula down to an arbitrary number of rows, but I'd rather see if there is a way to have the dynamic range do that for me. Does that make sense?

IE: I want the array formula that returns the values to live in the dynamic range "UnitProjectionRange"

6. ## Re: Populate Dynamic Range based on Criteria in Master Table

You can't automatically add or remove formulas without VBA.

7. ## Re: Populate Dynamic Range based on Criteria in Master Table

I am open to a VBA solution. I have not used VBA very much at all, as in I know how to get to the VBA screen, but I need to learn. What would the VBA solution be?

8. ## Re: Populate Dynamic Range based on Criteria in Master Table

Since this was posted in the "Excel Formulas & Functions" forum, I provided a formulaic approach.

VBA is not my specialty so I am unable to help there. I have brought this to the attention of others who may be able to assist you.

9. ## Re: Populate Dynamic Range based on Criteria in Master Table

Thread moved to the VBA section.

10. ## Re: Populate Dynamic Range based on Criteria in Master Table

Why not just simply use a pivot table!

Unit # >> Row Labels
Depreciation >> Report Filter

Filter depreciation to = Yes

--------------------------------------------------
If you need VBA, this works with your test sheet

``Please Login or Register  to view this content.``

11. ## Re: Populate Dynamic Range based on Criteria in Master Table

The results will be used to perform further calculations in the resulting table and I haven't worked with Pivot tables much. The VBA solution you provided is great! Thank you. Sorry for the late reply, I had to move on to some other projects. Thanks again.

There are currently 1 users browsing this thread. (0 members and 1 guests)