I thought this would be easy but I have been pulling my hair out over this one! I basically have a row of items (variable number of items) which show either a value or nothing (cell="") depending on requirements. As an example, imagine 15 rows A-O with a few blanks as per below:
A
B
D
E
F
J
K
N
O
Now what I would like is to have another column parse through these and remove the empty lines so that it would look like this. Because these are live cells which depend on others and have dependents, I cannot use a VBA sort/copy/paste values macro. I would ideally like to use formulas which would also update this "live". I have been trying to achieve this using offset (and quite a few hidden rows) but just cannot get my offset number to work reliably and for it to show the above like this:
Vert. Offset required to the right for info
A 0
B 0
D 1
E 1
F 1
J 4
K 4
N 6
O 6
Can anyone manage to make this work? The problem is being able to calculate the correct offset if there is more than 1-2 blank rows - in practice maybe all rows bar the last may be empty!
A
B
D
E
F
J
K
N
O
Now what I would like is to have another column parse through these and remove the empty lines so that it would look like this. Because these are live cells which depend on others and have dependents, I cannot use a VBA sort/copy/paste values macro. I would ideally like to use formulas which would also update this "live". I have been trying to achieve this using offset (and quite a few hidden rows) but just cannot get my offset number to work reliably and for it to show the above like this:
Vert. Offset required to the right for info
A 0
B 0
D 1
E 1
F 1
J 4
K 4
N 6
O 6
Can anyone manage to make this work? The problem is being able to calculate the correct offset if there is more than 1-2 blank rows - in practice maybe all rows bar the last may be empty!
