Hi everyone,
This issue has been stumping me and I'm at a loss of how to proceed. I'm trying to pull comments from Microsoft Forms results which are dumping into a 2nd sheet on my workbook (Form1). Here's how things are arranged.
'Readout Data'!B3: Name
'Readout Data'!A2: Start date (date format ex. 5/13/2020)
'Readout Data'!B2: End date (date format ex. 5/13/2020)
I'd like to retrieve the comments from Form1!X:X (the comments field) from my table when the name ('Readout Data'!B3) matches the value in Form1!G:G, and the start and end dates ('Readout Data'!A2 & B2) are within the DateTime value on Form1!B:B.
Initially I was using TEXTJOIN in a merged cell to pull in the values and apply a line break between the results. This works perfectly until the results exceeded the character limit of the merged cell/TEXTJOIN formula and gives me a #Value error. Here's what was working until I hit the limit:
="• "&TEXTJOIN(CHAR(10)&"• ",TRUE,IF(($B$3=Form1!G:G)*(Form1!B:B>=A2)*(Form1!B:B<=B2+1),Form1!X:X,""))
To resolve the issue, I've un-merged the range, and have shifted to using an Index Match Array, but it's failing to find any of the comments based on my criteria (getting #N/A):
{=INDEX(Form1!$X:$X,MATCH(1,($B$3=Form1!G:G)*($A$2>=Form1!B:B)*($B$2+1<=Form1!C:C),0))}
Hoping someone might know how I can adjust my Index Match Array to find the results or even a different formula to use. The only limitation I know I have is I cannot use VBA as a solution because this is all contained on an Excel Online file so the Forms Results can continue dumping the results into the sheet automatically as they're submitted.
Thanks everyone!
This issue has been stumping me and I'm at a loss of how to proceed. I'm trying to pull comments from Microsoft Forms results which are dumping into a 2nd sheet on my workbook (Form1). Here's how things are arranged.
'Readout Data'!B3: Name
'Readout Data'!A2: Start date (date format ex. 5/13/2020)
'Readout Data'!B2: End date (date format ex. 5/13/2020)
I'd like to retrieve the comments from Form1!X:X (the comments field) from my table when the name ('Readout Data'!B3) matches the value in Form1!G:G, and the start and end dates ('Readout Data'!A2 & B2) are within the DateTime value on Form1!B:B.
Initially I was using TEXTJOIN in a merged cell to pull in the values and apply a line break between the results. This works perfectly until the results exceeded the character limit of the merged cell/TEXTJOIN formula and gives me a #Value error. Here's what was working until I hit the limit:
="• "&TEXTJOIN(CHAR(10)&"• ",TRUE,IF(($B$3=Form1!G:G)*(Form1!B:B>=A2)*(Form1!B:B<=B2+1),Form1!X:X,""))
To resolve the issue, I've un-merged the range, and have shifted to using an Index Match Array, but it's failing to find any of the comments based on my criteria (getting #N/A):
{=INDEX(Form1!$X:$X,MATCH(1,($B$3=Form1!G:G)*($A$2>=Form1!B:B)*($B$2+1<=Form1!C:C),0))}
Hoping someone might know how I can adjust my Index Match Array to find the results or even a different formula to use. The only limitation I know I have is I cannot use VBA as a solution because this is all contained on an Excel Online file so the Forms Results can continue dumping the results into the sheet automatically as they're submitted.
Thanks everyone!