D
DJ Fitch
Background: For a construction business, I use a Workbook Template for each
job. This template contains a separate worksheet for each Labor, Equipment,
Material, Other, and Subcontract Prices. Each of these sheets has a
different amount of "fields" to suit each individually. I then have a sheet
that I use for the estimate, and up to 60 worksheets for individual days to
track or bill the job out. I have created ID's for each item in my pricing
databases (I call them that because that is what I think they are!) and use
the VLOOKUP function to enter the appropriate items on the estimate or daily
record sheets. The daily record sheets are set up into five different areas
for each category. Each one of the sheets is linked to a summary sheet
within the book that follows total costs for each category by the day.
Another summary sheets is linked to each to provide descriptions and 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 two sources. The one
source is me, I enter items from supplier invoices or quotes, and manually
update as required. The other source is a major supplier that provides us
with either a .txt file or and actual excel file with pricing updates. The
price list contains 100 or so categories (line no.) and over 20,000 parts or
supplies (part no.), a rough description (supplier description), and their
respective costs (cost). I maintain a field in my database for each one the
supplier has but add a couple others in between some. I also sort the
various items according to my descriptions or the id that I created. Again,
I also have the other items that I added in their as well. How do I update
the pricing the supplier provides me with (the line nos., description, and
part nos. are the same each time). I understand or believe that their part
no is the key connection but I am unsure how to get each one to find the
correct item and field to fill in the new price. To complicate it more,
each time I get an update, they may have added or deleted items. Another
issue is I do not use all 20,000 items and would like to omit certain
categories or parts from some of them.
I have no experience with Visual Basic or Macros, but understand or can
figure out a lot of the functions. I often wonder if I am making this whole
process complicated by having so many links and lookups (my template is
5,500KB). It does work rather well although I am always 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 my database
categories, it does not always update the pricing in the 60 sheets or the
summary sheets. There are times when I actually erase my 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 summary sheet. When doing a large
estimate, I have to be very careful to keep an eye on this.
While I am able to track totals of each category from all sheets, I would
like to track individual items from each category to see their respective
total from a job. i.e.; I can tell that my overall equipment price for a job
is $10,000.00 but would like to see that I used a loader for 50 hours at
$50.00 an hour for 2500.00 and a dozer for 40 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 certainly thank
everyone for at least reading ALL of the above.
I would even be willing to work with someone and provide them with my
workbook or a condensed version.
Thank You
job. This template contains a separate worksheet for each Labor, Equipment,
Material, Other, and Subcontract Prices. Each of these sheets has a
different amount of "fields" to suit each individually. I then have a sheet
that I use for the estimate, and up to 60 worksheets for individual days to
track or bill the job out. I have created ID's for each item in my pricing
databases (I call them that because that is what I think they are!) and use
the VLOOKUP function to enter the appropriate items on the estimate or daily
record sheets. The daily record sheets are set up into five different areas
for each category. Each one of the sheets is linked to a summary sheet
within the book that follows total costs for each category by the day.
Another summary sheets is linked to each to provide descriptions and 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 two sources. The one
source is me, I enter items from supplier invoices or quotes, and manually
update as required. The other source is a major supplier that provides us
with either a .txt file or and actual excel file with pricing updates. The
price list contains 100 or so categories (line no.) and over 20,000 parts or
supplies (part no.), a rough description (supplier description), and their
respective costs (cost). I maintain a field in my database for each one the
supplier has but add a couple others in between some. I also sort the
various items according to my descriptions or the id that I created. Again,
I also have the other items that I added in their as well. How do I update
the pricing the supplier provides me with (the line nos., description, and
part nos. are the same each time). I understand or believe that their part
no is the key connection but I am unsure how to get each one to find the
correct item and field to fill in the new price. To complicate it more,
each time I get an update, they may have added or deleted items. Another
issue is I do not use all 20,000 items and would like to omit certain
categories or parts from some of them.
I have no experience with Visual Basic or Macros, but understand or can
figure out a lot of the functions. I often wonder if I am making this whole
process complicated by having so many links and lookups (my template is
5,500KB). It does work rather well although I am always 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 my database
categories, it does not always update the pricing in the 60 sheets or the
summary sheets. There are times when I actually erase my 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 summary sheet. When doing a large
estimate, I have to be very careful to keep an eye on this.
While I am able to track totals of each category from all sheets, I would
like to track individual items from each category to see their respective
total from a job. i.e.; I can tell that my overall equipment price for a job
is $10,000.00 but would like to see that I used a loader for 50 hours at
$50.00 an hour for 2500.00 and a dozer for 40 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 certainly thank
everyone for at least reading ALL of the above.
I would even be willing to work with someone and provide them with my
workbook or a condensed version.
Thank You