Lost in Space

[i8ig]

Can you write a macro that places a formula in a cell that provides a value off another cell?

Example: If column 1 ="John" then column 3 "=if("column 14" is blank, leave blank, if it has a number in it, subtract 4 from it and place the value in column 3).

Column 1 needs to be able to accept at least 3 names and each name will have a different number to subtract from column 14 if it has a number in it.

I'm sure its simple, however the more I look at it the more lost I become!

 

[ravishankar]

Hi
C3 =IF(AND(A1="john",N1<>""),N1-4, "")
Ravi

 

[i8ig]

Thanks,
It looks like I would need to write this formula 3 times in the macro and change the name each time, is that correct?
Also these names will be entered multiple times on the sheet, so would I need to leave off the 'cell' designation?

 

[ravishankar]

Hi
I missed reading that you need a macro and gave you a formula. Try the following codes.

sub IITg
x= cells(rows.count,1).end(xlUp).row
for a = 1 to x
for b = 1 to 3
c = choose(b,"john", "Rob","Kim")
d = choose(b,4,7,10)
if cells(a,1) = c and cells(a,14) <> "" then
cells(a,3) = cells(a,14) - d
endif
next b
next a
End sub

Run the macro.
If col A has names john or rob or kim and col N is not blank it will subtract 4 or 7 or 10 from col N and places in col C.
Ravi

 

[Peter_SSs]

i8ig

If you are looking to put a formula in column 3 (ie column C), try a formula like the one below. Note that the three names in the formula must be in alphabetical order.
=IF(N3="","",N3-LOOKUP(A3,{"Alan","John","Sam"},{12,4,8}))