+ Reply to Thread
Results 1 to 7 of 7

formula to create a dynamic list of names from a range, based on corresponding range

  1. #1
    Registered User
    Join Date
    12-11-2011
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel Pro Plus 2019
    Posts
    67

    formula to create a dynamic list of names from a range, based on corresponding range

    Actual worksheet has 12 Data sheets and 1 Results sheet, in this example I have used only one Data sheet & Results, I show what are the results that I expect and few of the formulas that I have tried, ALL failed

    Each Data sheet has two corresponding ranges, F3:AN16 and BF3:CN16, column F corresponds to BF, column G corresponds to BG and ends with column AN corresponding to CN.

    Using excel 2019, formula to create a dynamic list of names from F3:AN16, based on corresponding cells in BF3:CN16 which have formulas that have NO VALUE, ie, it is blank.

    Example:
    O3 & BO3 are corresponding cells and BO3 is blank (Each corresponding cell is on the same row)
    P6 & BP6 are corresponding cells and BP6 is blank
    so both would be included in the new list in Results!D5:D20

    I don't mind creating 12 different lists and then manually combining them, if we cannot have all lists from all 12 Data sheets cannot be created in one list.

    Any help is very much appreciated.
    RJK
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: formula to create a dynamic list of names from a range, based on corresponding range

    Try:

    =IFERROR(INDIRECT("Data!"&TEXT(AGGREGATE(15,6,(ROW(Data!$F$3:$AN$16)*1000+COLUMN(Data!$F$3:$AN$16))/((Data!$F$3:$AN$16<>"")*(Data!$BF$3:$CN$16="")),ROWS(D$5:D5)),"R0C000"),FALSE),"")

    copied down. Sorting is more difficult, becasue of inconsistencies in formatting (Ms, for example, being neither forename nor surname... but just there).
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  3. #3
    Registered User
    Join Date
    12-11-2011
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel Pro Plus 2019
    Posts
    67

    Re: formula to create a dynamic list of names from a range, based on corresponding range

    Thank you, worked as I needed except I tried to make the sheet name (Data1, Data2, Data3) dynamically included in the formula, of course I stumbled.

    I attached a new worksheet to show you what I was trying to do.

    As to the sort, just for learning, what if there was no prefixes, how would I go about sorting the list with a formula.

    Again, thank you for your quick prompt, have a wonderful day! (always)

    BTW, tried to add to your reputation, the system would not allow me, "You must spread some reputation around before giving it to Glenn Kennedy", the problem is, no one else has ever helped me out
    Attached Files Attached Files
    Last edited by RJK; 10-15-2023 at 02:33 PM.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2504
    Posts
    19,244

    Re: formula to create a dynamic list of names from a range, based on corresponding range

    Try the following:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    12-11-2011
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel Pro Plus 2019
    Posts
    67

    [SOLVED] Re: formula to create a dynamic list of names from a range, based on correspondin

    Sorry for the delayed reply, I just got a chance now to see the solution, thank you very much, now I see what I did wrong, I was adding an extra "data1" inside the aggregate formula.
    Thank you very much for all your help

  6. #6
    Registered User
    Join Date
    12-11-2011
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel Pro Plus 2019
    Posts
    67

    Re: formula to create a dynamic list of names from a range, based on corresponding range

    BTW got this error after "submit reply"
    Fatal error: Declaration of vB_DataManager_ThreadRate_Multiple::fetch_query($condition, $limit = 0, $offset = 0) must be compatible with vB_DataManager_Multiple::fetch_query($condition = '', $limit = 0, $offset = 0) in /home/eforum/public_html/includes/class_dm_threadrate.php on line 290
    Last edited by RJK; 10-22-2023 at 01:21 PM.

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2504
    Posts
    19,244

    Re: formula to create a dynamic list of names from a range, based on corresponding range

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ 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] Create a dynamic named range or formula to change the range in the forecast formula
    By christhomas99 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-18-2022, 08:02 PM
  2. Create drop down list using range names
    By Tresfjording in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-17-2016, 04:36 PM
  3. Create a list that sums values of selected names in a dynamic range
    By PAexcel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-18-2014, 11:23 AM
  4. [SOLVED] chart based on dynamic range which resizes as per data in range-formula / vba
    By KK1234 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-11-2014, 04:02 PM
  5. Dynamic range names based on pivot table fields?
    By Gandalf21 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-23-2013, 07:47 PM
  6. Create drop list on form from range names?
    By Ed in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-15-2005, 12:06 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