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)