One rate sheet - multiple customers/multiple rates based on the one sheet

[awade74]

Hello,
My first two posts were in excel and here is my third...Not sure if this is for excel or for access.
currently i have a base rate sheet with cost including taxes and labor burden (some addition and multiplication formulas included in the main sheet)

normally what i do is have this same sheet as above for each customer and the % of Overhead and Profit we charge to that customer

however i am wondering if a can have a sheet or file or whatever that lists our customers out and the % of overhead/profit for them....maybe a column for the year with the % we charge under it.

my base rate sheet changes each year...as the taxes increase as well as the pay and benefits..

however need to keep a record of this base rate for each year and prior years.

normally i have another spreadsheet with three tabs. one tab where i calculate the hours worked by the trade as the base rate sheet is set up in columns of different trades and their rates/benefits.
then tab one transferrs to tab two which is then set up with
trade 1 st then the hours then the rate we charge them
trade 1 ot then the hours then the rate we charge them
trade 1 dt then the hours then the rate we charge them

trade 2 st then the hours then the rate we charge them
trade 2 ot then the hours then the rate we charge them
trade 2 dt then the hours then the rate we charge them

with a total on the hours and the rate.

any suggestions????

Please help....
Thanks!
Andrea

[SamT]

however need to keep a record of this base rate for each year and prior years.
Name the current rate sheet "CurrentRates". Have all your formulas refer to this sheet by name. Next year, copy it and name the copy "Rates 2014" and change the rates on "CurrentRates" sheet.

Yes, I left out the Space in the name. makes for easier formulas

Sample CurrentRates Table

(Cell A1)
Trade1
Trade2
Trade3


ST
$1.00
$2.00
$3.00


OT
$1.50
$3.00
$4.50


DT
$2.00
$4.00
$6.00



Some sheet's sample formula to return Trade 2 OverTime

=Hlookup(CurrentRates!A1:D4,"Trade2",3)

You can use a Defined Name for the table, say "CurrentRates!LaborRates"
When you prepend the sheet name to the Range Name, then change the sheet name or copy the sheet, that prepend will change to the new sheet name, therefore, it will never confuse Excel formulas as to which sheet's range to use.

Then your formula looks like

=Hlookup(CurrentRates!LaborRates,"Trade2",3)

If you go farther and create some named Formulas on the main sheet, (one that never gets deleted or copied):
Name ST, formula is =2
Name OT, formula is =3
Name DT, formula is =4

Those are constants, they can be on any sheet .and used in any formula in the workbook.

That same formula becomes

=Hlookup(CurrentRates!LaborRates,"Trade2",OT)