need formula

  • Thread starter Thread starter politesse
  • Start date Start date
P

politesse

Column a has numbers 1 to 12, column B has names.

I want a formula to change number in column A to zero or blank when I delete
the name in column B

Thanks
 
Formulas cannot change values elsewhere

But you could try achieving the effect in this way
Assuming data in row1 down
Insert a new col B (the names will now be in col C)
Put in B1: =IF(OR(A1="",C1=""),"",A1)
Copy B1 down as far as required. Hide away col A.
 
Insert a new column at A and add the following formula

=+IF(LEN(TRIM(C1))=0,0,B1)

Column B now contains your list of Numbers
Column C now has your list of Names

The formula trims the spaces from the contents of Column C and determines
the length of the contents. If the result is 0 then the contents of Column
C are either null or spaces and Column A is populated with 0. Otherwise,
Column C contains data and the formula populates Column A with the contents
of Column B.

QED
 
You can achieve this using a combination of IF and ISBLANK as follows:


A B
1 =IF(ISBLANK(B1),"",1) Tom
2 =IF(ISBLANK(B2),"",2) Dick
3 =IF(ISBLANK(B3),"",3) Harry
4 =IF(ISBLANK(B4),"",4) George
5 =IF(ISBLANK(B5),"",5) Fred

Where TOm, Dick and Harry etc are in column B

Formula says IF B1 ISBLANK leave A1 blank, otherwise insert 1

Hope that helps
 
If my understanding is correct

A B
1 Name1
2 Name2
3 Name3
.. Name.
.. Name.
12 Name12

then
=if(b1="","",1) put this in cell A1
=if(b2="","",2) put this in cell A2
..
..
..
=if(b12="","",12) put this in cell A12
 
paste this formula =if(b1="","",b1) in first column first cell copy this to
all cells in column A.
 
Back
Top