Autopopulating a cell

[Tralee6]

Hi,

My question is this: If cell a1 is entered with a name, how can I get
b2 to automatically fill with a number? I don't want b2 to fill unless
a name is entered into a1 and no matter what name is entered into a1, b2
will always have the same number. Would you use an "IF" statement?

 

[Vito]

Yes, an if statement should do it.

=If(A1<>"",number,""), where number is your actual number or a cell
reference containing the number to populate.

 

[Tralee6]

Hi Vito,

Thank you for the help. Unfortunately, its not working. When I enter
the formula, it does automatically populate b1 with the number I want
but I only want it to populate if someone enters a name in a1. If a1
is blank, then b1 should stay blank. So a1 is smith and then b1 should
change to 150. However, if a1 is blank then b1 should be blank. Is
that possible?

 

[Vito]

My formula states that if A1 is not blank then populate the number
otherwise leave it blank.

In B1 enter,

=If(A1<>"",number,"")

You can also, put it as =IF(A1="","",number)

Am I still missing something?

Does this name have to be specific, so that if Jackson is entered an
that is not a legal name, then you still want a blank

 

[Tralee6]

Perhaps I set it up wrong. I will try it again. Initially, a1 was
blank and when I went into b1 and typed in formula, it entered the 150.
Maybe a1 should have been a name, Smith, and then the formula entered
into b1? I'm guessing that once that's done, all I would have to do is
copy through the rest of the b1 column. There is no specific name, just
an entry into the a1 cell.

 

[Vito]

The formula is correct for what you are looking for.

Do you have your calculation set to manual. It should be automatic.

Go to Tools|Options and from the Calculation Tab, make sure the
Automatic radio button is selected in the Calculation area.

After this, do you still have problems with the formula?

 

[Tralee6]

THANK YOU! Its working perfectly now.

 

[Tralee6]

What if I needed the cell to show "0150". Is there a way to keep it a
a "number" cell and stop the "0" from disappearing

 

[Vito]

Oh! So you were set to Manual calculations?

Go to Format|Cells and from the Number tab choose Custom. Then in the
Type box enter for zeroes, 0000. The 0's will appear for all numbers
of 3 digits or less.