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 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!!
Here is the most recently update spreadsheet without names.
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!!
so you mentioned in your original post:
MS Office 365 Cloud
Accessed from Citrix desktophaving a 365 account means this should work for you but a) make sure the excel you are running in is actually 365 (should be confirmed in File -> Account)
secondly this comment about "Accessed from Citrix desktop". I don't know what impact that might have.
You mention that "it works in the dummy spreadsheet" but by that do you mean when you open the sheets we sent you see the 'correct' output or that you are able to make changes and those formulas continue to work. i.e. if you excel doesn't recognize these equations then the values will stay until you make changes and tries to re-perform those calculations and then it will fail.
I opened the "Updated..." file you attached and it seems to be working just fine. Not sure what I need to see in there.
I'll try and be as specific as possible. The spreadsheet that I uploaded most recently 'Updated..." worked both before and after I moved the date being used from the 'Date of Review' column to the 'Date Under Review' column. However, when I try to copy and paste the formula from there into the master spreadsheet which is named 'Staff Work Reviewed.xlsx', the formula either displays itself into the cells below or nothing at all, depending on how I do it.
I've tried the following methods:
1- select cell in dummy (Updated....xlsx) containing the formula > Ctrl+C. Then select cell in master workbook >format as General > F2 > Ctrl+V.
2- select cell with double-click > select all text in the formula bar > Ctrl+C. Then select cell in master via double-click > place cursor in the formula bar > Ctrl+V.
I've also tried using the 'Paste Special" function to only paste the formula.
I've double checked that the table names, column names and tab names all match in both workbooks.The master is different from the dummy in that...
- Rep001 and up are replaced with staff names. There are more names on the Master. They are also accessed via a drop-down list that sources from the 'LIST' tab.
- There are many more records on each worksheet in the Master than in the dummy.
- There are a few additional worksheets in the Master that do not require any calculations for the RESULTS tab or the Report tab we are trying to create.
So, it seems that the formula should be only referencing things that exist for both workbooks, but I'm terrible at deciphering the code. Pretty much stumped here.