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!
Bookmarks