Forum Discussion
HELP WITH COMBINING LARGE FORMULAS
Hello gurus! I have attached my examples for you to look at to help my questions make sense. I have two separate formulas that work for different parts of my overall Excel project. One formula is for static information and the other formula is for dynamic data (or multi-data cells). I want to combine the two formulas to ultimately return the information on the green "Combined Info" tab...the Candidate ID, #ComboMatch (the # of Job Order matches) and JobOrderIDs.
The two working formulas I have are:
Static formula: =COUNTIFS(JOs[Dept],[@Dept],JOs[Relo],[@Relo],JOs[2DigZip],[@2DigZip])+(0.00001*ROW())
Dynamic formula: =(IF((ISNUMBER(SEARCH("in play",'Practice Data 2.xlsx'!CandTable[@Status]))+ISNUMBER(SEARCH("ready",'Practice Data 2.xlsx'!CandTable[@Status])))*('Practice Data 2.xlsx'!JOtable[@Status]="in play"),"1","0"))*(IF((ISNUMBER(SEARCH("contract",'Practice Data 2.xlsx'!CandTable[@EmpPref]))+ISNUMBER(SEARCH("perm",'Practice Data 2.xlsx'!CandTable[@EmpPref])))*('Practice Data 2.xlsx'!JOtable[@EmpPref]="contract"),"1","0"))
How do I combine these two formulas into one large one? I am not sure which is more useful, TEXTJOIN or IF(ISNUMBER(SEARCH....)).
Thanks for reading and for your helpful ideas!
Ronna M.
7 Replies
Hi
First the ResultsTable is referring to another file Practice Data 2.xlsx which you didn't provide - thus resulting the formulas in #REF errors.
And second what exactly are you trying to do? What is the desired output?
Could you provide a sheet with the desired output (just the values)?
Is Power Query available?
