Help with Excel report creation for review results from multiple worksheets...

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.

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. 

here is an updated version with the last 2 columns:

=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(OR(AND(TAKE(result_row,,-1)<>"EXCEED", TAKE(result_row,,-1)<>"NOT MET"), SUM(table_matches)=0),TRANSPOSE(table_matches),
                                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),""))

I don't know what you mean by " just need error handling to move past the point where there are no reviews done"

I think we may have it! 

A5:E8 displays all of the tables for one of the Reps from 'Claim' through 'IDD'. However, A5:E9 displays the error in the cell and none of the tables. I tested it several ways and I think it is just need error handling to move past the point where there are no reviews done. Yay!! 

Also, I need the last 2 columns to display in the report "Pass/Fail" and "Notes:". Thanks! 

The lines that should be updated as needed are:

=LET(filterBy, RESULTS!A2:C2, 
       resultTable, RESULTS!A5:E12,
       columnCounts, {5,6,5,5,5,7,6,7},

So "RESULTS!A2:C2" needs to point to the Rep name and date range

"RESULTS!A5:E12" needs to point to the rows of data showing the table name and such

the last one "{5,6,5,5,5,7,6,7}" is how I'm filtering un-needed columns from each table.  Basically I noticed the columns you wanted were the first X columns and the very last column so this is a list of those first X columns that should be shown on the report.  For now, I'm not too worried about this value and we can tweak that later.

The fact that you are now getting a #VALUE! might or might not be progress.

So try going row by row so replace:

"RESULTS!A5:E12"

with

"RESULTS!A5:E5"

then try

"RESULTS!A6:E6"

then

"RESULTS!A7:E7"

and see if any of the individual tables work.

Ok. Pasted the formula. Now it just shows #VALUE! in the cell. I'm didn't understand your reference to the need to adjust the first 2 lines to point to the correct lines on the RESULTS tab. Are these the numbers to which you are referring (5,6,5,5,5,7,6,7)? If so, what are they supposed to be pointing to. I apologize for not being very code savvy. I do try to learn, but it's never ending! Lol! 

The RESULTS tab wasn't changed at all. Everything was already built on that tab when I sent it for help with the Report tab. All I did to make the dummy file was to change the staff names on the 'Validation Lists' tab to Rep001, 002 etc. and then remove the majority of the records from each review tab.

I'll try using this new code and let you know how it goes. Thanks! 

well it is good to see that something can be shown in the full book.  and you say the RESULTS page is working right?  

I just noticed your RESULTS tab uses INDIRECT() of the names in the REVIEW column for the table names.  Can you share that sheet?  it would seem if those equations work so should these.  Here is an updated version using INDIRECT to get the table names:

=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(OR(AND(TAKE(result_row,,-1)<>"EXCEED", TAKE(result_row,,-1)<>"NOT MET"), SUM(table_matches)=0),TRANSPOSE(table_matches),
                                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)),COLUMNS(tbl))))
                 ))),
    IFNA(IF(total=0, "", total),""))

in this formula you just need to make sure lines 1 and 2 are updated to point to the correct lines on the RESULTS tab