Friday, May 13, 2011

yogi_If A Equals Y then C Equals B Else C Equals C

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
anonymous user said:
I've looked around a lot but having no luck finding a straight forward answer. I know Google Docs doesn't allow iterative circular references like Excel and OpenOffice Calc do (GRR) but I need to import a sheet that does just that.
The data is Currently like this:
Y D =IF(A1=Y;B1;C1)
The formula copies down the entire sheet. Basically the idea is to only update collumn C if collumn A is a certain value, otherwise leave the currently displayed data in Collumn C alone. However, in Google Docs I can't use that formula since it doesn't allow iteration.
I'm trying to find a way to do the same thing within the bounds of Google Docs but having no luck. Any help would be appreciated. Using additional hidden cells would be fine but I'm not sure how to do that either, everything I have come up with thus far winds up with a circular reference.
Any help would be appreciated.
Let us say the following image depicts the original data in columns A, B, and C

In the following solution I have used an additional column D ... column C can be optionally hidden.