Excel Function -Help Please!!
 

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

Excel Function -Help Please!!
 

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 Liengme
www.stfx.ca/people/bliengme
remove caps from email

wrote in message
ups.com...
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

Excel Function -Help Please!!
 

=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")))

" wrote:

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

Excel Function -Help Please!!
 

On May 1, 11:28 am, "Bernard Liengme"
wrote:
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

wrote in message

ups.com...



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- Hide quoted text -

- 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

Excel Function -Help Please!!
 

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)

Ranges"!$G$7,1,0

"bj" wrote:

=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")))

" wrote:

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

Excel Function -Help Please!!
 

On May 1, 12:57 pm, bj wrote:
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)



Ranges"!$G$7,1,0
"bj" wrote:
=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")))

" wrote:

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- Hide quoted text -

- 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?

Excel Function -Help Please!!
 

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

wrote in message
ps.com...
On May 1, 11:28 am, "Bernard Liengme"
wrote:
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

wrote in message

ups.com...



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- Hide quoted text -

- 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

Excel Function -Help Please!!
 

" wrote:

On May 1, 12:57 pm, bj wrote:
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)



Ranges"!$G$7,1,0
"bj" wrote:
=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")))

" wrote:

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- Hide quoted text -

- 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?


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)

Excel Function -Help Please!!
 

On May 2, 2:08 pm, bj wrote:
" wrote:
On May 1, 12:57 pm, bj wrote:
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)

Ranges"!$G$7,1,0
"bj" wrote:
=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")))

" wrote:

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- Hide quoted text -

- 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?

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)