How to have Excel determine the correct Sales Tax to use.

[trex0530]

I have a pretty large spreadsheet to enter all our company's vehicle maintenance logs. I have 3 columns 1. Cost 2. Tax 3. Total Cost. Currently I have it selecting the sales tax from another cell but the problem I am starting to run into is that not all of the places we take the vehicles to have the same tax rate. So I started changing the spreadsheet and started inputting an IF function. The problem I am running into is that it is getting pretty long. I am not sure if the IF function is the best option for this purpose. The other issue I foresee are for the vehicles located at other offices (different cities). If the state would simply have one stupid sale tax rate, I'd be happy but so far I these three tax rates 7%, 7.25% & 7.5%, and I have multiple shops that fall within the same tax rate, but I have to enter each one of them individually.

Here is my formula that isn't all that long, but has the potential to be really long with 30 different shops that we could use throughout the state of Ohio.

=IF(U14=Lists!C$20,Q14*0.075,IF(U14=Lists!C$27,Q14*0.0725,IF(U14=Lists!C$29,Q14*0.07,IF(U14=Lists!C$9,Q14*0.075,0))))

I have 22 tabs in total on my spreadsheet, 17 (1 per vehicle) of them are where I enter each invoice whenever we have a work done on the vehicles. The "Lists" tab contains Column A listing all sorts of repair types, Column C is for the Service Centers, Column J has 5 different Sales Tax Rates. As you can see in my formula, I have 7.5% listed twice because of two different shops and I was about to enter a third when I decided to search online for something easier.

 

[Joe4]

Welcome to the Board!

Better to create a lookup table and use VLOOKUP.
See:
MS Excel: How to use the VLOOKUP Function (WS)
Excel Tips: How to Use Excel's VLOOKUP Function

 

[CamFlawless]

I am not sure I am understanding your exact needs, but it sounds like a LOOKUP table would make your life much easier. You could create a tab that list each service center/shop and the corresponding tax rate. Then, you could use the VLOOKUP function to determine the appropriate sales tax for the record of interest.

 

[trex0530]

</gs>I am not sure I <gs id="1fc171bd-6ef3-4ea5-a86e-7bdc4efad81c" ginger_software_uiphraseguid="caa984bb-51b5-47f9-9238-ee00cf09c917" class="GINGER_SOFTWARE_mark">am understanding</gs> your exact needs, but it sounds like a LOOKUP table would make your life much easier. You could create a tab that <gs id="b5fc8deb-a855-408c-ab6f-88d5c4b80a17" ginger_software_uiphraseguid="1257c01e-1e17-47c6-8998-23d0fa7d204d" class="GINGER_SOFTWARE_mark">list</gs> each service center/shop and the corresponding tax rate. Then, you could use the VLOOKUP function to determine the appropriate sales tax for the record of interest<gs id="10e27a32-5d98-4778-b7ba-73e0238eee36" ginger_software_uiphraseguid="70ef59c5-0aef-4286-81e5-b7268b967112" class="GINGER_SOFTWARE_mark">.</gs>

You've got it. I'm trying to figure out the VLOOKUP formula. The other issue I am running into is a national service center. For <gs id="9b9650ea-b324-46bc-a61f-ebe10bbf85f0" ginger_software_uiphraseguid="92804cbb-866a-4fb9-abba-0f687e6cb168" class="GINGER_SOFTWARE_mark">example I</gs> have Grismer in Columbus & <gs id="6933791d-0a5e-4542-b18e-eda36c6b2804" ginger_software_uiphraseguid="92804cbb-866a-4fb9-abba-0f687e6cb168" class="GINGER_SOFTWARE_mark">Cincinnati Ohio</gs> both with different tax rates. How can the VLOOKUP formula figure out which tax rate to use?

 

[Joe4]

I'm trying to figure out the VLOOKUP formula

Take a look at those links provided. They show you exactly how to set up a LOOKUP table and use the VLOOKUP function.

For <gs id="9b9650ea-b324-46bc-a61f-ebe10bbf85f0" ginger_software_uiphraseguid="92804cbb-866a-4fb9-abba-0f687e6cb168" class="GINGER_SOFTWARE_mark" style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">example I</gs>have Grismer in Columbus & <gs id="6933791d-0a5e-4542-b18e-eda36c6b2804" ginger_software_uiphraseguid="92804cbb-866a-4fb9-abba-0f687e6cb168" class="GINGER_SOFTWARE_mark" style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">Cincinnati Ohio</gs> both with different tax rates.

