D
DJ Fitch
Background: For a construction business, I use a Workbook Template fo
each job. This template contains a separate worksheet for each Labor
Equipment, Material, Other, and Subcontract Prices. Each of thes
sheets has a different amount of "fields" to suit each individually.
then have a sheet that I use for the estimate, and up to 60 worksheet
for individual days to track or bill the job out. I have created ID'
for each item in my pricing databases and use the VLOOKUP function t
enter the appropriate items on the estimate or daily record sheets.
The daily record sheets are set up into five different areas for eac
category. Each one of the sheets is linked to a summary sheet withi
the book that follows total costs for each category by the day.
Another summary sheets is linked to each to provide descriptions an
total cost for the day and a total.
Current Major Issue: My material database contains numerous fields
ie; line no., part no., supplier description, size, cost, markup1
markup2, price, etc. This database gets the information from tw
sources. The one source is me, I enter items from supplier invoices o
quotes, and manually update as required. The other source is a majo
supplier that provides us with either a .txt file or and actual exce
file with pricing updates. The price list contains 100 or s
categories (line no.) and over 20,000 parts or supplies (part no.),
rough description (supplier description), and their respective cost
(cost). I maintain a field in my database for each one the supplie
has but add a couple others in between some. I also sort the variou
items according to my descriptions or the id that I created. Again,
also have the other items that I added in their as well. How do
update the pricing the supplier provides me with (the line nos.
description, and part nos. are the same each time). I understand o
believe that their part no is the key connection but I am unsure how t
get each one to find the correct item and field to fill in the ne
price. To complicate it more, each time I get an update, they ma
have added or deleted items. Another issue is I do not use all 20,00
items and would like to omit certain categories or parts from some o
them.
I have no experience with Visual Basic or Macros, but understand or ca
figure out a lot of the functions. I often wonder if I am making thi
whole process complicated by having so many links and lookups (m
template is 5,500KB). It does work rather well although I am alway
trying to make it better.
Some of the other issues that I would like to improve are:
I have issues at times if I change a markup in one or all of m
database categories, it does not always update the pricing in the 6
sheets or the summary sheets. There are times when I actually erase m
summary sheet and then hit undo and it usually gets the prices updated
I have had times when it does not calculate or update the summar
sheet. When doing a large estimate, I have to be very careful to kee
an eye on this.
While I am able to track totals of each category from all sheets,
would like to track individual items from each category to see thei
respective total from a job. i.e.; I can tell that my overall equipmen
price for a job is $10,000.00 but would like to see that I used
loader for 50 hours at $50.00 an hour for 2500.00 and a dozer for 4
hours at 40.00 an hour for 1600.00 so on and so forth.
If anyone can help me I could not thank you enough and I certainl
thank everyone for at least reading ALL of the above.
I would even be willing to work with someone and provide them with m
workbook or a condensed version.
Thank You
D
each job. This template contains a separate worksheet for each Labor
Equipment, Material, Other, and Subcontract Prices. Each of thes
sheets has a different amount of "fields" to suit each individually.
then have a sheet that I use for the estimate, and up to 60 worksheet
for individual days to track or bill the job out. I have created ID'
for each item in my pricing databases and use the VLOOKUP function t
enter the appropriate items on the estimate or daily record sheets.
The daily record sheets are set up into five different areas for eac
category. Each one of the sheets is linked to a summary sheet withi
the book that follows total costs for each category by the day.
Another summary sheets is linked to each to provide descriptions an
total cost for the day and a total.
Current Major Issue: My material database contains numerous fields
ie; line no., part no., supplier description, size, cost, markup1
markup2, price, etc. This database gets the information from tw
sources. The one source is me, I enter items from supplier invoices o
quotes, and manually update as required. The other source is a majo
supplier that provides us with either a .txt file or and actual exce
file with pricing updates. The price list contains 100 or s
categories (line no.) and over 20,000 parts or supplies (part no.),
rough description (supplier description), and their respective cost
(cost). I maintain a field in my database for each one the supplie
has but add a couple others in between some. I also sort the variou
items according to my descriptions or the id that I created. Again,
also have the other items that I added in their as well. How do
update the pricing the supplier provides me with (the line nos.
description, and part nos. are the same each time). I understand o
believe that their part no is the key connection but I am unsure how t
get each one to find the correct item and field to fill in the ne
price. To complicate it more, each time I get an update, they ma
have added or deleted items. Another issue is I do not use all 20,00
items and would like to omit certain categories or parts from some o
them.
I have no experience with Visual Basic or Macros, but understand or ca
figure out a lot of the functions. I often wonder if I am making thi
whole process complicated by having so many links and lookups (m
template is 5,500KB). It does work rather well although I am alway
trying to make it better.
Some of the other issues that I would like to improve are:
I have issues at times if I change a markup in one or all of m
database categories, it does not always update the pricing in the 6
sheets or the summary sheets. There are times when I actually erase m
summary sheet and then hit undo and it usually gets the prices updated
I have had times when it does not calculate or update the summar
sheet. When doing a large estimate, I have to be very careful to kee
an eye on this.
While I am able to track totals of each category from all sheets,
would like to track individual items from each category to see thei
respective total from a job. i.e.; I can tell that my overall equipmen
price for a job is $10,000.00 but would like to see that I used
loader for 50 hours at $50.00 an hour for 2500.00 and a dozer for 4
hours at 40.00 an hour for 1600.00 so on and so forth.
If anyone can help me I could not thank you enough and I certainl
thank everyone for at least reading ALL of the above.
I would even be willing to work with someone and provide them with m
workbook or a condensed version.
Thank You
D