Always validate the business requirements

George got in touch the other day, in responding to an earlier post on date highlighting. He asked whether there was a way for Excel to automatically insert a row on the left-hand side of a range for each day that passed. So the days would run in reverse order, ascending from right to left. He needed this to allow people to enter hours worked in that day's column.

The key seemed to be the ability to see some other columns while entering the data, columns that would remain static n position at the left-hand side of the spreadsheet. (These columns were the number of hours worked in the last seven days, and the remaining hours to ensure that the worker didn't flout some form of working time directive.)

Now inserting a new column for each day is quite hard. It involves macros and you'd need logic to figure out how many days have elapsed since the spreadsheet was last opened. Indeed, Steve responded (thanks, Steve)

Instead, I suggested using the acres of space available off to the right of the spreadsheet, columns that already exist. And as for ensuring that the two static columns are always visible to the person entering the data, this can easily be achieved with Freeze Panes.

George seemed happy with the response.

You are truly the Excel wizard

In the end, the solution was way simpler than it would have been if the original requirements hadn't been questioned.

Happy days indeed.

• Tweet