Forum Discussion
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 tab. I have a results page that gives me the data I need. The table on the RESULTS tab contains the results for the reviews and marks them as either having 'Not Met', 'Met' or 'Exceeded' the goal for each category.
The final piece of the project is giving us problems. I received a great deal of help with it from @HansVodelaar here on the forum and he got very close to solving it. I've included the spreadsheet with the final attempted 'Result' tab.
Here is what I'm trying to do now...
Automatically generate a simple report
Based on the generated date on 'RESULTS' for the selected Rep
That lists all records,
Labeled by review category (see review tabs),
From the designated start date to designated end date,
Only for the categories with scores designating them as either 'Not Met' or 'Exceeding' the goal.
So far, the 'Report' tab works to list the records. However, there are a couple of problems with it.
- I can't seem to successfully copy the formula to the master workbook, which holds the same structure, but is not redacted.
- The returned data, as is, only separates the categories by a blank line, but does not identify to which review tabs (categories) the results belong.
Any help offered is greatly appreciated. Hans suggested that the solution may require use of Power Query. So, if anyone has an idea of how I can accomplish this report without having to go into each tab to filter, sort, format and print, it would be totally awesome!!
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
40 Replies
Ok. HansVogellar and m_tarler got me to the formula that works to do what I want, but I'm not able to paste it into the master workbook. The master workbook is named differently and contains more tabs than the redacted (included here) spreadsheet, but all of the table names and column headers are the same.
When I copy and paste the formula into a new worksheet on the master workbook, the formula either doesn't return anything at all, not even an error dialogue, or it just displays the formula itself in a range of cells. If anyone has an idea about why this might be happening, I would really appreciate any assistance. Thanks!
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 did copy and paste the text from the cell and from the formula bar, but I'm not sure how to paste it correctly. I tried pasting special : formula and just regular ctrl+V. Strange results each time. Everything is the same for both sheets except that I changed the names to Rep001, Rep002 etc. and the file names are not the same as the master spreadsheet.
I will attempt to use what you've offered and will let you know. Thank you!
Apparently, I don't know how to use the forum correctly. Having trouble finding where they are located. Anyway, the formula you guys made works perfect for what I need, except that I can't get it to work on the Master spreadsheet. Could it be that we are using different versions of Excel or something?
I'm going to be able to get by with excluding the columns that contain the Yes/No and N/A values. So that should eliminate any problems with fitting the data onto one sheet for printing. Yay!!
Now I just need help to figure out why it works in the attached dummy spreadsheet, but not in the master. Attaching the file again, just in case it couldn't be found where I put it yesterday. Thanks again everyone for all the help!!
