Forum Discussion

Marcus_Booth's avatar
Marcus_Booth
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...
excel
Excel for web
Formulas and Functions
Macros and VBA
office 365
  • m_tarler's avatar
    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's avatar
Marcus_Booth
Iron Contributor
Jun 20, 2025

I was able to copy and paste from here into the formula bar with no issues. However, the formula isn't displaying any results. Not sure what's happening with it. Do you get any results in the spreadsheet I uploaded? 

m_tarler's avatar
m_tarler
Bronze Contributor
Jun 20, 2025

yes it looks like this for me:

I also did a 'simplified' formula but you may not like the aesthetics of the report it makes:

=LET(combined, VSTACK({"-----","Claims","-----"},tblClaims[#All],
                      {"-----","MiscAssignments","-----"},tblMiscAssignments[#All],
                      {"-----","ProcessProtest","-----"},tblProcessProtest[#All],
                      {"-----","IDD","-----"},tblIDD[#All],
                      {"-----","ProcessAppeal","-----"},tblProcessAppeal[#All],
                      {"-----","DPU","-----"},tblDPU[#All],
                      {"-----","VetRRVDVOP","-----"},tblVetRRVDVOP[#All],
                      {"-----","RESEA","-----"},tblRESEA[#All]),
      total, FILTER(combined, (CHOOSECOLS(combined,3)=RESULTS!$A$2)*(CHOOSECOLS(combined,1)>=RESULTS!$B$2)*(CHOOSECOLS(combined,1)<=RESULTS!$C$2)
                              +(CHOOSECOLS(combined,1)="-----")+(CHOOSECOLS(combined,1)="Date of Review"), ""),
  IFERROR(IF(total=0, "", total),""))

EDIT: NVM this simplified version as it doesn't take into account the meets or exceeds expectations part.

  • Marcus_Booth's avatar
    Marcus_Booth
    Iron Contributor
    Jun 23, 2025

    I also tried the one from m_tarler. It behaved the same way...

    I copied the text from the formula bar and pasted into the first cell on the 'Report' tab I created in the Master spreadsheet. Not sure why, but the formula doesn't do anything and just displays like this on the shee.

Resources