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

Hi Fred

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

Regards

Roger Govier


Fred wrote:
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

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

Fred,

You could try this

ToolsOptionsCalcualtion 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)


"Fred" wrote in message
oups.com...
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

Fred wrote:
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


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 ToolsOptionsCalculation and select the
"Iteration" box. With that selected Excel will no longer badger you about
circular references.

Not a clean solution, but simple.

Bill