kind of a database problem

[d0wnt0wn]

Hi I work as a roofing estimator and i am trying to set up a small data
base for about ten different types of estimates.... the data base being
the material + price + tax = total...... is there a way that i can make
a drop down menu on the estimate sheet that accesses the database to do
this.....

if i select a material lets say *shingles* the other 3 columns
(price,tax and total) will automatically fill in to the right of the
material i selected.... dont know how to word this exactly so i hope
someone might understand what i mean.


thanks in advance

 

[Harald Staff]

Hi

A Vlookup function can look for the material entry in a table and return
price. See
http://www.personal-computer-tutor.com/vlookup.htm
http://www.usd.edu/~kbrady/vlookup.html

 

[Arvi Laanemets]

Hi

Create a sheet SetUp
On SetUp worksheet, create a table (col A:B)
Material UnitPrice

and enter the list of your materials with prices for area unit.
On same sheet
E1="TaxBase"
F1="TaxRate"
Select cell E2, and the from menu Data.Validation.List with Source=Price,SUM
Into F2, enter your tax rate %
Define named ranges:
Material=OFFSET(SetUp!$A$2,,,COUNTIF(SetUp!$A:$A,"<>")-1,1)
MaterialTable=OFFSET(SetUp!$A$2,,,COUNTIF(SetUp!$A:$A,"<>")-1,2)
TaxBase=SetUp!$E$2
TaxRate=SetUp!$F$2

Create a sheet Claculation
Create a table
Material Area UnitPrice Price Tax SUM

Select A2, and the from menu Data.Validation.List with Source=Material
C2=IF(A2="","",VLOOKUP(A2,MaterialTable,2,FALSE))
D2=IF(OR(B2="",C2=""),"",B2*C2)
E2=IF(D2="","",ROUND(IF(TaxBase="Price",D2*TaxRate,F2-D2),2))
F2=IF(D2="","",ROUND(IF(TaxBase="Price",D2+E2,D2/(1-TaxRate)),2))

It's all
On SetUp sheet you can select, is tax calculated from endsum or from price,
and set the tax rate.
On Calculation sheet, you select material, and enter the area volme - the
rest is calculated automatically

 

[d0wnt0wn]

thanks very much guys