Forum Discussion
Return Values Based on Multiple Criteria
I am talking about column Q - I need it to populate the name of the subcontractor from the SubAnalysis sheet.
Thank you for the suggesting of breaking up the formula to get better input. I will work on that and re-post
Mark -- I'm not in the business of doing estimates on contracting jobs, and recognize it's complicated. I am in the business (or have been; retired now) of helping people design Excel workbooks... yours as it stands strikes me, in general, as taking what's already complicated enough and making it even more complicated. You're clearly comfortable using pretty advanced Excel functions--well done!--but your overall design, i.e., the layout of the sheets, is still reminiscent of working with green ledger paper. So as a result you need to develop these elaborate formulas to extract a number from a cell without making use of clear tabular organization of the "raw data."
That is just an observation, and admittedly not only unsolicited (so feel free to reject it out of hand) but also based on limited experience. Were I to sit down with you, I think I'd be working toward turning at least one of your existing sheets into an Excel table--with "raw data" on it. Then another, or another two, could be more of an "output" sheet. Perhaps two, one for the client, one for the contractor.
What is the relationship of your front two sheets with the "Library" sheet, if I may ask? I didn't find any formulas drawing on it, so wonder if it's just for your reference.
Anyway, I don't mean to discount the work you've done; only to offer the observation that Excel generally works more effectively if at the foundation, there are tables of data (clean rows and columns) that those INDEX|MATCH and LOOKUP and OFFSET functions can readily refer to, and a clear differentiation between the raw data at the Input end of things from the formatted and user-friendly presentation at the Output end. You've got relatively user-friendly presentation at both the Input AND the Output end, and that makes it all a lot trickier to write the formulas.
Thanks for the input.
Library holds the data for calling to the division and Phase columns - Look in the Name Manager and look at =Ph.
I have used a relational database for estimating for years, but my new job does not have the same program and they do not want to purchase the program just yet.
The biggest issue is when data comes in. There is also a lot of variability in project scopes. I have other sheets that pull labor rates based on the contract and staff member as well as a table for different mark-ups based on the contract.
Yes, this does draw a lot of influence from green sheet ledger, but it takes the construction industry a lot of time to change. I would have to wrap my head around the workflow to determine how a master table would function.
To give you an idea of my data input work flow
1. I outline the project based on the scopes required in the project (Columns Div and Phase)
2. I input individual tasks under the scopes in Description
3. I quantify the labor, material and equipment quantities needed for each line item (the quantity is entered in Takeoff quantity) Say for instance a building has 45,000 sqft of carpet tile - 45,000 would go into takeoff quantity, unit would be sqft, a production rate would be entered to get hours then a labor staff would be assigned which would give a labor amount. A material unit rate would then be entered to give a total cost for the 45,000 sqft of carpet tile. The next line down could be rolled carpet with a different production rate and material rate. After that occurs, I may get a subcontractor who gives me a lower bid to install the carpet tile and rolled carpet. I end up canceling the quantities I have for carpet and enter 1 lsum number in Subcontractor Amount.
4. That process i described in 3 will happen multiple times in a single bid and may even happen a few times from one subcontractor to the next for the same scope when better sub numbers come in.
5. then I have the different clients (Project Manager, Accounting, President, Site Superintendent, company client, etc.) Each one of these people will want to see different information at different times.
I am always open to modifying and improving my process as long as it does not increase the risk to the organization.
Thanks for the input and continued conversation.
Mark
Since you've worked with a relational database in the past, you might find Power Query (in some more advanced versions of Excel) to your liking. From all I've read, it bears strong resemblance to working with SQL. All of which is to say that if you had your "raw data" organized in the form of tables, you'd be able to do the kinds of joins that are at the heart of pulling meaningful data out of relational databases.