Pulsar3000
New Member
- Joined
- Apr 19, 2021
- Messages
- 44
- Office Version
- 365
- Platform
- Windows
Hello:
The formula in cell T2 returns a number as text. Column I has a date (it matches a date between two other dates based on the name of a place in a different sheet) and seems to be the issue but I'm not sure. I used the Value function and adding a zero at the end and it made the result numeric but it no longer returned the multiple values that it found.
I also concatenated the values in a helper column, copy/pasted them as values, and tried to reduce the function but the matching didn't work. Lastly, I copy pasted Column I with the dates but that too didn't work.
Question: How can I get the formula to return multiple values in one cell be numeric?
Function: T2=(IF(OR(R2="OT Hours",R2="Holiday Hours")=TRUE,TEXTJOIN(", ",TRUE,UNIQUE(IF(($A$2:$A$16=A2)*($D$2:$D$16=D2)*($F$2:$F$16=F2)*(@$I$2:$I$16=I2)*($K$2:$K$16<>0),$N$2:$N$16,""))),0))
Functions/Descriptions of Cells Referenced in Above Function:
R2=INDEX($K$1:$M$1,MATCH(TRUE,INDEX(K4:M4<>0,),0))
A2=Name of place that is just text
D2=Name of person that is just text
F2=INDEX(Type!$B$2:$B$45,MATCH(E2,Type!$A$2:$A$45,0))
I2=INDEX(Rates!$C$2:$C$6,MATCH(1,((Rates!$B$2:$B$6=A2)*(Rates!$C$2:$C$6<=G2)*(Rates!$D$2:$D$6>=G2)),0))
K2=Amount of Hours hardcoded
N2=Rate hardcoded
The formula in cell T2 returns a number as text. Column I has a date (it matches a date between two other dates based on the name of a place in a different sheet) and seems to be the issue but I'm not sure. I used the Value function and adding a zero at the end and it made the result numeric but it no longer returned the multiple values that it found.
I also concatenated the values in a helper column, copy/pasted them as values, and tried to reduce the function but the matching didn't work. Lastly, I copy pasted Column I with the dates but that too didn't work.
Question: How can I get the formula to return multiple values in one cell be numeric?
Function: T2=(IF(OR(R2="OT Hours",R2="Holiday Hours")=TRUE,TEXTJOIN(", ",TRUE,UNIQUE(IF(($A$2:$A$16=A2)*($D$2:$D$16=D2)*($F$2:$F$16=F2)*(@$I$2:$I$16=I2)*($K$2:$K$16<>0),$N$2:$N$16,""))),0))
Functions/Descriptions of Cells Referenced in Above Function:
R2=INDEX($K$1:$M$1,MATCH(TRUE,INDEX(K4:M4<>0,),0))
A2=Name of place that is just text
D2=Name of person that is just text
F2=INDEX(Type!$B$2:$B$45,MATCH(E2,Type!$A$2:$A$45,0))
I2=INDEX(Rates!$C$2:$C$6,MATCH(1,((Rates!$B$2:$B$6=A2)*(Rates!$C$2:$C$6<=G2)*(Rates!$D$2:$D$6>=G2)),0))
K2=Amount of Hours hardcoded
N2=Rate hardcoded