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
so you mentioned in your original post:
MS Office 365 Cloud
Accessed from Citrix desktop
having 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.
I just had another thought. Could it be the Staff Names? Rep001, Rep002 etc. are only one word, where the staff names are two words with a space between them.
Oh, and the Citrix is a cloud-based system. So, we use devices called thin clients in the office to access the server where everything is stored. When working from home, as I am today, we access the server through a Citrix Workstation app. I hope that isn't the problem as there is no other option.
ok when you get blank cell after pasting have you tried to force a calculate? (Formula -> Calculation -> Calculate Now (F9) )
you should also try a much more simlified version of the formula. Maybe something like:
=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) , "none") )this should just reply the lines for the Claims table or "none" if there are none. If that still doesn't work then we make an even more simplified formula. If that does work then we can start adding to it.
Cool. I will mess around with it and try forcing a calc. Never heard of that before. I'll get back with you on it ASAP.
I'm curious, is there a part of the formula that controls which columns are returned from each of the tables? I want to see if I can get the 'Report' to display only the columns I need to see.
For example, if I don't want to see any of the columns used to mark items Yes, No, or N/A. I probably need only 4 or 5 columns, depending on which review tab it is.
Anywho, Thanks a bunch!