If formula 1

[HRISUser]

I have an excel file with multiple worksheets and I am trying to have condition which returns different cells based on different criteria being met.


This is the formla that I am trying to use:

=IF(B6<='Pricing'!C3,'Pricing'!C5) (IF(AND(B6>'Pricing '!C3, B6<='Pricing'!D3),'Pricing'!D5))

I get #VALUE! as the result. I tried both sets of Ifs alone, and they worked. The problem is when I combine them I get the #VALUE! result. Was wondering if someone knew how to fix this.

Any help is greatly appreciated.
Thanks!

 

[ThomasLafferty]

If I read your formula correctly, you need to remove the closing parenthesis at the end of the first IF and replace it with a comma: , . Your formula should look like this:

=IF(B6<='Pricing'!C3,'Pricing'!C5,IF(AND(B6<='Pricing'!C3,B10<='Pricing'!D3),'Pricing'!D5))

NOTE: the apostrophes surrounding the sheeet reference to Pricing (ie: 'Pricing') are stripped out when I use them in my workbook. Are you using local or global names?

Tom

 

[xlbo]

' ' around sheet names is ther ein case there are any invalid characters such as spaces

Pricing!A1 doesn't need ' ' but
'Pricing Sheet'!A1 does

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[HRISUser]

Hi All,
Thanks so much for your quick responses Tom's formula worked like a charm!
-----------------------------