Hello
Formula seems to work inconsistently mat be due to it being too big, or there is some conflict that arises in the dates????
Could someone help with the formula - this would be hugely appreciated.
See attached actual example.
Refer Er Revised CAPEX Sheet (Result Data)
Cell I4=IFERROR(INDEX(OFFSET('Ericsson PIP Date Input'!$C$2, MATCH($H4,'Ericsson PIP Date Input'!$C$3:$C$597,0), 1, 16,), MATCH(I$1, OFFSET('Ericsson PIP Date Input'!$C$2, MATCH($H4, 'Ericsson PIP Date Input'!$C$3:$C$597, 0), MATCH(MAX(OFFSET('Ericsson PIP Date Input'!$C$2, MATCH($H4, 'Ericsson PIP Date Input'!$C$3:$C$597,0)-1, , , 43)), OFFSET('Ericsson PIP Date Input'!$C$2,MATCH($H4, 'Ericsson PIP Date Input'!$C$3:$C$597,0)-1, , , 43), 0)-1, 16, ), 0)), "")
This formula is picking-up data from the source table in Ericsson PIP Date Input (Source Data)
What the formula is doing
It picks-up the characteristic in Column D of Source Data (example ADV) and returns it to the Result sheet in the correct date as defined by I$1 and for the right Purchase Order descriptor $H4. Each new set of revised dates are entered into the Table and these are the dates that push the characteristic into the right date in the result sheet.
Problem
It seems to work inconsistently. I cannot copy and paste formulas into the revised dates table (say Cell F21). The characteristics in the Results disappear. It should push the characteristics in Results into the most recent dates as defined by the most up to date set of dates with the Revision Date in the yellow row. Sometimes if I copy the dates across from left to right and then change the dates it works, but not always;
For example, Refer Row 5 in Er Revised CAPEX Sheet (Result Data). It is missing a number of the characteristics such as "CoReady" etc. This relates to the dates entered into Cell F21 in Ericsson PIP Date Input (Source Data).
I thought it was because the formula was not looking forward to the most recent set of dates, but I don't think this is the problem as I seemed to overcome it for one the examples.
Thanks in advance.
David
Bookmarks