+ Reply to Thread
Results 1 to 17 of 17

Creating a Final Estimate Form and a Final Materials List based on Worksheet Results

  1. #1
    Registered User
    Join Date
    12-13-2013
    Location
    Oregon
    MS-Off Ver
    Excel 2010
    Posts
    10

    Creating a Final Estimate Form and a Final Materials List based on Worksheet Results

    I have created a worksheet "Materials Budget", that walks the user through the assembly process of remodeling so that the person doesn't forget to include items in the Final Estimate. Some of the row items of the Materials Budget, will not be used, so I set up a check box to highlight the row if the user will is going to use it for focus sake, and in the same row a box that indicates that the corresponding material indicated is approved for purchasing with an "x" or "y". I am using columns B:T

    I am trying to create a "Final Estimate" Form that auto-populates if column Q, of the MaterialsBudget! contains text "x" or "y" (approval for purchase).

    The "Final Estimate" should only include rows that meet the criteria "x or y", and should only display row contents of the ranges:

    Supplies_Bathroom1
    Supplies_Bathroom2
    Supplies_Bedroom1
    Supplies_Bedroom2
    Supplies_Kitchen
    Supplies_FrontPorch

    End result will look like:
    Bedroom-Master Supplies Chosen SKU Cost Qty Supplier Balance
    (above is in Range Supplies_Bathroom1


    I am also trying to create a Final Materials Order List form that will first look at MaterialsBudget! for rows where cell Q has an "x or y" (approval for purchase), then sort the MaterialsBudget! by Column R or range "Supplier", or alternatively cell contains "Home".

    The result of the Final Materials List should should contain all materials to be purchased from supplier "home" as indicated in column R, in numeric order of MaterialsBudget! column "t".

    End result will look like:
    Description SKU Cost Qty (in order of numeric department 19-electrical, 20-plumbing, etc which is already in column T)
    (above is in Range: Supplies_Bathroom1)

    Can anyone help with this?

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Creating a Final Estimate Form and a Final Materials List based on Worksheet Results

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    12-13-2013
    Location
    Oregon
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Creating a Final Estimate Form and a Final Materials List based on Worksheet Results

    The attached file is the "Project Budget" file, and the first worksheet is the "Materials Budget".

    Materials Budget column Cell "Q" tells the Materials Estimate and Materials Fax Sheet to suppliers to insert all rows in the Materials Budget that contain an "X" or "Y" in cell Q.

    The Materials Fax sheets look for content in Materials Budget cell Q, and also look for the word "Home" or "Lowes" in column "R" and only copies information that is either Home Depot, or only Lowes.

    The Materials Estimate provided is a mock up of what it might look like when auto competed.

    The Materials Fax is also a mock up of what it might look like when auto completed.

    FYI,column "A" in the Materials Budget, when it is not empty removes the blue highlight to reveal the true colors.

    Any help would be appreciated.

    --Crunched for time
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    12-13-2013
    Location
    Oregon
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Creating a Final Estimate Form and a Final Materials List based on Worksheet Results

    I don't know how to use VBA yet, but did discover a code that I tried to modify to make the Materials_Budget information auto list to the Materials_Estimate worksheet and the Fax worksheets.
    Please Login or Register  to view this content.
    Last edited by Crunched For Time; 12-16-2013 at 04:03 PM. Reason: Figured out how to use code tags

  5. #5
    Registered User
    Join Date
    12-13-2013
    Location
    Oregon
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Creating a Final Estimate Form and a Final Materials List based on Worksheet Results

    Tried and failed the following VBA Code:
    Please Login or Register  to view this content.
    Last edited by Crunched For Time; 12-16-2013 at 04:02 PM. Reason: Figured out how to use code tags

  6. #6
    Registered User
    Join Date
    12-13-2013
    Location
    Oregon
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Creating a Final Estimate Form and a Final Materials List based on Worksheet Results

    Deleted to include code tags
    Last edited by Crunched For Time; 12-16-2013 at 04:01 PM. Reason: Didn't include code tags

  7. #7
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Creating a Final Estimate Form and a Final Materials List based on Worksheet Results

    Hi Crunched,

    Glad to see that you're trying something but:

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

  8. #8
    Registered User
    Join Date
    12-13-2013
    Location
    Oregon
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Creating a Final Estimate Form and a Final Materials List based on Worksheet Results

    Also tried:

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    12-13-2013
    Location
    Oregon
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Creating a Final Estimate Form and a Final Materials List based on Worksheet Results

    xladept,

    Thanks for the tip, I'm still a "greengo" at VBA.

    -Crunched For Time

  10. #10
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Creating a Final Estimate Form and a Final Materials List based on Worksheet Results

    Thanks for the code tags - is just y in Q the criterion or both x, and y in Q? And, what about the x in A, does that need to be present for the Materials Buget??

  11. #11
    Registered User
    Join Date
    12-13-2013
    Location
    Oregon
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Creating a Final Estimate Form and a Final Materials List based on Worksheet Results

    either x or y in column Q.

    The A column "x" is set to remove the conditional blue formatting so that the user can differentiate what row items he's working with from those he doesn't need. I think it's probably a good idea to include the A column.


    Thank you!

  12. #12
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Creating a Final Estimate Form and a Final Materials List based on Worksheet Results

    Hi Crunched,

    I've come to an impasse - I don't know how to get the labor hours - but this should get a lot of your Materials Estimate sheet:

    Please Login or Register  to view this content.
    Last edited by xladept; 12-16-2013 at 07:56 PM. Reason: Details

  13. #13
    Registered User
    Join Date
    12-13-2013
    Location
    Oregon
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Creating a Final Estimate Form and a Final Materials List based on Worksheet Results

    xladept,

    Thank you, you got a lot farther than I had.

    The code still needs tweeking to resolve some remaining issues.

    There are a total of 13 construction areas (4 bedrooms, 2 bathrooms, 1 hallway, 1 kitchen, 2 porches, 1 Florida Room, 1 Garage, and 1 Laundry room), and the Final Estimate is currently separating row items into separate into 48 construction areas duplicating several rooms.

    The Master Bedroom order has ten entries, but the Final Estimate only displays eight rows at the top of the form, and then duplicates of the first two rows are repeated below a few times.

    I put numbers in row T to classify the item types so that stores could more accurately pull the materials for the order. I see that the faxes are not populating with or without those number classifications.

    If anyone can assist with this code please reply.

    Thank you xladept for your assistance.

    --Crunched For Time

  14. #14
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Creating a Final Estimate Form and a Final Materials List based on Worksheet Results

    You're welcome! Do we need more rows for the materials, (i coded for 10 as shown in the sample).

    I was working on the fax aspect when I got a gig over the phone, don't know when I'll be able to get back to it

    BTW - T is a column, I think that's what you mean.

    You should post the new sample.
    Last edited by xladept; 12-17-2013 at 12:32 AM.

  15. #15
    Registered User
    Join Date
    12-13-2013
    Location
    Oregon
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Creating a Final Estimate Form and a Final Materials List based on Worksheet Results

    The Materials Budget sheet is there for users to work out calculations, shop, get pricing, set quantities, and a final price. The Q column is there to move forward with purchasing, and all of those rows that have a "Y" marked on the Materials Budget sheet are copied to the Materials Estimate sheet, then segregated apart dependent on Materials Estimate row H, and copied onto one of the retailer Faxes.

    Right now instead of filling up the Materials Estimate sheet with all of the qualifying rows, it does two things, it only prints the first nine rows of any Room, and it has listed rooms and it's items multiple times in duplication on the Materials Estimate sheet.

    The Fax sheets are not filled out.

    Anyone that can assist us on ironing out the details would be of much help.

    Thank you xladept!

    --Crunched For Time

  16. #16
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Creating a Final Estimate Form and a Final Materials List based on Worksheet Results

    I'm nearly finished with my gig. I may have time to help tomorrow? If you still need it by then

  17. #17
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Creating a Final Estimate Form and a Final Materials List based on Worksheet Results

    Hi Crunched,

    Finished my gig - try this next installment:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. creating a final list of selected amount
    By matskiuk in forum Excel Charting & Pivots
    Replies: 24
    Last Post: 10-17-2012, 08:52 AM
  2. [SOLVED] Search a Word across the WorkSheet and list the output in final column
    By Mithran in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-16-2012, 08:56 PM
  3. Replies: 0
    Last Post: 06-14-2012, 11:39 AM
  4. replacing lowest test grade with final if final is higher formula help
    By colbyclay in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-16-2012, 02:48 AM
  5. [SOLVED] How do I calculate cost of raw materials to final product?
    By just desserts in forum Excel General
    Replies: 2
    Last Post: 01-28-2006, 03:20 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1