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....
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.
