"IF" with no "false" action
 

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

"IF" with no "false" action
 

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

"IF" with no "false" action
 

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

"IF" with no "false" action
 

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

"IF" with no "false" action
 

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

"IF" with no "false" action
 

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)


"bleu808" wrote in
message ...

All,

Can anyone help with a variation if this earlier thread? This is
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,000
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 - i
want it to remain as Mike. I used this variation, since I am not
using time.

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

Thanks all!

Bleu


--
bleu808
------------------------------------------------------------------------
bleu808's Profile:
http://www.excelforum.com/member.php...fo&userid=9196
View this thread: http://www.excelforum.com/showthread...hreadid=487594