+ Reply to Thread
Results 1 to 5 of 5

Create summaries in a master sheet

  1. #1
    Registered User
    Join Date
    03-05-2012
    Location
    Ft. Lauderdale
    MS-Off Ver
    Excel 2010
    Posts
    2

    Create summaries in a master sheet

    Hello everyone,
    I just joined the forum and have been an excel user for a while but my new job is going to force me to get a little more creative. I am currently evaluating estimates for a large commercial construction project. This is for flooring right now and I want to set-up a formula to re-organize the subcontractors excel sheet the way we need it to spot potential cost reduction points.

    What I'm working with:

    - The subcontractor sent me a 13 sheet workbook. The first one is his simple summaries that I don't really need so I added my own to the front (were at 14 sheets now). Consider sheet 2 worthless, sheets 3-14 where the needed data is, and sheet 1 where I want to put my results.

    - On each data sheet he uses column B to list the room affected (e.g.: 1000C). Column D is the square footage of said room. Column I is the name of the tile being used there (e.g.: 12x12 Blue Ballet Mosaic).

    What I would like to do is:
    1. (Important One): Set up a filter or formula (or both) on sheet 1 so it can give me a summary of each type of tile in each data sheet. For example: My numerical data (SF) in my summary sheet would be =(whenever the text "12x12 Blue Ballet Mosaic" appears in column I add the D cell from that row to a summation forumula). I'm assuming some kind of if/only if function.

    2. (Not critical. I could enter it by hand but automation would be awesome.): Set up a filter or formula where anytime "12x12 Blue Ballet Mosaic" shows up in column I Excel will take the text from the B cell in that same row and start populating a list. For example: "12x12 Blue Ballet Mosaic" shows up in I18, I24, and I30. The text in B18 is "119A", the text in B24 is "127", and the text in B30 is "132". I want the final result of the formula or filter to read just "119A, 127, 132" to show a summation of where it's being used.

    Is what I just typed even possible or am I going to have to sit there with a printed version of the worksheet and go with the old 20 colors of highlighter method? Thanks!

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: New Member - Need help creating complex logic summaries in a master sheet.

    For part 1 I would be seriously tempted to put all 13 sheets into 1 and use a pivot table. That makes things 100x easier than working with multiple sheets.

    I don't really understand point two, so you should upload a sample workbook with any confidential data removed

  3. #3
    Registered User
    Join Date
    03-05-2012
    Location
    Ft. Lauderdale
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: New Member - Need help creating complex logic summaries in a master sheet.

    Merging all the sheets is fine as long as I can still see the separated data althought it would be easier to keep them separate if possible.

    Point 2 is just as a verification summary. I want the master summary (slave sheet) to reflect any chages we make along the way. For example: Right now there are a lot of columns that just say "TBD Quarry Tile" because not all finishes have been finalized. A summary of Quarry Tile is fine for current budgeting but if I go in and swith "TBD Quarry Tile" to "Brand X Porcelain" in room 210 once the architect makes up his mind I would like the summations to change accordingly. Remove X SF from "TBD Quarry TIle" and move it to "Brand X Porcelain" and also remove room "210" from the "TBD Quarry Tile" list and add it to "Brand X Porcelain" list.

    I have uploaded a sample workbook for you to mess with.
    Attached Files Attached Files

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: New Member - Need help creating complex logic summaries in a master sheet.

    eugh, that's a really horribly laid out workbook

    To use excel properly data should be laid out in tables without blank rows or columns, this allows you to use Excel how it was designed to be used. There's very little you can do with the data in its current form.

    You probably have 2 options:
    1. Format all the data as above and merge onto a single sheet with an extra column to say where it is for
    2. Have a look at SumProduct() functions

    Option 2 may give you what you want, but it will be a lot of work and not particularly maintainable, tbh I would spend my time getting the data into a usable format (think tables). You can then use filters and pivot tables to summarise your data.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Create summaries in a master sheet

    Are you still following this post?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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