Quote:
Originally Posted by ArviLaanemets
Nothing like this is happening for me! Changed EID's (tested it with both Tables), changed leave dates, added empty and non-empty rows, etc. All changes (which must to change the Report Table) were shown immediately. Btw., I did all this on sheets LeaveRep and Report!
Only thing I did find that must be changed is for case the same date has response for more than 1 row (leave entries are overlapping). When this happens, either an error is returned, or in worst case the code from wrong row on LeaveRep sheet is returned. So the formula for all date columns on Report sheet must be like:
Code:
=IF(COUNTIFS(Leave_ReportX[EID];[@EID];Leave_ReportX[BeginDate];"<=" & INDEX(nDates;COLUMN()-2);Leave_ReportX[EndDate];">=" & INDEX(nDates;COLUMN()-2))>1;"overlapping";
IF(SUMIFS(Leave_ReportX[RowNo];Leave_ReportX[EID];[@EID];Leave_ReportX[BeginDate];"<=" & INDEX(nDates;COLUMN()-2);Leave_ReportX[EndDate];">=" & INDEX(nDates;COLUMN()-2))=0;"";
INDEX(Leave_ReportX[C_ode];SUMIFS(Leave_ReportX[RowNo];Leave_ReportX[EID];[@EID];Leave_ReportX[BeginDate];"<=" & INDEX(nDates;COLUMN()-2);Leave_ReportX[EndDate];">=" & INDEX(nDates;COLUMN()-2)))))
You having a polish name, I didn't edit the formula and left semicolons as parameter separators. In case I was wrong, replace them with commas before copying the formula into table columns.
NB! As this site interpreted field name Code put in brackets as the code tag in posting, I had to replace the Code with C_ode. Before using the formula, correct this!
The formula is somewhat long, so in case you want to use it, define some Dynamic Names (activate the top datarange cell in Date01 column of Report Table before defining them) and use them there. Like
Code:
nOverLap = COUNTIFS(Leave_ReportX[EID];[@EID];Leave_ReportX[BeginDate];"<=" & INDEX(nDates;COLUMN()-2);Leave_ReportX[EndDate];">=" & INDEX(nDates;COLUMN()-2))>1
nEmpty = SUMIFS(Leave_ReportX[RowNo];Leave_ReportX[EID];[@EID];Leave_ReportX[BeginDate];"<=" & INDEX(nDates;COLUMN()-2);Leave_ReportX[EndDate];">=" & INDEX(nDates;COLUMN()-2))=0
nRepCode = INDEX(Leave_ReportX[C_ode];SUMIFS(Leave_ReportX[RowNo];Leave_ReportX[EID];[@EID];Leave_ReportX[BeginDate];"<=" & INDEX(nDates;COLUMN()-2);Leave_ReportX[EndDate];">=" & INDEX(nDates;COLUMN()-2)))
And the formula will then be like:
=IF(nOverlap;"overlapping",IF(nEmpty;"";nRepCode))
|
Thank you for your help!!
I see that I can make changes to the report when working on Mac but results are disappearing on Windows PC - not sure what is happening there but I can do this on my personal computer.
Although I really like the idea of report telling me when someone has couple of different leaves approved over the same period, I couldn't make the formula work for me. Made all the changes (code and commas) but still excel is asking me whether I am trying to write formula. My brain is definitely built-in with anty-excel functions
Yes, originally I'm form Poland
12-12-2023, 01:32 AM
Similar Threads
Vlookup or Index/Match - Multiple Criteria
Mac OS X 
Office 2016 for Mac
Hybrid Mode