Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all, I need to use the latest good Column value to fill the subsequence Column if their value is missing.
For instance, for the following Data table, the Cells( L2,C3), ( L2,C4), ( L2,C5) should assume (display) the value of the Cell (L2,C2) which contains the latest "good Value" of C2L2
L | C1 | C2 | C3 | C4 | C5 | C6 |
L1 | C1L1 | C2L1 | C3L1 | C4L1 | C5L1 | C6L1 |
L2 | C1L2 | C2L2 | C6L2 | |||
L3 | C1L3 | C2L3 | C3L3 | C4L3 | C5L3 | C6L3 |
L4 | C1L4 | C2L4 | C3L4 | C4L4 | C5L4 | C6L4 |
L5 | C1L5 | C4L5 | C5L5 | C6L5 | ||
L6 | C1L6 | C2L6 | C3L6 | C4L6 | C5L6 | C6L6 |
L7 | C1L7 | C2L7 | C3L7 | C4L7 | C5L7 | C6L7 |
L8 | C1L8 | C2L8 | C3L8 | C4L8 | C5L8 | C6L8 |
L9 | C1L9 | C2L9 | C3L9 | C4L9 | C5L9 | C6L9 |
L10 | C1L10 | C6L10 | ||||
L11 | C1L11 | C2L11 | C3L11 | C4L11 | C5L11 | C6L11 |
Due to the nature of data (Balance Financial Report) the information is displayed in a Pivot, of Balance Accounts (horizontally - 1st Dimension) over Months (Vertically- 2nd Dimension ) .
A simple solution would be to use the function Column() as in the expression:
If(IsNull(Data), Column(ColumNo()-1), Data)
but in a Pivot it doesn't work due to dimensionality used.
I've attached an example illustrating the problem.
Any suggestions?
Thanks in advance,
pmb
if(len(Data),Data,before(column(1)))
if(len(Data),Data,before(column(1)))
Great!
Thank you John & best regards,
Paulo