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
As for #1, did you try to copy the TEXT in the formula bar and paste the TEXT into the other formula bar? I also presume the actual TABLE names are the exact same.
As for #2 it would seem you could just add that right into the formula. maybe something like this:
=LET(
total, IFERROR(VSTACK(
"Claims",
IF(OR(AND(RESULTS!E5<>"EXCEED", RESULTS!E5<>"NOT MET"), COUNTIFS(tblClaims[Rep Name], RESULTS!$A$2, tblClaims[Date of Review], ">="&RESULTS!$B$2, tblClaims[Date of Review], "<="&RESULTS!$C$2)=0), "",
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), ""))),
"","MiscAssignments",
IF(OR(AND(RESULTS!E6<>"EXCEED", RESULTS!E6<>"NOT MET"), COUNTIFS(tblMiscAssignments[Rep Name], RESULTS!$A$2, tblMiscAssignments[Date of Review], ">="&RESULTS!$B$2, tblMiscAssignments[Date of Review], "<="&RESULTS!$C$2)=0), "",
VSTACK( tblMiscAssignments[#Headers],
FILTER(tblMiscAssignments[#Data], (tblMiscAssignments[Rep Name]=RESULTS!$A$2)*(tblMiscAssignments[Date of Review]>=RESULTS!$B$2)*(tblMiscAssignments[Date of Review]<=RESULTS!$C$2), ""))),
"","ProcessProtest",
IF(OR(AND(RESULTS!E7<>"EXCEED", RESULTS!E7<>"NOT MET"), COUNTIFS(tblProcessProtest[Rep Name], RESULTS!$A$2, tblProcessProtest[Date of Review], ">="&RESULTS!$B$2, tblProcessProtest[Date of Review], "<="&RESULTS!$C$2)=0), "",
VSTACK( tblProcessProtest[#Headers],
FILTER(tblProcessProtest[#Data], (tblProcessProtest[Rep Name]=RESULTS!$A$2)*(tblProcessProtest[Date of Review]>=RESULTS!$B$2)*(tblProcessProtest[Date of Review]<=RESULTS!$C$2), ""))),
"","IDD",
IF(OR(AND(RESULTS!E8<>"EXCEED", RESULTS!E8<>"NOT MET"), COUNTIFS(tblIDD[Rep Name], RESULTS!$A$2, tblIDD[Date of Review], ">="&RESULTS!$B$2, tblIDD[Date of Review], "<="&RESULTS!$C$2)=0), "",
VSTACK( tblIDD[#Headers],
FILTER(tblIDD[#Data], (tblIDD[Rep Name]=RESULTS!$A$2)*(tblIDD[Date of Review]>=RESULTS!$B$2)*(tblIDD[Date of Review]<=RESULTS!$C$2), ""))),
"","ProcessAppeal",
IF(OR(AND(RESULTS!E9<>"EXCEED", RESULTS!E9<>"NOT MET"), COUNTIFS(tblProcessAppeal[Rep Name], RESULTS!$A$2, tblProcessAppeal[Date of Review], ">="&RESULTS!$B$2, tblProcessAppeal[Date of Review], "<="&RESULTS!$C$2)=0), "",
VSTACK( tblProcessAppeal[#Headers],
FILTER(tblProcessAppeal[#Data], (tblProcessAppeal[Rep Name]=RESULTS!$A$2)*(tblProcessAppeal[Date of Review]>=RESULTS!$B$2)*(tblProcessAppeal[Date of Review]<=RESULTS!$C$2), ""))),
"","DPU",
IF(OR(AND(RESULTS!E10<>"EXCEED", RESULTS!E10<>"NOT MET"), COUNTIFS(tblDPU[Rep Name], RESULTS!$A$2, tblDPU[Date of Review], ">="&RESULTS!$B$2, tblDPU[Date of Review], "<="&RESULTS!$C$2)=0), "",
VSTACK( tblDPU[#Headers],
FILTER(tblDPU[#Data], (tblDPU[Rep Name]=RESULTS!$A$2)*(tblDPU[Date of Review]>=RESULTS!$B$2)*(tblDPU[Date of Review]<=RESULTS!$C$2), ""))),
"","VetRRVDVOP",
IF(OR(AND(RESULTS!E11<>"EXCEED", RESULTS!E11<>"NOT MET"), COUNTIFS(tblVetRRVDVOP[Rep Name], RESULTS!$A$2, tblVetRRVDVOP[Date of Review], ">="&RESULTS!$B$2, tblVetRRVDVOP[Date of Review], "<="&RESULTS!$C$2)=0), "",
VSTACK( tblVetRRVDVOP[#Headers],
FILTER(tblVetRRVDVOP[#Data], (tblVetRRVDVOP[Rep Name]=RESULTS!$A$2)*(tblVetRRVDVOP[Date of Review]>=RESULTS!$B$2)*(tblVetRRVDVOP[Date of Review]<=RESULTS!$C$2), ""))),
"","RESEA",
IF(OR(AND(RESULTS!E12<>"EXCEED", RESULTS!E12<>"NOT MET"), COUNTIFS(tblRESEA[Rep Name], RESULTS!$A$2, tblRESEA[Date of Review], ">="&RESULTS!$B$2, tblRESEA[Date of Review], "<="&RESULTS!$C$2)=0), "",
VSTACK( tblRESEA[#Headers],
FILTER(tblRESEA[#Data], (tblRESEA[Rep Name]=RESULTS!$A$2)*(tblRESEA[Date of Review]>=RESULTS!$B$2)*(tblRESEA[Date of Review]<=RESULTS!$C$2), "")))
), ""),
IF(total=0, "", total))
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?
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.
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.
I like m_tarler​'s idea, but I would implement it slightly differently. See the attached version.
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....
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.
I originally did it that way or very similar and then changed it all up specifically to show the tabs that didn't have any results to make it clear those tabs had none.
That's a good way too - Marcus_Booth​ can decide which version he likes best.
