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 23, 2025

Thank you. I copied and pasted the text from the last one sent from Hans. Copied from the formula bar and pasted it into the first cell of the Master sheet, but the formula didn't return anything. It only displays the formula on the page like this....

 

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Jun 23, 2025

    that looks like you are pasting into the cell and not into the formula bar.

    you need to paste it into the formula bar as shown above (or if you hit F2 and go into edit mode inside a cell).

    In the above image the same formula that I pasted into the formula bar in E1 did NOT paste correctly when I just clicked on C1 and hit paste.

     

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

      Ok. I open both spreadsheets, copy the formula from the formula bar using Ctrl+C. Then I highlight the cell A1 on the master sheet and click into the formula bar, then Ctrl+V to paste. I have to close the sheet I'm pasting from or it tries to link the sheets together or something. Copying into one sheet does something to the original instead of actually copying. 

      Now, I'm wondering if it has anything to do with the fact that there are other worksheets on the master that were not included on the one I sent. No calculations are performed on the data contained there, so I didn't include in the redacted file. Other than that, I'm not sure what would be keeping it from working. I didn't change any of the table names or sheet names. 

      • m_tarler's avatar
        m_tarler
        Bronze Contributor
        Jun 23, 2025

        I don't understand what is happening.  copying from the formula bar and pasting into the formula bar should not matter if it is a different sheet or workbook or anything and shouldn't affect the other book.  (that said there is a bug in excel where it could paste the value onto the wrong sheet but that is different).

        Make sure the cell you are pasting into is formatted as GENERAL (at least not TEXT) before you paste into the cell.

        That said, best guess is that you are probably getting some errors and don't know it because that formula has the IFERROR( ....  , "") so all you see is the blank.   So next step is to start figuring out what part broke.  I might start by removing that IFERROR().  Also I would highlight lines in the formula to see if and what they output:

        in this image see how I expanded the formula bar (hover near the bottom of the bar until the cursor changes to arrows and drag down)

        Then I highlighted one of the FILTER statements and notice at the arrow at the top there is an output sneak peek that pops up.  NOTE that will only work for a fully defined part of the formula so if you highlight even 1 too many or too few ")" or the such it will NOT show an output.  But you can even hover over small things and check that each Table is correctly found.  e.g. highlight "tblVetRRVDVOP[#Data]" and make sure it shows some values.

Resources