Forum Discussion
Help with Excel report creation for review results from multiple worksheets...
OK so I think maybe in your RESULTS table the 'met expectations' column might be a little different or something and therefor the conditional check might be 'passing' when it shouldn't and besides it seems all we care about is if the # reviews is >0 so try this:
=LET(filterBy, RESULTS!A2:C2, resultTable, RESULTS!A5:E12, columnCounts, {5,6,5,5,5,7,6,7}, report, LAMBDA(result_filter,result_row,table_all, LET(table_data, DROP(table_all,1), table_matches, (CHOOSECOLS(table_data,3)=INDEX(result_filter,1))* (CHOOSECOLS(table_data,2)>=INDEX(result_filter,2))* (CHOOSECOLS(table_data,2)<=INDEX(result_filter,3)), IF(N(INDEX(result_row,2)), VSTACK("*** "&INDEX(result_row,1)&" ***", TAKE(table_all,1), FILTER(table_data, table_matches, "")), ""))), total, REDUCE("",SEQUENCE(ROWS(resultTable)),LAMBDA(p,q,VSTACK(p, LET(tbl, INDIRECT("tbl"&TEXTJOIN(,,TEXTSPLIT(INDEX(resultTable,q,1),{" ","-"}))&"[#ALL]"), report(filterBy, CHOOSEROWS(resultTable,q), CHOOSECOLS(tbl,SEQUENCE(,INDEX(columnCounts,q)),SEQUENCE(,2,COLUMNS(tbl)-1)))) ))), IFNA(IF(total=0, "", total),""))and not to beg for likes but feel free to give me some if you like
Sorry about that. I mean it appears that the code runs fine until it comes to some point where there are not any reviews done. When it gets to one of those, the whole thing just displays as an error. Specifically '#Value!'.
I came to the conclusion because, whenever I isolate the code to one review category at a time, the results display perfectly for the ones that have reviews but display the error for the ones with no reviews. In other words, the ones that don't return a score to be determined as either 'Not Met', 'Met' or 'Exceeds' for some goal.
I hope that makes more sense. It had me thinking that something needs to be in the code to tell it to skip over the ones that don't return a value and keep calculating. Seems that when it finds a zero value anywhere, it's returning the error.
Remember, I don't really know anything much. Lol!
Ok, may have a better way to explain...
When I look at a staff who has reviews for 'Claims', 'Misc Assignments', 'Process Protest' and 'IDD', the results appear perfectly on the report tab.
However, if I choose a staff who has reviews for 'Misc Assignment', 'Process Protest', but not the 'Claims' category, no results appear for any of them. Just returns the error.
OK so I think maybe in your RESULTS table the 'met expectations' column might be a little different or something and therefor the conditional check might be 'passing' when it shouldn't and besides it seems all we care about is if the # reviews is >0 so try this:
=LET(filterBy, RESULTS!A2:C2,
resultTable, RESULTS!A5:E12,
columnCounts, {5,6,5,5,5,7,6,7},
report, LAMBDA(result_filter,result_row,table_all, LET(table_data, DROP(table_all,1),
table_matches, (CHOOSECOLS(table_data,3)=INDEX(result_filter,1))*
(CHOOSECOLS(table_data,2)>=INDEX(result_filter,2))*
(CHOOSECOLS(table_data,2)<=INDEX(result_filter,3)),
IF(N(INDEX(result_row,2)),
VSTACK("*** "&INDEX(result_row,1)&" ***",
TAKE(table_all,1),
FILTER(table_data, table_matches, "")),
""))),
total, REDUCE("",SEQUENCE(ROWS(resultTable)),LAMBDA(p,q,VSTACK(p,
LET(tbl, INDIRECT("tbl"&TEXTJOIN(,,TEXTSPLIT(INDEX(resultTable,q,1),{" ","-"}))&"[#ALL]"),
report(filterBy, CHOOSEROWS(resultTable,q), CHOOSECOLS(tbl,SEQUENCE(,INDEX(columnCounts,q)),SEQUENCE(,2,COLUMNS(tbl)-1))))
))),
IFNA(IF(total=0, "", total),""))and not to beg for likes but feel free to give me some if you like
Works like a charm!!! If it's really easy to do, is there a way to make the results appear at the top of the worksheet even when the there are no reviews for the blank ones. Right now they are just appearing as blank. So, if there were no reviews for the first 3 categories, for instance, there are several blank lines at the top where they would have been had there been reviews to count.
If not, I can certainly live with it as is. Does it help to choose one of these as a 'Solution'? Not sure if I can mark more than one. So, is the last one the best or can I mark more than one? :)
Oh my gosh! I didn't even think about giving likes. I appreciate the ask. I'll be sure to go back through and do that. You've been awesome! Others have too. This forum has been a BIG help to me for quite a while now.
Right, I don't need a report for the reviews marked as 'Met' nor for the ones where zero reviews were done.
I'll give this a go and let you know. Thanks again.