Forum Discussion
Return Values Based on Multiple Criteria
I have 2 similar issues that I am trying to solve.
Issue 01 - On sheet DetailedEstimate am trying to populate a cell in Column Q [Subcontractor] based on 3 criteria - The last value above on column A DetailEst[CLIN] matching SubAnalysis [Headers], the last value above in column C DetailEst[Phase] matching SubAnalysis[Phase] and the value in SubAnalysis[Use] = "Yes"
Issue 02 - I think this is actually identical to Issue 01, but I have not convinced myself of that yet. but I need to populate the related dollar value multiplied by 1+[Omissions]
The attached spreadsheet includes SubAnalysis where I have mocked up some data and DetailEstimate where i am wanting the data to report to. I have some notes on DetailEstimate
7 Replies
First, I think your actually talking about the cell in Column R (not Q), because that's where I find this formula:
=IFERROR(INDEX(SubAnalysis,MATCH([@Subcontractor],SubAnalysis[Subcontractor],0),MATCH(@OFFSET(DetailEst[[#Headers],[CLIN]],MATCH(LOOKUP(2,1/(DetailEstimate!$A$6:A9<>""),DetailEstimate!$A$6:A9),[CLIN],0),,COUNTIF([CLIN],LOOKUP(2,1/(DetailEstimate!$A$6:A9<>""),DetailEstimate!$A$6:A9))),SubAnalysis[#Headers],0)),"")
which is just far too complicated to decode. It may be fun to write a formula like this when you can get it to work--it is, I know from experience--but it loses some of the gloss when you try to go back a few months later to fix a problem. And for someone here in the techcommunity forum to try to make sense of it is, I would suggest, harder still, since we're not familiar with the underlying situation [not to take away from those very few who are able to do so]. Most text references on Excel, maybe all, recommend against such lengthy formulas for precisely this reason.
So, a suggestion: is it at all possible for you to break this apart into several different formulas (off to the side, probably on the SubAnalysis sheet itself)? We'll often recommend "helper columns" for this kind of purpose; in your case, the reference would be "helper cells"--but the idea is to take each INDEX/MATCH combo and do it in a cell of its own; similarly with the LOOKUP function; and so forth. Then a single formula that takes the results of those individual (and comprehensible) calculations could be used in cell R9. It would be easier for you to comprehend and debug too.
Also, it forced me to re-register so I ended up with a different user name. Do you know how i can get back to my initial registered account?
Also, it forced me to re-register so I ended up with a different user name. Do you know how i can get back to my initial registered account?
Sorry...no. I'm surprised it forced a new user name. I got into the situation myself (just a couple hours ago, in fact) where I was forced to create a new password, but definitely was still able to use the old user name. I'd suggest seeing if you can just enter the old user name and go through the "I forgot my password" routine to reestablish your original credentials.
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.
