Excel Function -Help Please!!

[hvfr902]

hi,

i need some help with a complex excel function that may need to be a
macro but i am not the most proficent at them. what i need is below if
anyone can offer help

i need to check the data in D99 and then run a specific formula
depending on the data in D99

if d99 = 1960 then the forumula needs to be =IF(D7='Proficy CL Limits,
Ranges'!G7,1,0)

if d99 = 311 then the formula needs to be =IF(D7='Proficy CL Limits,
Ranges'!D7,1,0)

if d99 = 305 then the formula needs to be =IF(D7='Proficy CL Limits,
Ranges'!J7,1,0)

the formula must then run in d100 to d184

Any help would be greatly appreciated

 

[Bernard Liengme]

Try
=(D99=1960)*(D7=Sheet2!G7)+(D99=311)*(D7=Sheet2!D7)+(D99=305)*(D7=Sheet2!J7)
I'll let you fix the worksheet name!
Note: you will get 0 if D99 is none of required values and/or if D7 fits
none or the required values
best wishes

 

[bj]

=if($D$99=1960,if($D$7='Proficy CL Limits,
Ranges"!$G$7,1,0),if($D$99=311,if($D$7='Proficy CL Limits,
Ranges"!$D$7,1,0),if($D$99=305,if($D$7='Proficy CL Limits,
Ranges"!$J$7,1,0),"otherwise")))

 

[hvfr902]

Try
=(D99=1960)*(D7=Sheet2!G7)+(D99=311)*(D7=Sheet2!D7)+(D99=305)*(D7=Sheet2!J7­)
I'll let you fix the worksheet name!
Note: you will get 0 if D99 is none of required values and/or if D7 fits
none or the required values
best wishes
--
Bernard V Liengmewww.stfx.ca/people/bliengme
remove caps from email













- Show quoted text -

If see how you are having it check the value of D99 but if the value
of D99 = 1960 then the formula that must be in cell D100 should be
=IF(D7='Proficy CL Limits, Ranges'!G7,1,0)

That is the part i am stuck on

 

[bj]

I just noticed I put double quotes rather than single quotes in my equation

if D99 will only be 1960,311 or 305 you could also use

=if(D7=indirect("'Proficy CL Limits,
Ranges'!$"&lookup(D$D99,{305,311,1960;"J","D","G"})&"$7"),1,0)

 

[hvfr902]

I just noticed I put double quotes rather than single quotes in my equation

if D99 will only be 1960,311 or 305 you could also use

=if(D7=indirect("'Proficy CL Limits,
Ranges'!$"&lookup(D$D99,{305,311,1960;"J","D","G"})&"$7"),1,0)






- Show quoted text -

It is still not returning correctly... it seems a problem here


(D$D99,

if i change that to

D99 i get #N/A back in the field

any ideas?

 

[Bernard Liengme]

In the formula
=(D99=1960)*(D7='Proficy CL Limits Ranges'!G7)
+(D99=311)*(D7='Proficy CL Limits Ranges'!D7)
+(D99=305)*(D7='Proficy CL Limits Ranges'!J7­)
each term (bla-bla) will either be true of false; when you multiple
TRUE?FALSE in Excel they get treated as 1/0. SO each term can be considered
1 or 0 so if D99 does equal 1960 and D7 does equal what is in G7 on the
other sheet then we get

1*1 + 0*0 +0*0 which = 1

best wishes


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

Try
=(D99=1960)*(D7=Sheet2!G7)+(D99=311)*(D7=Sheet2!D7)+(D99=305)*(D7=Sheet2!J7­)
I'll let you fix the worksheet name!
Note: you will get 0 if D99 is none of required values and/or if D7 fits
none or the required values
best wishes
--
Bernard V Liengmewww.stfx.ca/people/bliengme
remove caps from email













- Show quoted text -

If see how you are having it check the value of D99 but if the value
of D99 = 1960 then the formula that must be in cell D100 should be
=IF(D7='Proficy CL Limits, Ranges'!G7,1,0)

That is the part i am stuck on

 

[bj]

It is still not returning correctly... it seems a problem here


(D$D99,

if i change that to

D99 i get #N/A back in the field

any ideas?

thats cause I had a second error

=if(D7=indirect("'Proficy CL Limits,
Ranges'!$"&lookup(D99,{305,311,1960},{"J","D","G"})&"$7"),1,0)

 

[hvfr902]

thats cause I had a second error

=if(D7=indirect("'Proficy CL Limits,
Ranges'!$"&lookup(D99,{305,311,1960},{"J","D","G"})&"$7"),1,0)- Hide quoted text -

- Show quoted text -

I am still getting #N/A with

=IF(D7=INDIRECT("'Proficy CL Limits,
Ranges'!$"&LOOKUP(D99,{305,311,1960},{"J","D","G"})&"$7"),1,0)