excel - Fill remaining cell based on which column is entered -

i want able following:

     d       e                           f 1    p       q                           pf 2    100     =d2/f2*sin(acos(f2))        =d2/sqrt((d2^2+e2^2)) 3    200     =d3/f3*sin(acos(f3))        =d3/sqrt((d3^2+e3^2)) 4 5    p       q                           pf 6    100     =d6/f6*sin(acos(f6))        =d6/sqrt((d7^2+e6^2)) 7    200     =d7/f7*sin(acos(f7))        =d7/sqrt((d7^2+e7^2)) 

that is, if enter value in column f, value in column e should automatically filled in, , if enter value in column e, value in column f should automatically filled in.

but, don't want display #div/0!, before enter value.

objective:

1. check if column d contains number.
2. check if either column column e or f contains number
3. fill in remaining column

also, enter names in of rows, these must disregarded. know can drag equations down , achieve way, again, give me #div/0!.

is there clever way this? i'm using excel 2010.

• open vbe (visual basic editor) alt+f11
• right-click in project explorer , insert module
• paste below code

udf functions

function e()      dim d range, f range     set d = activecell.offset(0, -1): set f = activecell.offset(0, 1)      if len(d) = 0 or len(f) = 0 e = ""     if len(f) > 0         dim result double         result = evaluate("=" & d & "/" & f & "*sin(acos(" & f & "))")         e = result     end if end function  function f()     dim d range, e range     set d = activecell.offset(0, -2): set e = activecell.offset(0, -1)      if len(d) = 0 or len(e) = 0 f = ""     if len(e) > 0         dim result double         result = evaluate("=" & d & "/sqrt((" & d & "^2+" & e & "^2))")         f = result     end if end function 

• go spreadsheet , enter new formulas
• cell e2 =e() , cell f2 =f() drag formulas down

ended

update:

to automate insertion , calculation

in project explorer » right-click sheet1 » view code

insert below code

private sub worksheet_change(byval target range)     if target.column = 5         fillf target     elseif target.column = 6         fille target     end if end sub 

in module1 replace code have 1 below

option explicit  sub fillf(byval target range)      dim d range, e range, f range      set e = target     set d = e.offset(0, -1)     set f = e.offset(0, 1)      if not iserror(e)         if len(e) = 0             exit sub         elseif len(d) = 0             exit sub         elseif len(d) > 0 , len(f) = 0             f.formula = "=" & d & "/sqrt((" & d & "^2+" & e & "^2))"             exit sub         else             exit sub         end if     else         f.clearcontents     end if  end sub  sub fille(byval target range)      dim d range, e range, f range      set f = target     set e = f.offset(0, -1)     set d = f.offset(0, -2)      if not iserror(f)         if len(f) = 0             exit sub         elseif len(d) = 0             exit sub         elseif len(d) > 0 , len(e) = 0             e.formula = "=" & d & "/" & f & "*sin(acos(" & f & "))"             exit sub         else             exit sub         end if     else         e.clearcontents     end if  end sub 

now, go spreadsheet , enter number either in column e, or f other formula should calculated , result should appear in neighbouring cell.