Formulas Using Words

[Guest]

I was told that Excel can use word formulas. Is this true? I have one
worksheet that has our proposals which adds up the sums of different cells.
I need to create an easy way for my salesmen to include sales tax. Seems
pretty basic, but not every city has the same tax rate and the salesman can't
seem to be able to figure out how to coss refernce the information to plug
in. I had an idea of creating a different worksheet that lists the different
cities and rates, but how can I get that information to pull into thier
proposal by just having them type in the city? Then I could create a cell
that will multiply the tax rate of that city by the subtotal for the total.
Any ideas if excel can do this?

 

[JE McGimpsey]

One way:

Put your cities/tax rate in a second sheet, say 'Sales Tax':

A B
1 City Tax
2 NY 40%
3 DC 110%
4 ...

Choose Insert/Name/Define and enter the following names:

Name in Workbook: Refers To:
Cities
=OFFSET('Sales Tax'!$A$1,1,0,COUNTA(
'Sales Tax'!$A$2:$A$10000),1)

TaxTable =OFFSET('Sales Tax'!$A$2,,,COUNTA(Cities),2)

In your entry cell, say, A1, choose Data/Validation, select List, and
enter

A1: =Cities

in the input box.

In your tax cell, enter

B1: =VLOOKUP(A1,TaxTable,2,False)

to return the tax rate for that city.