How are they differentiated? Logically speaking, how would you tell which one needs to be used?

 

[trex0530]

</gs>Take a look at those links provided. They show you exactly how to set up a LOOKUP table and use the VLOOKUP function.


How are they differentiated? Logically speaking, how would you tell which one needs to be used<gs id="8718f0aa-964a-4120-beca-e0bbfb0aa538" ginger_software_uiphraseguid="f9c0ac3b-0058-46ab-8857-8b29485418f0" class="GINGER_SOFTWARE_mark">?</gs>

That's where I am running into my new problem. From tab #1 - #18 list each vehicle. Each tab is color coded so that I know which location they belong (e.g. Columbus, Dayton, Cincinnati, Kentucky). But that is simply a visual thing, no code anywhere explaining what each is. I do have some VB code within each <gs id="1a1e9114-90af-43c0-8aa0-83235be7201d" ginger_software_uiphraseguid="54ec9974-620c-43a1-acdc-43b28a86562c" class="GINGER_SOFTWARE_mark">tab but</gs> I am not seeing where I can name that tab so VLOOKUP knows that this is a Cincinnati vehicle and therefore should only find Cincinnati <gs id="426fb0b1-d98f-44e0-9cfe-720a26556e19" ginger_software_uiphraseguid="54ec9974-620c-43a1-acdc-43b28a86562c" class="GINGER_SOFTWARE_mark">ServiceCenters</gs> and <gs id="2b0484d6-1f05-4db2-b7b4-e396483bdd57" ginger_software_uiphraseguid="54ec9974-620c-43a1-acdc-43b28a86562c" class="GINGER_SOFTWARE_mark">TaxRates</gs>. <gs id="1c0cc62e-f414-456d-bf09-658c667444ef" ginger_software_uiphraseguid="68145993-fda0-4c7e-88e4-365335f059e0" class="GINGER_SOFTWARE_mark">In</gs> my table I've listed Grismer twice (once for Columbus @7<gs id="f2eae1e7-a5aa-4084-9919-bbe04888fa00" ginger_software_uiphraseguid="68145993-fda0-4c7e-88e4-365335f059e0" class="GINGER_SOFTWARE_mark">.</gs>5%, once <gs id="dd4ffd71-bf05-4fe4-b70a-5a966f97edd1" ginger_software_uiphraseguid="68145993-fda0-4c7e-88e4-365335f059e0" class="GINGER_SOFTWARE_mark">for</gs> Cincinnati @ 6.5%). My table has ServiceCenter, TaxRate & Location headers.

I feel I need to explain something in a little more detail to make sure that we aren't just wasting time here. Within one of the vehicle tabs I have Column Q=Cost, R=Tax, S=Total. The formula in S is simply adding Q+R. Column R is where I started entering my IF Statement which it looks to Column U for the ServiceCenter. Each cell in Column U contains a drop-down list referencing my Lists tab where I choose the ServiceCenter (SC <gs id="35c77414-af09-4272-8842-e6e99fe039c4" ginger_software_uiphraseguid="2d21ecf2-c3cf-45b0-a544-56d3e97b3d38" class="GINGER_SOFTWARE_mark">going</gs> forward). So if I change the SC from one shop to another and that shop is located in a different <gs id="83a6a820-6fe3-4e9d-ac01-4eb3ec85dcb2" ginger_software_uiphraseguid="78b36131-484f-459f-9d11-e55e33193432" class="GINGER_SOFTWARE_mark">TaxRate</gs> (TR) area then it'll change columns R and S. But with 30 SC's in my list and can only be expected to grow as the company grows, I am wanting something easier and more functional.

I hope that helped clarify things a little. The only other thing I can think of is to send you the actual spreadsheet so that you can actually see and understand it.

 

[Joe4]

Yes, it is very hard to understand without actually seeing the data. There are instructions on how you can post images found in the "Posting Aids" section of this link here: http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html. There is also a "Test Here" forum on this site that you can use to test those tools before using them in your threads.

The other option you can do is to upload the file to a file sharing site and provide a link here. The caveat there is because of security concerns, many people are unwilling of unable to download files off the internet. I can do it from home, but not at work.

My original though was to create one giant lookup table that has all your locations and related tax rates, and reference that.
However, if you want to program something automatically, you many need to try to avoid situations like this:

Each tab is color coded so that I know which location they belong (e.g. Columbus, Dayton, Cincinnati, Kentucky). But that is simply a visual thing, no code anywhere explaining what each is.

You want to create situations that you can create formulas or programs against easily.