"IF" with no "false" action

F

Fred

I have a spreadsheet (a home grown estimating template) that I want to
put a reference number in, based on the date/time, so am using =Now().
However each time the sheet re-calculates, the reference updates. I
tried using an IF to check on the value of another cell being 0 or 1
and wanted to put in a NOP (assembler term for No Operation) as the
False result to leave the contents unchanged after being set.

=IF(A1=0,Now(),NOP)

But there doesn't appear to be a NOP function within Excel.

Can anyone suggest an easy, non-VB (if possible) way round please.

Thanks in advance for any suggestions
Fred
 
F

Fred

Hi Roger,

Thanks for the suggestion, but that puts the characters NOP in the cell
(H4), whereas the first time through it is populated with the reference
number and there-after I dont want it to change. That formula
overwrites my reference # with NOP. Assembler had the useful facility
to do a compare and, if required carry out no action as one of the
results

If A1 = 0 Then
Now() puts in the date/time stamp as a number (acting as a reference
#)
Else NOP no action taken, leaves the contents of the reference number
cell as is.

But thanks for replying

Regards
Fred
 
B

Bill Martin

Fred said:
I have a spreadsheet (a home grown estimating template) that I want to
put a reference number in, based on the date/time, so am using =Now().
However each time the sheet re-calculates, the reference updates. I
tried using an IF to check on the value of another cell being 0 or 1
and wanted to put in a NOP (assembler term for No Operation) as the
False result to leave the contents unchanged after being set.

=IF(A1=0,Now(),NOP)

But there doesn't appear to be a NOP function within Excel.

Can anyone suggest an easy, non-VB (if possible) way round please.

Thanks in advance for any suggestions
Fred
Click to expand...

Here's one dirt simple approach assuming the cell you're trying to control is
B1, though someone may have a better method for you.

[B1] = if(a1=0,now(),B1)

If you enter this you'll immediately see an error message that you've got a
circular reference. Click "cancel" to tell Excel to ignore it for the moment.
At this point every time Excel recalculates you'll get the error message again.
You can also kill that by going into Tools>Options>Calculation and select the
"Iteration" box. With that selected Excel will no longer badger you about
circular references.

Not a clean solution, but simple.

Bill
 
B

Bob Phillips

Fred,

You could try this

Tools>Options>Calcualtion and check the Iteration box

Then use a formula of

=IF(A1=0,NOW(),B1)

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

bleu808

All,

Can anyone help with a variation if this earlier thread? This i
exactly what I am looking for, except I am not using a date function
I am using text.

so, I have few cells that look like this. (actually have about 8,00
cells worth of info on the spreadsheet.)

A
1 BOB
2
3
4
5 MIKE
6

So, what I am trying to is this -

a2 is blank, it should = a1 - thats easy... but - a5 is not blank -
want it to remain as Mike. I used this variation, since I am no
using time.

=IF(a5=" ",a4,NOP)

Thanks all!

Ble
 
B

Bob Phillips

You can't do that as the formula would overwrite the value, but you could do
this in an adjacent columns.

In B1 enter

=INDEX($A$1:$A$1000,MAX(IF($A$1:$A1<>"",ROW($A$1:$A1))))

as an array formula, so commit with Ctrl-Shift-Enter, and then copy down.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Top