Excel 2003 (IE 2008) formula question

[Leena2012]

I'm working in Excel 2003 (IE 2008). I want column A to change to zero only if column B says COMPLETED. If column B says PENDING or anything else, column A should remain the same. There are no other formulas in the spreadsheet. I appreciate any assistance on this. Thanks in advance!

 

[SkipVought]

hi,

What you expect, cannot be done with a formula in the same column as the original data.

It CAN, however be done in an adjacent empty column, like column C...
[tt]
C2: =if(b2="COMPLETED",0,A2)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Leena2012]

I tried that but the zero appears in the c column instead of changing the information in the A column. In other words, if column B ("Status") says pending, then column A ("Bin") would tell you which bin the merchandise is in. But when column B says completed, the merchandise would be in bin 0. If I use the formula the way you indicated above, then even when the status column shows completed, the bin column wouldn't change to bin 0.

 

[SkipVought]

What you expect, cannot be done with a formula in the same column as the original data.

If you need the data in column A to change, it must be done with a VBA procedure (macro).

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Leena2012]

Thank you, I will look into that.

 

[lionelhill]

Caveat caveat caveat: I really really don't recommend you do this, not at all, not never, but here are some VB-free suggestions.

(1) You say there are no other formulae on the worksheet. This suggests that you merely want the "0" as a blanking entry for things that are completed. If so, you can use conditional formatting not to replace your data with "0", but to change the font to white on a white background so it no longer shows up on printing. The underlying data, however, are unchanged. This is particularly awkward if you are exporting the data for use in another piece of software.

If you do go this way, a simple conditional format formula that will do the job is =(index(B:B, row())="COMPLETED"). There are probably simpler versions, but I can't get my head round how Excel handles relative and absolute addresses on copying and pasting conditional formats.

(2) If you ever upgrade, in Excel2007 onwards, IF your data in column A are numbers, you can go one step closer to your request and use a conditional number format of "0" (not 0), which will display the text "0" and not the value. But again, the underlying value is still there and remains unchanged.