Forum Discussion
concatenate and if statements
Sergei, I don't think I have created the formula properly. In your earlier message you refer to (MinistryTeam[[#data],[" & d$42 & "]]"),$c65),0)
I think that means "go to Ministryteam worksheet, look at those cells and at cell d$42, add them up, and return the number or 0 if they don't match".
But which cells? One column eg A2:A11? or all the cells eg A2:E11?
Also, what is the purpose of the 2nd ampersand and space in [" & d$42 & "]?
Hi Steve,
In such case the formula could be
= COUNTIF(D$2:D$45,$C65) + COUNTIF(OFFSET(WorshipTeam!$A$2,1,MATCH(D$21,WorshipTeam!$A$2:$E$2,0)-1,30),$C65) + COUNTIF(OFFSET(MinistryTeam!$A$2,1,MATCH(D$42,MinistryTeam!$A$2:$E$2,0)-1,30),$C65) - (($C65=D$21)+($C65=D$42))
For first COUNTIF
WorshipTeam!$A$2 is the cell with most left column name;
MATCH finds which column is for the team name in D21;
OFFSET returns the values (30, last parameter) in the column shifted from A2 on one row (second parameter) down and on MATCH()-1 to the right.
and you count how many times value in C65 is in above range.
Please see attached.
Hi Sergei. I want to say how helpful you have been in my project. Really appreciate it. Working on SchHist now. Want to transcribe each month's entries into the appropriate rows without affecting the previous month's entries. Thought I could use an if function based on the date in LeadSch!L$2 then just add 30 days for subsequent months. Used this formula
=if(LeadSch!$L$2=1/6/2018,LeadSch!D$5)
should return Steve, actually returns FALSE. I assume I've got the syntax wrong, but I don't see it.
have modified formula to be
=if(LeadSch!$l$2=datevalue("1/6/2018")),LeadSch!D11)
but when I change the datevalue for subsequent months the value in earlier month shows FALSE and of course I want to preserve the earlier values.
Steve
Hi Steve,
I'm not sure with logic of your rules
"Same task last 4 weeks" - but you have only 4 weeks at all, what to compare?
"More than 4 tasks" - that's better use conditional formatting. Add rule to the cell D63
=(D63>4)
and apply it to entire names/weeks range (see attached)
"Sound vs usher" - if simply compare names when
=IF(OR(D$12=$D43,D$12=$D44,D$43=$D44),"conflict", "okay")
"Worship vs Ministry teams" - was in previous post
"Kids class vs Torah Portion" - again names?
=IF(D$35=$D28,"conflict", "okay")
"Worship vs Ushering" - compare team to name, but two times
=IF(
COUNTIF(OFFSET(WorshipTeam!$A$2,1,MATCH(D$21,WorshipTeam!$A$2:$E$2,0)-1,30),D$43) +
COUNTIF(OFFSET(WorshipTeam!$A$2,1,MATCH(D$21,WorshipTeam!$A$2:$E$2,0)-1,30),D$44), "conflict","okay")"Teaching vs Ushering" - as above
=IF(OR(D$25=$D43,D$25=$D44,D$43=$D44),"conflict", "okay")
"More than 1 Liturgy" - I guess we shall compare if no matches in D17,D18 and D19,D20. When
=IF(SUMPRODUCT(--(D$17:D$18=D$19:D$20)),"conflict", "okay")
and in attached
Sergei, I'm ready to throw in the towel. When you provide answers to my formula questions the formulas are hard for me to understand. everything you provide works but I understand so little I can't expand them to apply to other formula needs. I have 7 formulas to figure out (see attached file) and am running up to a deadline. If not the actual formulas (hopefully) please provide some guidance.
Steve
Hi Steve,
The formula could be like
=IF(COUNTIF( OFFSET(MinistryTeam!$A$2,1,MATCH(D$42,MinistryTeam!$A$2:$E$2,0)-1,30), D$45),"conflict","okay")
You have to select list of people (MinistryTeam) the name of which is given in cell D$42 ("Raymond"), after that you count how many times the name in D$45 is in this list and return result depends on no matches or more than one match.
Similar is for other formulas, first step you have to define the list of people in the selected team. Again, with Tables will be easier, but we use OFFSET here.
For example, for another conflict check if someone from selected Worship team is in selected MinistryTeam the formula could be
=IF(SUMPRODUCT(COUNTIF(<WorshipTeam>,<MinistryTeam>)), "conflict", "okay")
or with how we define the teams
=IF(SUMPRODUCT(COUNTIF( OFFSET(MinistryTeam!$A$2,1,MATCH(D$42,MinistryTeam!$A$2:$E$2,0)-1,30), OFFSET(WorshipTeam!$A$2,1,MATCH(D$21,WorshipTeam!$A$2:$E$2,0)-1,30) )),"conflict","okay")
Please see attached
Hi Steve,
Okay, will check bit later
Thank you so much for your help in the struggle to do the leadership counting. Everything works across the whole month. Now on to the next task. I've attached the updated file for you to look at. At the bottom of the second page of LdrSch you'll see a section called conflict analysis which looks for a situation in which a person might have been scheduled to do two tasks at the same time. In the row called "afterglow" vs Ministry" I've put this formula
=if(d$45=(match(d$42,MinistryTeam!$a$2:$e$2,0)),"conflict",0)
which should return "conflict" because Mike B. is both on afterglow and on the ministry team of Raymond. Instead it returns 0. Can you tell me where I went wrong?