I have a sheet with 3 columns.
In Column A I have a list of dates. These dates are pulled from another sheet in the same workbook with a formula. The formula is copied down the column. I want it to copy the original cell if it encounters a date there and to give an error if it doesn’t. For that I use this formula:
=IF(COUNTA($‘Income Log’.B2)=1,$‘Income Log’.B2,err)
In Column B I have a separate list of dates. These dates are pulled from a third sheet in the same workbook with basically the same method. The following formula is also copied down column B:
=IF(COUNTA($Expenses.A2)=1,$Expenses.A2,err)
In column C I use TOCOL to list all the dates in both other Columns, listing first all the dates in Column A, and then, right under, skipping all the errors, all the dates in Column B.
This is the formula:
=TOCOL(A2:B1048576,3,TRUE)
After hitting enter it turns into an Array formula like this:
{=TOCOL(A2:B1048576,3,1)}
So far so good.
When I add a date to either my “Income Log” or “Expenses” sheet, they appear in the correct place at the bottom of Columns A or B.
Again, so far so good.
However, Column C doesn’t update.
Refreshing doesn’t work either (not that having to manually refresh each time I add data would be the best solution anyway).
The only thing that seem to work is Deleting Column C and rewriting the TOCOL formula. Not a viable solution to have to do this each time I add data, especially taking into account that I have a Pivot table that pulls data from Column C and I’d then have to set that up each time also.