Hello!

I am unsing the following formula to pull in values from a spreadsheet.

=IF(AND(Sheet2!N6<0,Sheet2!B6="North"=TRUE),Sheet2 !A6,0)

i am getting a list of "0" values. how can i move to the next cell
below, only returning values for 'true' formulae.

cheers

nick

Hi Nick,

There is something wrong in your formula. In the part - AND :

AND(Sheet2!N6<0,Sheet2!B6="North"=TRUE)

You might not use TRUE here. This will work without using the word. So
the formula will be

=IF(AND(Sheet2!N6<0,Sheet2!B6="North"),Sheet2!A6,0 )

Now if the AND formula is "True" then "Sheet2!A6 will be the outcome,
otherwise "0" will appear.

In your case the AND formula has always the output of "False" hence it
shows you "0".

This is what I interpreted. I could be able to tell you exact when I
see your data.

Thanks, I hope this helped you.

Shail



wrote:
Hello!

I am unsing the following formula to pull in values from a spreadsheet.

=IF(AND(Sheet2!N6<0,Sheet2!B6="North"=TRUE),Sheet2 !A6,0)

i am getting a list of "0" values. how can i move to the next cell
below, only returning values for 'true' formulae.

cheers

nick

Dear Nic!,
Hope you mean by
"=IF(AND(Sheet2!N6<0,Sheet2!B6="North"=TRUE),Sheet 2!A6,0) "

Sheet2's Cell 'N6' has a value which is less than 0
AND
Sheet2's B6 has a text "North"

If your both conditions are true show the figure from A6


TRY THIS ONE

=IF(AND(Sheet2!N6<0,Sheet2!B6="North"),Sheet2!A6,0 )

if you don't want to see Zero values

Tools-Options-View-window Options-Zerovalues uncheck

YOurs,
sajay

On Dec 18, 4:30 pm, wrote:
Hello!

I am unsing the following formula to pull in values from a spreadsheet.

=IF(AND(Sheet2!N6<0,Sheet2!B6="North"=TRUE),Sheet2 !A6,0)

i am getting a list of "0" values. how can i move to the next cell
below, only returning values for 'true' formulae.

cheers

nick

Maybe just change the 0 to "" which will leave the cell looking blank.

=IF(AND(Sheet2!N6<0,Sheet2!B6="North"=TRUE),Sheet2 !A6,"")


Gord Dibben MS Excel MVP

On 18 Dec 2006 03:30:47 -0800, wrote:

Hello!

I am unsing the following formula to pull in values from a spreadsheet.

=IF(AND(Sheet2!N6<0,Sheet2!B6="North"=TRUE),Sheet 2!A6,0)

i am getting a list of "0" values. how can i move to the next cell
below, only returning values for 'true' formulae.

cheers

nick

thanks,

i've followed Gords advice.

nick

Gord Dibben wrote:
Maybe just change the 0 to "" which will leave the cell looking blank.

=IF(AND(Sheet2!N6<0,Sheet2!B6="North"=TRUE),Sheet2 !A6,"")


Gord Dibben MS Excel MVP

On 18 Dec 2006 03:30:47 -0800, wrote:

Hello!

I am unsing the following formula to pull in values from a spreadsheet.

=IF(AND(Sheet2!N6<0,Sheet2!B6="North"=TRUE),Sheet 2!A6,0)

i am getting a list of "0" values. how can i move to the next cell
below, only returning values for 'true' formulae.

cheers

nick