I have a spreadsheet with about 5000 entries, and I'm trying to automate the updates for certain pieces of information.

I have two columns of text, representing a range of years, i.e., 2005-2009 and a second column representing the component years of that range, i.e., 2005, 2006, 2007, 2008, 2009. Another example is 2010-PRESENT (where PRESENT is entered annually, i.e., 2011). Below is what the data looks like (except no cell boxes):

2005-2009 2005, 2006, 2007, 2008, 2009
2005-2009 2005, 2006, 2007, 2008, 2009
2005-2009 2005, 2006, 2007, 2008, 2009
2010-PRESENT 2010, 2011
2010-PRESENT 2010, 2011
2010-PRESENT 2010, 2011

This information has all been entered manually.

I've been able to develop a logic test that handles reading to see if PRESENT is part of the date, and updating the component years accordingly. It is: =IF(RIGHT(D21,1)="T", CONCATENATE(D21,4),", ",CURRENT_YEAR))). CURRENT_YEAR is a named cell and assigned the value of 2011 for this model year.

For data which doesn't include PRESENT, it fails the logic test and returns a "FALSE" instead of leaving the existing data. I have not been able to develop anything that will keep/update the component years if I run the other formula on it.

Ideally, I would have a macro or something to read the first and ending years, then fill the cells in the second column with the component years.

I would appreciate your thoughts and ideas, so I can get out of manually doing these updates every year.

Thanks!

Al