Returning a Figure from Adjacent Cells

[ericarenzo]

I have 8 columns in my excel grid (CJ - CQ), each showing the number of people enrolled each year (2001 - 2008). Ideally I would have a number of enrollees in column 2008 (most recent enrollment) for each line, however some lines do not have a figure in the 2008 column. I would like to create a formula that will look in the 2008 column first (CQ), and if there is a figure there return that figure. If there is no figure in the 2008 column, I would like it to look in the 2007 column (CP) and return that figure, and so on back to the 2001 column (CJ) if necessary. Is this possible?

 

[Expiry]

I'm sure someone will provide a neater way of achieving this, but this works if your blank cells are really blank. if they have 0s in them, change the ""s to 0s in the formula.


=IF(CQ2="",IF(CP2="",IF(CO2="",IF(CN2="",IF(CM2="",IF(CL2="",IF(CK2="",CJ2,CK2),CL2),CM2),CN2),CO2),CP2),CQ2)

 

[ericarenzo]

Brilliant! Thank you SO much!!