Forum Discussion

Iron Contributor

Jun 20, 2025

Solved

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

Windows 11 MS Office 365 Cloud Accessed from Citrix desktop I've been working on this project for a while and have a tracking spreadsheet for several review categories, each on its own worksheet t...

061625-MSReturn-Staff Work Reviewed.xlsx81 KB

excel

Excel for web

Formulas and Functions

Macros and VBA

office 365

m_tarler

Jun 27, 2025

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

Marcus_Booth

Iron Contributor

Jun 25, 2025

I'll try and be as specific as possible. The spreadsheet that I uploaded most recently 'Updated..." worked both before and after I moved the date being used from the 'Date of Review' column to the 'Date Under Review' column. However, when I try to copy and paste the formula from there into the master spreadsheet which is named 'Staff Work Reviewed.xlsx', the formula either displays itself into the cells below or nothing at all, depending on how I do it.

I've tried the following methods:

1- select cell in dummy (Updated....xlsx) containing the formula > Ctrl+C. Then select cell in master workbook >format as General > F2 > Ctrl+V.

2- select cell with double-click > select all text in the formula bar > Ctrl+C. Then select cell in master via double-click > place cursor in the formula bar > Ctrl+V.

I've also tried using the 'Paste Special" function to only paste the formula.
I've double checked that the table names, column names and tab names all match in both workbooks.

The master is different from the dummy in that...

Rep001 and up are replaced with staff names. There are more names on the Master. They are also accessed via a drop-down list that sources from the 'LIST' tab.
There are many more records on each worksheet in the Master than in the dummy.
There are a few additional worksheets in the Master that do not require any calculations for the RESULTS tab or the Report tab we are trying to create.

So, it seems that the formula should be only referencing things that exist for both workbooks, but I'm terrible at deciphering the code. Pretty much stumped here.

Marcus_Booth

Iron Contributor

Jun 25, 2025

I just had another thought. Could it be the Staff Names? Rep001, Rep002 etc. are only one word, where the staff names are two words with a space between them.

Oh, and the Citrix is a cloud-based system. So, we use devices called thin clients in the office to access the server where everything is stored. When working from home, as I am today, we access the server through a Citrix Workstation app. I hope that isn't the problem as there is no other option.

m_tarler
Bronze Contributor
Jun 25, 2025
ok when you get blank cell after pasting have you tried to force a calculate? (Formula -> Calculation -> Calculate Now (F9) )
you should also try a much more simlified version of the formula. Maybe something like:
=VSTACK(tblClaims[#Headers], FILTER(tblClaims[#Data], (tblClaims[Rep Name]=RESULTS!$A$2)* (tblClaims[Date of Review]>=RESULTS!$B$2)* (tblClaims[Date of Review]<=RESULTS!$C$2) , "none") )
this should just reply the lines for the Claims table or "none" if there are none. If that still doesn't work then we make an even more simplified formula. If that does work then we can start adding to it.
- Marcus_Booth
  Iron Contributor
  Jun 25, 2025
  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!
  - m_tarler
    Bronze Contributor
    Jun 25, 2025
    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.
    Updated-062525-MSReturn062325-Staff Work Reviewed (1).xlsx85 KB