Forum Discussion

GabrielTeixeira's avatar
GabrielTeixeira
Copper Contributor
Jan 26, 2023
Solved

How to switch between tables using a dropdown

Hi there,

 

I've got a customer pricing contract that has the same rows and columns applying to different locations. It's annoying to keep scrolling the screen to check each location, as I'd like to combine all contracts into a single one, selecting the location using a dropdown. Is that possible?

 

Of course, only setting a list through the Data Validation won't work, because all data will remain the same. I want the data to change according to the location I selected on the dropdown.

 

See this fictitious example... 

 

CURRENTLY:

 

WHAT I WANT TO DO (but with the data being populated accordingly):

 

  • Microsoft 365 Apps for business
  • Excel version 2211 (Build 15831.20252)
  • Windows 11 Business 22H2
  • OS build 22621.1105Link to the sample Excel sheet 
  • Intel i7-11390H 16GB x64

 

Thanks in advance, guys 🙂

I appreciate your thoughtfulness

excel
Macros and VBA
  • mathetes's avatar
    mathetes
    Jan 27, 2023

    GabrielTeixeira 

     

    I just noticed your example table in your first post. Was that there the first time you posted it? If so, I didn't see it.

     

    Here it is returned to you in with a formula (and table of tables) that does what you want. There no doubt are other solutions, but take a look at this and see if you can adapt what I've done.

     

    I'd give a more complete explanation if I had time, but I'm needing to leave for a drive shortly. Come back with questions if I can help further.

  • mathetes's avatar
    mathetes
    Silver Contributor
    Jan 26, 2023

    GabrielTeixeira 

     

    I'm attaching an example that does the same thing as what you're seeking to do. This is a spreadsheet that changes the table used in a VLOOKUP formula in order to calculate US Federal Income Tax, using different tables based on filing status.

     

    The formula makes use of the relatively new LET function, first, to define the variable ttbl as the result of an INDIRECT  VLOOKUP formula that retrieves the name of the table to be used nested in the yet another VLOOKUP that calculates the tax  using the appropriate table.

    =LET(TTbl,INDIRECT(VLOOKUP(B6,FilingStatus,2,0)),VLOOKUP(A6,TTbl,4,1)+(VLOOKUP(A6,TTbl,6,1)*(A6-(VLOOKUP(A6,TTbl,8,1)))))

     

    If you wish to share your spreadsheet itself (rather than just images) I'd be happy to adapt that formula to your situation. But you're welcome to see if you can reverse engineer the logic of this sample. To share yours, post a copy on OneDrive or in GoogleDrive and paste a link here that grants edit access to that workbook.

     

    • GabrielTeixeira's avatar
      GabrielTeixeira
      Copper Contributor
      Jan 27, 2023

      mathetes 

       

      Thank you very much! Please, have a look at the link: Customer pricing table 

       

      Note that each tab refers to a customer, and that every single orange table (pricing) is identical to another -- only Customer and Location are changing at the header, I suppose that's where our formula gets in.

       

      Can you please show me an example of how could we have the formula applied? Feel free to create a main tab if you wish, or to combine all pricing tables into one single tab if needed.

       

      LINK:

      https://docs.google.com/spreadsheets/d/1ADdVGENYNhp9A5D8fh0fsE6wuBY_MDCX/edit?usp=share_link&ouid=111071986558261781290&rtpof=true&sd=true

      • mathetes's avatar
        mathetes
        Silver Contributor
        Jan 27, 2023

        GabrielTeixeira 

         

        This looks quite different from what I expected. I was expecting two or three tables that needed to be referenced, depending on country or some other location specific. This looks FAR different from that.

         

        So help me navigate/understand a bit more

         

        Note that each tab refers to a customer,  noted, but what, then, is the difference between AAA/ CMH-003 and AAA/EW-001 and so on. I see different Base Rents in some of the arrays WITHIN the tab AAA.

         

        Note that each tab refers to a customer, and that every single orange table (pricing) is identical to another -- only Customer and Location are changing at the header, I suppose that's where our formula gets in.

         

        So help me see where that "identical to another" comes in. Which ones are identical to which other ones? What is the core identifier, then, of those that follow a given pattern or sameness.

         

        For example, in my example of the three income tax tables, there are only three tables, even though there are five filing statuses

        So i use a VLOOKUP to get, from this table, the name of the relevant table for each filing status. I did this after realizing (from the IRS's publications) that the tax table used for Married Filing Joint is in fact the same as the one for Surviving Spouses; similarly Married Filing Separate uses the same table as Single.

         

        What are the basic tables in yours, and what in your detailed sheets calls out the one table that is applicable to it? 

Resources