kind of a database problem

  • Thread starter Thread starter d0wnt0wn
  • Start date Start date
D

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
 
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
 
Back
Top