+ Reply to Thread
Results 1 to 24 of 24

How to filter a cell, dependant on 3 other cells.

  1. #1
    Registered User
    Join Date
    03-26-2022
    Location
    Queensland, Australia.
    MS-Off Ver
    365
    Posts
    19

    How to filter a cell, dependant on 3 other cells.

    Hi, I am new here, I am reasonably proficient with excel, also done some C# programming a while back. But I am stumped on this. This is my most ambitious excel project to date. My workbook should be attached. Here is a description of what I have done so far and what the end goal is. I have created a workbook which has 9 tabs, the 6 green ones relate to each different products data set. There is a "Lists" sheet, for my named ranges. The blue tab is home to my main table, this table contains all the product data in it's entirety. At first I thought there is probably a way to access this using formula's than using the lists, but I was not able to do it. The red tab has the start of the quote form for the products. This is the where the lookup formula's would be instigated from. You will note that I have set this table up the same as "Dr List". The results are all to come from drop down lists, which are mostly dependent on others.

    So, I can filter the "Dr List" table from left to right to arrive at the data required on the quote form, just don't know how to get there using formulae.
    What I have done on the quote form is derived the answers from the "Lists", but then got stuck when it came to the "H" cell. Because this cell is dependent on what is contained in all 3 of "Model", "Type", and "D". In that order, I just don't know how to do it?

    Once I have that sorted, I should be able to work out the "KG" cell formula.

    Price has to come from the individual product tables to the "Dr List" table, then onto the quote form. At least that is how I see it ATM.

    Any insight would be much appreciated. I am really struggling.

    Cheers,
    Kenny_K
    Attached Files Attached Files
    Last edited by Kenny_K; 03-27-2022 at 01:49 AM. Reason: Forgot file

  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/365
    Posts
    52,004

    Re: How to filter a cell, dependant on 3 other cells.

    Your file did not come through
    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
    03-26-2022
    Location
    Queensland, Australia.
    MS-Off Ver
    365
    Posts
    19

    Re: How to filter a cell, dependant on 3 other cells.

    Should be there now.

  4. #4
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016
    Posts
    1,016

    Re: How to filter a cell, dependant on 3 other cells.

    I'm not sure did my solution is the right way.

    1. I need to sort all field of your 'Dr List'
    2. Create formula to get avilable list
    ** I didn't use unique function due to my excel version. **
    - IDX criteria : condition for create index number
    - IDX prefix : prefix leading of each group
    - IDX no : prefix & running number
    - List : element to show
    - IDX Merge : Group all IDX no to connected cells.
    - List Merge : List for dropdown
    3. in th CSS Quote , I've change data validation
    B2
    =OFFSET('Dr List'!$O$6,MATCH(B$1&"*",'Dr List'!$N$6:$N$414,0)-1,0,COUNTIF('Dr List'!$N$6:$N$414,B$1&"*"))

    C2
    =OFFSET('Dr List'!$V$6,MATCH($B2&"*",'Dr List'!$U$6:$U$414,0)-1,0,COUNTIF('Dr List'!$U$6:$U$414,$B2&"*"))

    D2
    =OFFSET('Dr List'!$AC$6,MATCH($B2&"_"&$C2&"*",'Dr List'!$AB$6:$AB$414,0)-1,0,COUNTIF('Dr List'!$AB$6:$AB$414,$B2&"_"&$C2&"*"))

    E2
    =OFFSET('Dr List'!$AJ$6,MATCH($B2&"_"&$C2&"_"&$D2&"*",'Dr List'!$AI$6:$AI$414,0)-1,0,COUNTIF('Dr List'!$AI$6:$AI$414,$B2&"_"&$C2&"_"&$D2&"*"))

    G2
    =OFFSET('Dr List'!$AQ$6,MATCH($B2&"_"&$C2&"_"&$D2&"_"&$E2&"*",'Dr List'!$AP$6:$AP$414,0)-1,0,COUNTIF('Dr List'!$AP$6:$AP$414,$B2&"_"&$C2&"_"&$D2&"_"&$E2&"*"))

    H2
    =OFFSET('Dr List'!$AX$6,MATCH($B2&"_"&$C2&"_"&$D2&"_"&$E2&"_"&$G2&"*",'Dr List'!$AW$6:$AW$414,0)-1,0,COUNTIF('Dr List'!$AW$6:$AW$414,$B2&"_"&$C2&"_"&$D2&"_"&$E2&"_"&$G2&"*"))

    Note : not change formula in F2 due to no information about width in Dr List.

    Regards.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-26-2022
    Location
    Queensland, Australia.
    MS-Off Ver
    365
    Posts
    19

    Re: How to filter a cell, dependant on 3 other cells.

    WOW!! Menem, that's excellent. No wonder I couldn't do it, all that IDX stuff I have not seen before.
    Thank you so much.
    Next thing is to get the prices to populate. It would be awesome if you would help me with that now.
    The prices are found in the various tables in the green tabs. Typically the orange, green, black, and grey formatted tables.
    I don't know how but the "Dr List" table should perhaps have a formula in the price cells to lookup these prices?
    Then from there they need to find their way onto the Quote form. This may be the most complex part?
    What are your thoughts?
    Cheers,
    Ken.

  6. #6
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016
    Posts
    1,016

    Re: How to filter a cell, dependant on 3 other cells.

    Please provide me how to get the price.
    Just only know that green sheets are refered to model.
    BTW, these are maybe the problems.

    -height , it's seem not in the same way with Dr List
    -table structure seem not in symmetric
    -green sheet not refer direct to model (for sample MET should be Metabox)

    Regards.

  7. #7
    Registered User
    Join Date
    03-26-2022
    Location
    Queensland, Australia.
    MS-Off Ver
    365
    Posts
    19

    Re: How to filter a cell, dependant on 3 other cells.

    Hi Menem, I have attached something to help you understand, hope this does the trick.
    If you need anything else just ask.
    Cheers,
    Ken.
    Attached Images Attached Images
    Last edited by Kenny_K; 03-28-2022 at 02:52 AM. Reason: File didn't upload

  8. #8
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016
    Posts
    1,016

    Re: How to filter a cell, dependant on 3 other cells.

    I need you to do somthing.

    -rename green sheet in Model name
    -every table must start at column L (or anyothers but must the same to all sheets)
    -provide every table into symmetric as 20 rows x 12 columns (it just for sample , I think it might be enough)
    -Height-Weight must be at the top left of table
    -depth must be number

    I've adjust ANT to Antaro
    and modify table of M-30 and M-30-IN
    for your sample.

    Then you can use this formula in I2
    =IFERROR(INDEX(
    OFFSET(INDIRECT("'"&$C2&"'!"&ADDRESS(MATCH($G2&"-"&$H2&IF($D2="Inner","-IN",""),INDIRECT("'"&$C2&"'!L:L"),0),CODE("L")-CODE("A")+1)),0,0,20,12),
    MATCH($F2/1000,OFFSET(INDIRECT("'"&$C2&"'!"&ADDRESS(MATCH($G2&"-"&$H2&IF($D2="Inner","-IN",""),INDIRECT("'"&$C2&"'!L:L"),0),CODE("L")-CODE("A")+1)),0,0,20,1),0),
    MATCH($E2/1000,OFFSET(INDIRECT("'"&$C2&"'!"&ADDRESS(MATCH($G2&"-"&$H2&IF($D2="Inner","-IN",""),INDIRECT("'"&$C2&"'!L:L"),0),CODE("L")-CODE("A")+1)),0,0,1,12),0)),0)



    Regards.

    Note , you may change L:L to L$1:L$-the last row number of sheet as you need , for increase speed/performance of the worksheet.
    Attached Files Attached Files
    Last edited by menem; 03-28-2022 at 05:32 AM.

  9. #9
    Registered User
    Join Date
    03-26-2022
    Location
    Queensland, Australia.
    MS-Off Ver
    365
    Posts
    19

    Re: How to filter a cell, dependant on 3 other cells.

    Thanks very much Menem, I will get to work on that.
    You have been so very helpful I really appreciate it!

  10. #10
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    864

    Re: How to filter a cell, dependant on 3 other cells.

    You can use dynamic arrays with FILTER and UNIQUE nicely here. e.g.

    =UNIQUE(FILTER(DrList[Depth],(DrList[Brands]='CSS Quote'!B2)*(DrList[Models]='CSS Quote'!C2)*(DrList[Type]='CSS Quote'!D2)))

    The list in Data validation then refers to the dynamic cell, e.g. ='Dr List'!N2#

    See attached for example, I'm sure you can figure out the KG and Price. I've put the lookups on the DR List sheet, but they can be moved if you prefer.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    03-26-2022
    Location
    Queensland, Australia.
    MS-Off Ver
    365
    Posts
    19

    Re: How to filter a cell, dependant on 3 other cells.

    Awesome Nick, I will check that out.

  12. #12
    Registered User
    Join Date
    03-26-2022
    Location
    Queensland, Australia.
    MS-Off Ver
    365
    Posts
    19

    Re: How to filter a cell, dependant on 3 other cells.

    Nick that is what I was originally trying to do, but where I kept falling over was I couldn't get the validation box to accept the code. I must not have been typing it in correctly I'm thinking now. At the time I thought that I must not be able to put that sort of code in there. So thanks again Bud.

  13. #13
    Registered User
    Join Date
    03-26-2022
    Location
    Queensland, Australia.
    MS-Off Ver
    365
    Posts
    19

    Re: How to filter a cell, dependant on 3 other cells.

    Ok, I have made a lot of progress. Done some reformatting and thanks to Nick got all the looking up working. The thing that has me stumped now is getting the price into the quote sheet. I have a formula in there which is throwing an error, although I have another spreadsheet which I used the same basic formula in which works a treat. I have also added a column to "Dr List" table with the name of the price table for each drawer, I thought this would simplify the price lookup.
    Now if someone could have a look at the index match formula I have in there that isn't working, and correct it for me, or tell me what I have done wrong and what the solution is I would be much appreciative.
    Also most of the price grids are "Named Ranges" I converted 2 of them to tables thinking this is necessary for my formula to work. So keep that in mind. The 2 that are tables are in the "Antaro" tab.
    Thankyou.
    Attached Files Attached Files

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,334

    Re: How to filter a cell, dependant on 3 other cells.

    Try

    =INDEX(M_30,MATCH(F2,M_30[Width],0),MATCH(TEXT(E2,"000"),M_30[#Headers],0))

    Headers in TABLES are always TEXT so you need to "convert" E2 to text for the match
    Attached Files Attached Files
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  15. #15
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016
    Posts
    1,016

    Re: How to filter a cell, dependant on 3 other cells.

    Kenny,

    I think your formula in the last file (DrawerCostingN.xlsx) in the 'Dr List' at range K1:R8 just only relate to
    'CSS Quote'!B2. So, you might got some problems if Brand in the other rows are different from B2.

    But it's will be ok, if all of them are the same.

    Regards.

  16. #16
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    864

    Re: How to filter a cell, dependant on 3 other cells.

    Or use E2&"" to force it to text if they won't always be 3 characters long.

  17. #17
    Registered User
    Join Date
    03-26-2022
    Location
    Queensland, Australia.
    MS-Off Ver
    365
    Posts
    19

    Re: How to filter a cell, dependant on 3 other cells.

    John I tried your formula, it worked until I substituted the table names in the formula to the cell references that hold them. This is what I was originally trying: =INDEX(I2,XMATCH(F2,I2[Width]),XMATCH(E2,I2[#Headers]))
    In your formula I swapped M_30 to I2, it didn't work. Probably a simple answer, but it has me stumped.

  18. #18
    Registered User
    Join Date
    03-26-2022
    Location
    Queensland, Australia.
    MS-Off Ver
    365
    Posts
    19

    Re: How to filter a cell, dependant on 3 other cells.

    Thanks Menem, those have some of the new formulae that are only available in 365. Won't work in your 2016 version.
    I really appreciate what you showed me earlier. Lot less work if you use 365 though.
    Bless you mate!

  19. #19
    Registered User
    Join Date
    03-26-2022
    Location
    Queensland, Australia.
    MS-Off Ver
    365
    Posts
    19

    Re: How to filter a cell, dependant on 3 other cells.

    Quote Originally Posted by nick.williams View Post
    Or use E2&"" to force it to text if they won't always be 3 characters long.
    =INDEX(M_30,MATCH(F2,M_30[Width],0),MATCH(TEXT(E2&""),M_30[#Headers],0))
    Tried this Nick, but it only caused an error. To few arguments. With it as it was, it was returning 6 characters, like 57.455 for instance.

  20. #20
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,334

    Re: How to filter a cell, dependant on 3 other cells.

    Try

    =INDEX(INDIRECT(I2),MATCH(F2,INDIRECT(I2 &"[Width]"),0),MATCH(E2&"",INDIRECT(I2 &"[#Headers]"),0))

    Use INDIRECT to reference the value in I2: I have included Nick's formula (bold)

  21. #21
    Registered User
    Join Date
    03-26-2022
    Location
    Queensland, Australia.
    MS-Off Ver
    365
    Posts
    19

    Re: How to filter a cell, dependant on 3 other cells.

    So, just to reiterate. This formula works: =INDEX(M_30,MATCH(F2,M_30[Width],0),MATCH(TEXT(E2,"000"),M_30[#Headers],0))
    What I need to change, is instead of the table name M_30 being in the formula. I need the formula to pull whatever table name is referenced in the cell in column I. If I substitute M_30 for I2 in the formula it doesn't work.

  22. #22
    Registered User
    Join Date
    03-26-2022
    Location
    Queensland, Australia.
    MS-Off Ver
    365
    Posts
    19

    Re: How to filter a cell, dependant on 3 other cells.

    Oh Yeah!!!!!!!!
    It works!
    Thanks so much guys! I really appreciate your help.
    I can go to bed now with my mind at ease.

  23. #23
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,334

    Re: How to filter a cell, dependant on 3 other cells.

    Before you nip into bed, can you please mark as solved! ("Thread tools" at top of page)

  24. #24
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016
    Posts
    1,016

    Re: How to filter a cell, dependant on 3 other cells.

    Oh thanks, seem I need to try

    Regards.

+ 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] finding average with date dependant and cell dependant
    By sfoll in forum Excel General
    Replies: 4
    Last Post: 08-24-2015, 04:58 AM
  2. Sum of a cell dependant on the sum of another cells value.
    By phish3rz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-14-2013, 08:35 AM
  3. Replies: 0
    Last Post: 09-22-2012, 02:20 AM
  4. [SOLVED] Colour range of cells dependant on value of a cell VBA
    By ThomasCarter in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-05-2012, 04:26 PM
  5. cell dependant on other cells
    By sdts in forum Excel General
    Replies: 5
    Last Post: 12-30-2010, 07:13 AM
  6. lock cells dependant on another cell value
    By Mistweaver in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-16-2010, 07:49 AM
  7. Replies: 0
    Last Post: 01-27-2010, 05:47 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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.52945 seconds
  • Memory Usage 9,548KB
  • Queries Executed 16 (?)
More Information
Template Usage (35):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)bbcode_quote
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (24)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (7)postbit_attachment
  • (24)postbit_legacy
  • (24)postbit_onlinestatus
  • (24)postbit_wrapper
  • (4)showthread_bookmarksite
  • (7)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (49):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_attachment
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1