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

I followed the instructions above to move the 'Report (2)' tab to the master workbook. As you can see, it failed to pick up the data from the master workbook. In fact, it continues to display the same results as if it were still in the dummy workbook. See the highlighted. I also marked the extra tabs at the bottom to show that it's the master and not the dummy. I even completely closed the dummy and try to run 'Calculate Now' to no avail. 

Also, here is the version information for my Excel in case it might help...

if you don't commit the formula into that cell (i.e. hit enter, tab, checkmark, etc...) then it isn't officially there yet and calculate will treat that cell as blank.

That said, when you click on the cell you "see an outline" indicates there is an array of data coming out.  How big is that rectangle (rows and columns)?  If it is just like 8 rows and 1 columns it might just be the blank responses from each of the table checks.  if it is bigger then are you sure the text isn't white or some weird conditional formatting turned on?  

What if you right click on the tab in the 'dummy' book and select 'move or copy...' and then in the first option box change the "To book" to be the real book and then in the second box you can pick where in the book to put it and then check the checkbox "Create a copy".

I discovered that if I post the formula and leave the cursor at the end of the formula in the fx: bar while choosing the 'Calculate Now' button, the formula completely disappears. However, if I click out of the fx: bar and into the cell before calculating, the formula stays but does nothing.  I did a document inspection and it didn't find anything except for 2 hidden columns. When I went back after doing the check, I could see an outline of where the data should have been displayed, but the area inside was blank with no grid lines inside. So, something is working but the data isn't showing. 

11:35 am update - I deleted the 'Claims' tab in the dummy workbook and tried moving the same tab from the master over to it. The formula stopped displaying anything and the drop-down menus (data-validation) were removed from all of the cells.  

Good morning! I transferred the formula to the master workbook and specified the columns to be displayed. However, this is what I got...

Formula is in A1. When I first posted the formula and chose 'Calculate Now', the formula completely disappeared. I repeated it again and it pasted ok, but the 'Calculate Now' didn't have any effect. Nothing happened that I could see. Craziness!  It looks great in the 'dummy' workbook. I'm at a total loss. 

yes, the very first part of the FILTER will determine which data rows to return:

FILTER(tblClaims[#Data],...

you could 

FILTER(CHOOSECOLS(tblClaims[#Data],1,2,5,6,8,9),...

note: the numbers I picked were arbitrary/random you will need to pick the correct columns.

That said, just above that we stack the HEADER above it and you would want to do the same to that.

I actually took some time to redo the whole equation and make use of a LAMBDA to clean it up (at least IMHO):

=LET(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),"",
                                VSTACK(INDEX(result_row,1), TAKE(table_all,1), FILTER(table_data, table_matches, ""))))),
   filterBy, RESULTS!A2:C2,
   total, IFERROR(VSTACK(report(filterBy, RESULTS!A5:E5, CHOOSECOLS(tblClaims[#All],1,2,3,4,5,14)),
                        report(filterBy, RESULTS!A6:E6, CHOOSECOLS(tblMiscAssignments[#All],1,2,3,4,5,6,11)),
                        report(filterBy, RESULTS!A7:E7, CHOOSECOLS(tblProcessProtest[#All],1,2,3,4,5,12)),
                        report(filterBy, RESULTS!A8:E8, CHOOSECOLS(tblIDD[#All],1,2,3,4,5,8)),
                        report(filterBy, RESULTS!A9:E9, CHOOSECOLS(tblProcessAppeal[#All],1,2,3,4,5,12)),
                        report(filterBy, RESULTS!A10:E10, CHOOSECOLS(tblDPU[#All],1,2,3,4,5,6,7,13)),
                        report(filterBy, RESULTS!A11:E11, CHOOSECOLS(tblVetRRVDVOP[#All],1,2,3,4,5,6,15)),
                        report(filterBy, RESULTS!A12:E12, CHOOSECOLS(tblRESEA[#All],1,2,3,4,5,6,7,18))  ),
                ""),
  IF(total=0, "", total))

so now each table/row of the results is passed to the LAMBDA so you just need to tweak those rows 6-13.  I did a first guess at which are the right columns you want in the CHOOSECOLS but I hope you can get the idea and tweak this a little easier.

and who knows maybe when you paste this version it will work.

Cool. I will mess around with it and try forcing a calc. Never heard of that before. I'll get back with you on it ASAP. 

I'm curious, is there a part of the formula that controls which columns are returned from each of the tables? I want to see if I can get the 'Report' to display only the columns I need to see. 

For example, if I don't want to see any of the columns used to mark items Yes, No, or N/A. I probably need only 4 or 5 columns, depending on which review tab it is.

Anywho, Thanks a bunch!