Forum Discussion
Having a text formula update automatically across multiple sheets when I add additional information
Hello, all. So I currently have an IFS formula written into cells C3:C30. It's set up so that if I type in a city name it puts in the tax rate so I don't have to type it out constantly for each ...
Hi johnsboxftm ,
If you have the option to create an additional table to store all of the cities and tax rates, you can use the VLOOKUP instead of the IFS. In the table below, I stored some cities and tax rates in a separated sheet:
Then, in your table you just apply this formula in the Tax Rate column: =VLOOKUP(B2, Sheet5!$A$1:$B$36, 2, FALSE). It will looks like this:
You just have to take the following precautions:
1. When you add a new city to the list, make sure to expand the range of the table used in VLOOKUP formula;
2. The city name (the look up value) have to be on the left side of the table where you store the tax rates;
3. Keep the last argument as FALSE.
If this answer does not solve your problem, please let me know.
Thank you for this, I will implement it shortly, If I may ask, how did you transfer the information from my IFS formula to the columns on sheet 5 when you made the table? Did you have to enter them each individually or is there a way to import that info?
- 1. I just copied your formula and pasted in a cell;
2. After, used the Text to Columns feature, in data tab of the ribbon, to separate it into columns, using comma as separator;
3. Then I copied the whole row and pasted in a cell, using the Transpose paste option, transforming it into a column with cities and tax rates;
4. Then I filtered all of the results by "%", copied it and pasted somewhere else;
5. After have the tax rates properly saved, just deleted all of them from the column and removed the duplicates to delete the blank cells and let only the city names;
6. Finally, I pasted the tax rates (that I saved somewhere else) on the side of the cities.
You can find this table in the file that I sent you in the last comment. In any case, double check it to make sure that I didn't messed with some tax rate in this process 🙂