+ Reply to Thread
Results 1 to 11 of 11

Populate Dynamic Range based on Criteria in Master Table

  1. #1
    Forum Contributor
    Join Date
    03-30-2016
    Location
    Gillette, WY
    MS-Off Ver
    2016
    Posts
    220

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

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,891

    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. #3
    Forum Contributor
    Join Date
    03-30-2016
    Location
    Gillette, WY
    MS-Off Ver
    2016
    Posts
    220

    Re: Populate Dynamic Range based on Criteria in Master Table

    Did not return any values.

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Populate Dynamic Range based on Criteria in Master Table

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

  5. #5
    Forum Contributor
    Join Date
    03-30-2016
    Location
    Gillette, WY
    MS-Off Ver
    2016
    Posts
    220

    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"
    Last edited by TFiske; 10-20-2017 at 02:08 PM.

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Populate Dynamic Range based on Criteria in Master Table

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

  7. #7
    Forum Contributor
    Join Date
    03-30-2016
    Location
    Gillette, WY
    MS-Off Ver
    2016
    Posts
    220

    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?
    Last edited by TFiske; 10-20-2017 at 02:29 PM.

  8. #8
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,891

    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. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,108

    Re: Populate Dynamic Range based on Criteria in Master Table

    Thread moved to the VBA section.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  10. #10
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,163

    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.
    Last edited by jeffreybrown; 10-20-2017 at 05:57 PM.
    HTH
    Regards, Jeff

  11. #11
    Forum Contributor
    Join Date
    03-30-2016
    Location
    Gillette, WY
    MS-Off Ver
    2016
    Posts
    220

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Self populate tables based on criteria of data in 'master' table
    By white_ross in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-12-2016, 11:07 AM
  2. Dynamic Dependent Dropdown Lists that populate based on matching rows within table
    By macdonaldtomw in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-14-2015, 05:44 AM
  3. [SOLVED] populate user form combobox based on dynamic range
    By mcdermott2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-22-2015, 09:22 PM
  4. [SOLVED] Dynamic Named Range to Auto-Populate another range based on criteria
    By BeachRock in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-24-2014, 06:46 PM
  5. Replies: 9
    Last Post: 02-26-2014, 04:17 PM
  6. Populate master workbook with data from other workbooks based on criteria lookup
    By nabilbil in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-26-2013, 02:47 PM
  7. Replies: 2
    Last Post: 02-10-2012, 02:00 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1