Forum Discussion
How to switch between tables using a dropdown
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.
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.
Thank you very much! Please, have a look at the link: https://docs.google.com/spreadsheets/d/1ADdVGENYNhp9A5D8fh0fsE6wuBY_MDCX/edit?usp=share_link&ouid=111071986558261781290&rtpof=true&sd=true
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
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?
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
Can't thank you enough for your attentiveness and thoughtfulness! I was able to reproduce the INDEX formula on my previous table, and that's fine now.
I've studied this INDEX formula once, but it was not clear to me how to use it in this example. Besides teaching me and spreading the word of the Holy Excel, your assistance is helping me deliver a major task to my directors.
That was really brilliant! Thank you again 🙂