+ Reply to Thread
Results 1 to 3 of 3

Need to return a series of sizes from a list

  1. #1
    Registered User
    Join Date
    06-06-2018
    Location
    Woodstock, ON
    MS-Off Ver
    365
    Posts
    19

    Need to return a series of sizes from a list

    My problem is similar to this issue here

    I've tried getting this to work for my issue but I can only seem to get it to work with a single value returned....I need several options returned.

    I need to take the 3 values (case dimensions) in A4, B4 and D4 and generate a list of 8-10 case sizes from my Case Inventory tab that fit the bill.

    ie: the first number (case length) can to be 317mm +51mm/-2mm (if the -2 mucks up the ability to find numbers then ignore it)
    the second number (case width) can to be 297mm +51mm/-2mm (if the -2 mucks up the ability to find numbers then ignore it)
    the third number (case depth) can to be 175mm +51mm/-0

    I've fought with this all weekend and half the day today and I am at my wits end.
    I've tried so many different version of the answer supplied on the attached link but I'm supplying it as a start point because for this guy's issue we know it worked.
    Mine were too many and too wrong to even bother starting with them.

    My spreadsheet is attached.

    Thanks in advance for any and all help offered.
    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,225

    Re: Need to return a series of sizes from a list

    One way:

    =IFERROR(INDEX('Case Inventory'!$A:$A,AGGREGATE(15,6,ROW('Case Inventory'!$A$2:$A$193)/
    (('Case Inventory'!$D$2:$D$193>=$A$4-2)*('Case Inventory'!$D$2:$D$193<=$A$4+51)*
    ('Case Inventory'!$E$2:$E$193>=$B$4-2)*('Case Inventory'!$E$2:$E$193<=$B$4+51)*
    ('Case Inventory'!$F$2:$F$193>=$D$4)*('Case Inventory'!$F$2:$F$193<=$D$4+51)),ROWS($1:1))),"")

    copied down. See sheet.
    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
    06-06-2018
    Location
    Woodstock, ON
    MS-Off Ver
    365
    Posts
    19

    Re: Need to return a series of sizes from a list

    AGGREGATE!! That's what I was missing! I would not have known how to use it but I have had others help me with formulas with that before so I sort of have an idea how it works.

    Thanks so much! and sorry for taking so long to reply...its been an insane week.

+ 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. Merge 4 Lists ( Various Sizes ) into 1 main list
    By DAVIDNUFC85 in forum Excel General
    Replies: 25
    Last Post: 01-05-2020, 09:37 PM
  2. Larger sizes against smaller sizes
    By nathanocs in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-13-2013, 04:41 PM
  3. How to find the annualized return from a monthly return series
    By tulasiram in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-01-2013, 05:39 AM
  4. Creating a list of files with file sizes
    By CobraLAD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-15-2011, 02:21 AM
  5. List box changing sizes
    By mngolfer in forum Excel General
    Replies: 2
    Last Post: 01-14-2011, 04:06 PM
  6. Replies: 0
    Last Post: 12-09-2008, 08:42 PM
  7. How do I stop Control List boxes from changing sizes
    By Thomas998 in forum Excel General
    Replies: 1
    Last Post: 11-13-2008, 09:23 PM

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