+ Reply to Thread
Results 1 to 6 of 6

Return corresponding value (from a range of cells) if criteria in 3 columns are met

  1. #1
    Registered User
    Join Date
    02-25-2014
    Location
    Loganville, GA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Return corresponding value (from a range of cells) if criteria in 3 columns are met

    I am working in a workbook with numerous worksheets. The first worksheet is a summary of all widths, lengths, and control locations used for a project. I am using it as a key for the individual floors of a project.
    Column A is list of alpha characters. Each character represents a specific width, length, and control location. The additional worksheets are floor specific. For instance, Floor 1 is a worksheet, Floor 2 is a worksheet, and so on. These worksheets show the actual measures for each floor. I need to assign the "master" alpha character when all three conditions are met for a specific letter. The purpose is for me to know how many type As, Bs, Cs, etc. that I need to order for each floor, and for the complete project. Please help!

    Thanks,
    Cliff

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,447

    Re: Return corresponding value (from a range of cells) if criteria in 3 columns are met

    Hi and welcome to the forum

    To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    02-25-2014
    Location
    Loganville, GA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Return corresponding value (from a range of cells) if criteria in 3 columns are met

    Thanks, I hope this works.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    02-25-2014
    Location
    Loganville, GA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Return corresponding value (from a range of cells) if criteria in 3 columns are met

    Maybe this time.
    Attached Files Attached Files

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,447

    Re: Return corresponding value (from a range of cells) if criteria in 3 columns are met

    The file came though fine thanks

    However, it might help if you provided a few sample answers (or maybe you did and I dont understand what you want)

    It looks like you will have multiple matches for a given set of criteria though? I have been trying to match teh values in Tag sheet with 4 sheet...
    row 18 TAG...
    S...92 1/2...111...R

    4 sheet...
    A
    B
    C
    D
    E
    F
    G
    663
    TAG
    LOCATION
    QTY
    WIDTH
    LENGTH
    CONTROL
    BRACKET TYPE
    664
    S
    403 A
    1
    92 1/2
    111
    R
    665
    S
    407 B
    1
    92 1/2
    111
    R
    666
    S
    413 B
    1
    92 1/2
    111
    R
    667
    S
    502 D
    1
    92 1/2
    111
    R
    668
    S
    603 A
    1
    92 1/2
    111
    R
    669
    S
    707 B
    1
    92 1/2
    111
    R
    670
    S
    807 B
    1
    92 1/2
    111
    R
    671
    S
    903 A
    1
    92 1/2
    111
    R
    672
    S
    907 B
    1
    92 1/2
    111
    R
    673
    S
    913 B
    1
    92 1/2
    111
    R
    674
    S
    1002 D
    1
    92 1/2
    111
    R
    675
    S
    1103 A
    1
    92 1/2
    111
    R
    676
    S
    1207 B
    1
    92 1/2
    111
    R
    677
    S
    1307 B
    1
    92 1/2
    111
    R
    678
    S
    1403 A
    1
    92 1/2
    111
    R
    679
    S
    1407 B
    1
    92 1/2
    111
    R
    680
    S
    1413 B
    1
    92 1/2
    111
    R
    681
    S
    1502 D
    1
    92 1/2
    111
    R
    682
    S
    1603 A
    1
    92 1/2
    111
    R
    683
    S
    1707 B
    1
    92 1/2
    111
    R
    684
    S
    1807 B
    1
    92 1/2
    111
    R


    so what would your answer be?

  6. #6
    Registered User
    Join Date
    02-25-2014
    Location
    Loganville, GA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Return corresponding value (from a range of cells) if criteria in 3 columns are met

    You are correct; there will be multiple matches. That's part of the goal. Instead of going line by line to order each size, I am wanting to know how many "S"s I will need to order. An S means that I will order a 92 1/2 x 111, and in this case, a quantity of 21 as shown above. Once I can get the "TAG"s to auto-populate (based off the criteria), I can then summarize. It would look something like this:

    TAG LOCATION QTY WIDTH LENGTH CONTROL
    A XXX 15 90 120 R
    B XXX 4 90 120 L
    C XXX 7 85 108 R
    D XXX 10 60 72 L

  7. #7
    Registered User
    Join Date
    02-25-2014
    Location
    Loganville, GA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Return corresponding value (from a range of cells) if criteria in 3 columns are met

    Did my last post help?

  8. #8
    Registered User
    Join Date
    02-25-2014
    Location
    Loganville, GA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Return corresponding value (from a range of cells) if criteria in 3 columns are met

    FDibbins, I've not heard anything back. Do you have the "magical" formula for me?

+ 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] Looking for a formula to reference a range of cells and return data if meets criteria
    By jeremyhawg in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-24-2014, 07:19 PM
  2. Replies: 1
    Last Post: 09-03-2013, 07:41 PM
  3. Replies: 10
    Last Post: 02-19-2013, 12:05 PM
  4. [SOLVED] Return Max value of two columns IF date criteria is met
    By Medir in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-06-2013, 07:45 AM
  5. Return a range of cells that meets a criteria
    By bgallagher1 in forum Excel General
    Replies: 3
    Last Post: 08-14-2010, 04:33 AM

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