+ Reply to Thread
Results 1 to 4 of 4

From estimation to production budget with VBA

  1. #1
    Registered User
    Join Date
    12-28-2013
    Location
    Halmstad, Sweden
    MS-Off Ver
    Excel 2010
    Posts
    19

    From estimation to production budget with VBA

    Hi,

    I work in a construction project where we have an estimation software, but it is not linked to any cost control reconciliation software. I am trying to solve this through Excel but not managing to fix it.

    I attach a file with an example with less lines than in original file which also included more descriptive comments whatīs need to be done

    I need help with this:

    1. Copy lines from sheet 1 to sheet 2 if certain criteria are met
    2. If certain criteria in step 1 not is met all these lines should be copied to Sheet 2 but grouped into 1 line in sheet 2 (summing them together if two criterias are met)
    3. Actual installed quantites are filled into Sheet 2 each month, so each month when the macro is used these numbers must remain

    The macro will then be used once a month through a button


    Very thankful if anyone could help me with this. I have managed to solve step 1 myself but not the rest.


    TestingProductionbudget.xlsx

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    26,191

    Re: From estimation to production budget with VBA

    Before going too far with a bespoke analysis, have you considered the flexibility of a Pivot Table?

    Here's your data with a PT for some of your Change data. I don't understand columns V:AA on the Production Budget sheet but it's quite likely that you could derive this information too from a PT. You might need some additional helper columns in the data or use a Calculated field in the PT, but you'll save yourself a lot of hassle if this info can be presented with a PT.
    Attached Files Attached Files
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    12-28-2013
    Location
    Halmstad, Sweden
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: From estimation to production budget with VBA

    Hi,

    I agree that pivot table are to prefer, but we will have thousands of lines in the estimation sheet, but we will not be able to follow up costs and installed quantitiies on each of those lines, thatīs why I need to make the list shorter, by grouping the ones that not has an X.

    In column V to X the user will fill in how much they have procured the items for and quantities, in column Y to AA the user will fill in the quantities actually installed in the buildings.

  4. #4
    Registered User
    Join Date
    12-28-2013
    Location
    Halmstad, Sweden
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: From estimation to production budget with VBA

    I was thinking if it could be solved like this

    copy everything to sheet "Production budget", then remove duplicates with a condition that only removes duplicates if value in column "N" not equals to "X"

    Then just sumifs formulas to get the amounts from "Estimation sheets", and maybe v-lookups to find the value it was procured for and installed quantity, but i could proabably solve that myself by adding a specific number prefix and with help of an extra sheet to take the information from

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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