Quote:
Originally Posted by Agnieszka
@ArviLaanemets Do you know why the result table looses all the returns when I make any changes to LeaveRep table?
|
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))