+ Reply to Thread
Results 1 to 14 of 14

Assistance with selecting from two cells/values with dropdown from table

  1. 11-22-2023, 10:35 AM #1
    MrO
    MrO is offline
    Registered User
    Join Date
    11-26-2012
    Location
    West Virginia, US
    MS-Off Ver
    Excel Pro Plus 2019
    Posts
    69

    Question Assistance with selecting from two cells/values with dropdown from table

    Camping Activities (1)(2) AliGW (1).xlsx
    I got some help yesterday with a similar problem but I am unsure how to adjust the previous formula to work with the slightly different cell set up.

    =_xlfn.LET(_xlpm.f,_xlfn._xlws.FILTER(Kingdom!$Q$29:$X$36,Kingdom!$N$29:$N$36=Activities!G4),INDEX(_xlpm.f,MATCH(LEFT(F4,FIND(" ",F4)-1),_xlpm.f,0)+1))
    I am trying to use the above formula with adjusted values to work with getting a number from the table mentioned above. I tried editing the values to indicate the new cell but it didn't work changing what would be F8 to I8 without success).

    As always thank you for your help!
    Last edited by MrO; 11-23-2023 at 08:11 PM.
    Reply With QuoteRegister To Reply
  2. 11-22-2023, 10:43 AM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,943

    Re: Assistance with selecting from two cells/values with dropdown from table

    Your profile says you are using Excel 2019. 2019 doesn't have the LET or FILTER functions, hence you see _xlfn.LET(_xlpm.f,_xlfn._xlws.FILTER(...

    And _xlpm is a parameter defined in the LET function
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 11-22-2023, 11:45 AM #3
    MrO
    MrO is offline
    Registered User
    Join Date
    11-26-2012
    Location
    West Virginia, US
    MS-Off Ver
    Excel Pro Plus 2019
    Posts
    69

    Re: Assistance with selecting from two cells/values with dropdown from table

    Let is a new parameter for me. Do you have time to help me with a working formula?
    Thank you
    Reply With QuoteRegister To Reply
  4. 11-22-2023, 11:54 AM #4
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,943

    Re: Assistance with selecting from two cells/values with dropdown from table

    That formula, in that workbook, works for me in Excel 365. However, it won't work in Excel 2019. It might have shown the correct results until you tried to change it. At which point, Excel says, no, I can't do that.

    I don't have time at the moment to work out a formula that is compatible with Excel 2019. But someone will come along with one.
    Reply With QuoteRegister To Reply
  5. 11-22-2023, 12:15 PM #5
    MrO
    MrO is offline
    Registered User
    Join Date
    11-26-2012
    Location
    West Virginia, US
    MS-Off Ver
    Excel Pro Plus 2019
    Posts
    69

    Re: Assistance with selecting from two cells/values with dropdown from table

    OK thanks for looking
    Reply With QuoteRegister To Reply
  6. 11-22-2023, 01:09 PM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,792

    Re: Assistance with selecting from two cells/values with dropdown from table

    That was my mistake - I thought you had Excel 2021. Apologies. Can't help with Excel 2019 - sorry.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  7. 11-22-2023, 07:52 PM #7
    MrO
    MrO is offline
    Registered User
    Join Date
    11-26-2012
    Location
    West Virginia, US
    MS-Off Ver
    Excel Pro Plus 2019
    Posts
    69

    Re: Assistance with selecting from two cells/values with dropdown from table

    I have excel at work which is 2019 but my home excel is 365 version 2310.
    Reply With QuoteRegister To Reply
  8. 11-23-2023, 12:32 AM #8
    CARACALLA
    CARACALLA is offline
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    2,009

    Re: Assistance with selecting from two cells/values with dropdown from table

    Activities

    I4=SUMPRODUCT((Kingdom!$N$29:$N$36=Activities!$G4)*(Kingdom!$O$29:$Y$36=Activities!$H4)*(N(+Kingdom!$P$29:$Z$36)))

    Copy down

    I22=SUMPRODUCT((Kingdom!$N$29:$N$36=Activities!$G22)*(Kingdom!$O$29:$Y$36=Activities!$H22)*(N(+Kingdom!$P$29:$Z$36)))

    Copy down

    I34=SUMPRODUCT((Kingdom!$N$29:$N$36=Activities!$G34)*(Kingdom!$O$29:$Y$36=Activities!$H34)*(N(+Kingdom!$P$29:$Z$36)))

    Copy down


    I44=SUMPRODUCT((Kingdom!$N$29:$N$36=Activities!$G44)*(Kingdom!$O$29:$Y$36=Activities!$H44)*(N(+Kingdom!$P$29:$Z$36)))

    Copy down
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 11-23-2023, 12:12 PM #9
    MrO
    MrO is offline
    Registered User
    Join Date
    11-26-2012
    Location
    West Virginia, US
    MS-Off Ver
    Excel Pro Plus 2019
    Posts
    69

    Re: Assistance with selecting from two cells/values with dropdown from table

    Camping Activities (1)(2) AliGW.xlsx

    Ok I made all the suggested changes. While doing so I noticed I had misspelled Intrigue in a couple of spots. After making those corrections a number of previous cells came up with a correct result instead of 0. I tried to replace all instances of the misspelled word without success. However, now when I try to save the file I get the following error:

    This XML file does not appear to have any style information associated with it. The document tree is shown below.
    <recoveryLog>
    <logFileName>error138040_01.xml</logFileName>
    <summary>
    Errors were detected in file 'C:\Users\mistr\Dropbox\PC (2)\Downloads\Camping Activities (1)(2) AliGW.xlsx'
    </summary>
    <additionalInfo>
    <info>
    Damage to the file was so extensive that repairs were not possible. Excel attempted to save your formulas and values, but some data may have been lost or corrupted.
    </info>
    </additionalInfo>
    </recoveryLog>
    Reply With QuoteRegister To Reply
  10. 11-23-2023, 01:05 PM #10
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,792

    Re: Assistance with selecting from two cells/values with dropdown from table

    You can't use my formula in Excel 2019 - sorry. There's no point trying to edit the formula. Use Caracalla's suggestions instead.

    I have just opened the file attached to post #9 with no issues.
    Reply With QuoteRegister To Reply
  11. 11-23-2023, 08:11 PM #11
    MrO
    MrO is offline
    Registered User
    Join Date
    11-26-2012
    Location
    West Virginia, US
    MS-Off Ver
    Excel Pro Plus 2019
    Posts
    69

    Re: Assistance with selecting from two cells/values with dropdown from table

    The weird error mentioned in post 9 is gone after a reboot but the inability to download anything remains.

    Thank you Caracalla and AliGW for your help with this issue. Much appreciated.
    Reply With QuoteRegister To Reply
  12. 11-24-2023, 02:52 AM #12
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,792

    Re: Assistance with selecting from two cells/values with dropdown from table

    What device are you trying to download on? I see the issue you raised only on my iPhone - are you using a mobile device?
    Reply With QuoteRegister To Reply
  13. 11-24-2023, 12:39 PM #13
    MrO
    MrO is offline
    Registered User
    Join Date
    11-26-2012
    Location
    West Virginia, US
    MS-Off Ver
    Excel Pro Plus 2019
    Posts
    69

    Re: Assistance with selecting from two cells/values with dropdown from table

    I have created a different post that was moved to the off topic area for help with this issue.
    Thank you, Ali
    Reply With QuoteRegister To Reply
  14. 11-24-2023, 01:08 PM #14
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,792

    Re: Assistance with selecting from two cells/values with dropdown from table

    Yes, I know - I have responded to it!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to get values when selecting from dropdown list
    By jin0109 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-26-2019, 09:30 PM
  2. How to do loading the dropdown list after selecting match some values?
    By riyajuweb in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-12-2018, 10:50 AM
  3. [SOLVED] selecting different dates in a pivot table dropdown
    By xi603 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-12-2017, 05:53 PM
  4. how to assign conditional values to cells from selecting a item from a dropdown list?
    By dmoul2001 in forum Excel General
    Replies: 1
    Last Post: 05-19-2015, 12:42 AM
  5. [SOLVED] Selecting a table based on two dropdown lists.
    By nudiekap in forum Excel General
    Replies: 7
    Last Post: 02-18-2014, 11:40 AM
  6. Selecting value from dropdown dependant on another cells value
    By shenty in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-27-2012, 08:11 AM
  7. Help! How to have a table automatically appear after selecting an option from a dropdown
    By mya3000 in forum Excel General
    Replies: 0
    Last Post: 07-25-2012, 11:22 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1