I have an array consisting of a list of 27 events (Column B) and dates
(Column A), together with other details (Columns C:F). The events recur at
more-or-less monthly intervals, and I wanted to extend the list to cover a
forthcoming period of, say, two years.
Doing a Copy and Paste Special (using VALUE) is an easy way of doing it, but
I thought Excel might offer an alternative which would allow me to edit the
copied cells, and to retain any changes for future months.
I therefore entered into cell A28 the function [=A1+30.5], which gave me an
approximate date and into B28 the function [=B1]. I then copy-dragged the two
columns down to cover the two-year period. So far, so good. Dragging the
formula from Column B to Columns C to F was also easy enough
The problem is that the list of events only 'more-or-less' recurs monthly.
In Month 2 and months after, I needed to change an occasional date, delete
one event and replace it by another , amend support details and so on. As
expected, these changed details were duly picked up in the following months'
entries.
The problems started when it was necessary to sort one month's entries by
date. Lots of unexpected things happened, with events being linked to wrong
dates, etc. The entries when sorted did not pick up the value of the text in
the cell but only its formula, which followed its source to the original
entry.
Is there any way of getting the application to treat text the same way as
numbers are treated then Value() is used?