Forum Discussion
Help with Excel report creation for review results from multiple worksheets...
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
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.
I went through the code and realized that a wrong column was being referenced. The 'Date of Review' needed to be 'Month of Review' column instead. I changed them all manually, but now nothing is being displayed from the formula. The formula is not appearing anymore on the sheet. Now when I click out of the A1 where the formula is, nothing displays. I will try going through each section of the code as you instructed. I'll let you know what I find. Thanks a bunch!
m_tarler​ HansVogelaar​ Here is the redacted version again, but with the column header changes made. It seems to work just fine in this file, but I still can't get it to work with the full dataset. It's driving me nuts, but I think it may start with the second one that was returned for Rep003. When I highlight the code, for referencing tblClaims, I can see the colors on the table there. That isn't the case with the tblMiscAssignments section. I'm not good at finding code mistakes. Nothing stood out to me as looking wrong. Anyway, hope that helps some. Thank you.
I tried to highlight sections of code for analysis, but nothing is returning any results on the master spreadsheet still. Also, when I try to view the return for a single code on the one you sent, there is also no pop-up display of the results like what's showing on your example above. I'm not sure what's going on. I even made sure that I'm using files saved to my computer as opposed to the cloud in case that was the issue. Still no luck getting the formula to work in the different workbook. Maybe my Excel is messed up?