Hello all,
I have an issue with 2 tabs.
Basically on my tab called Wiki Results, the cells in yellow, i WANT to use this formula:
=LEFT(D2,1) and =right(D2,1) in cell F2 and F3. i Will drag this down
However, this affects my scoring data on the first tab. In cell M7 on this tab, you can see 10 points is displayed. It should be 20 points, not 10.
On Wiki Results tab, the cells in yellow, if I type 2 and 2 (with no formulas) cell M7 on the first tab, you will see 20 points is displayed. which is what i want to see. Why is this?
my formula on the first tab may give some clues: See H7:
=ARRAYFORMULA({"",IF(I$2:$2="",,IFERROR( IFS( MOD(COLUMN(I$2:$2),6)=3,TRANSPOSE(INDEX(SPLIT(VLOOKUP("*"&TRANSPOSE(I$2:$2)&"*",INDEX(Whatsapp!$3:$274,,MATCH(A7,Whatsapp!$2:$2,0)),1,0),":-"),,2)), MOD(COLUMN(I$2:$2),6)=5,TRANSPOSE(INDEX(SPLIT(VLOOKUP("*"&TRANSPOSE(I$2:$2)&"*",INDEX(Whatsapp!$3:$274,,MATCH(A7,Whatsapp!$2:$2,0)),1,0),":-."),,3)), MOD(COLUMN(I$2:$2),6)=1,IF(F7="",,IFERROR(5*TRANSPOSE((INDEX(SPLIT(VLOOKUP("*"&TRANSPOSE(I$2:$2)&"*",INDEX(Whatsapp!$3:$274,,MATCH(A7,Whatsapp!$2:$2,0)),1,0),":-."),,2)=D7)+(INDEX(SPLIT(VLOOKUP("*"&TRANSPOSE(I$2:$2)&"*",INDEX(Whatsapp!$3:$274,,MATCH(A7,Whatsapp!$2:$2,0)),1,0),":-."),,3)=F7)+(SIGN(INDEX(SPLIT(VLOOKUP("*"&TRANSPOSE(I$2:$2)&"*",INDEX(Whatsapp!$3:$274,,MATCH(A7,Whatsapp!$2:$2,0)),1,0),":-."),,2)-INDEX(SPLIT(VLOOKUP("*"&TRANSPOSE(I$2:$2)&"*",INDEX(Whatsapp!$3:$274,,MATCH(A7,Whatsapp!$2:$2,0)),1,0),":-."),,3))=SIGN(D7-F7))+(TRIM(INDEX(SPLIT(VLOOKUP("*"&TRANSPOSE(I$2:$2)&"*",INDEX(SUBSTITUTE(Whatsapp!$3:$274,"-","|"),,MATCH(A7,Whatsapp!$2:$2,0)),1,0),":."),,2))=D7&"|"&F7)),0)), MOD(COLUMN(I$2:$2),6)=4,TRANSPOSE(IF(REGEXMATCH(VLOOKUP("*"&TRANSPOSE(I$2:$2)&"*",INDEX(Whatsapp!$3:$274,,MATCH(A7,Whatsapp!$2:$2,0)),1,0),"Default"),"Default Score",)))))})
Sheet is here. thANKS so much!
https://docs.google.com/spreadsheets...it?usp=sharing
Bookmarks