Paste link causing problems with IF formula

[ADE6]

Hi

I have the IF formula below that works ok,based on the values shown in column AB(122:126),the result would be "BUY".


The formula works if i just type in the text in column AB(122:126),however if i use the paste link to insert the values in column AB the formula stops working.

I know i could use a lookup table as an alternative the problem would be that there would be so many possible outcomes that would have to covered that the lookup table would take a long time to construct.




COLUMN AB

BUY
BUY
TRENDING
TRENDING
LONG TRADES ONLY



=IF(AND(AB122="BUY",AB123="BUY",AB124="TRENDING",AB125="TRENDING",AB126="LONG TRADES ONLY"),"BUY",IF(AND(AB122="SELL",AB123="SELL",AB124="TRENDING",AB125="TRENDING",AB126="SHORT TRADES ONLY"),"SELL","DON'T TRADE"))

To break it down there are three possible results:

=IF(AND
(AB122="BUY",AB123="BUY",AB124="TRENDING",AB125="TRENDING",AB126="LONG TRADES ONLY"),"BUY"

IF(AND(AB122="SELL",AB123="SELL",AB124="TRENDING",AB125="TRENDING",AB126="SHORT TRADES ONLY"),"SELL"

Any other combination ="DON'T TRADE"

Thanks for the ideas

Ade

 

[SkipVought]

Ade,

What paste link are you entering in AB 122:126???

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[ADE6]

Hi Skip

Three values are put into column AB using the paste link fom the current worksheet and two values from the AUTOCALC worksheet.


AB122 ='AUTO CALC'!$Q$101
AB123 =$Q$101
AB124 ='AUTO CALC'!$AD$5
AB125 =$AD$5
AB126 =$Z$1 COLUMN AB

Current values in column AB(paste linked from other cells)
AB122--BUY
AB123--BUY
AB124--TRENDING
AB125--TRENDING
AB126--LONG TRADES ONLY

 

[SkipVought]

are you absolutaly sure that the values in
[tt]
'AUTO CALC'!$Q$101
$Q$101
'AUTO CALC'!$AD$5
$AD$5
$Z$1 COLUMN AB
[/tt]
contain no LEADING or TRAILING BLANKS or anything else???

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[ADE6]

i will look into it Skip,thanks for the ideas

 

[SkipVought]

I ask you to check that because simply pasting links to AB 122:126 does not disable the formula.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244