If function - move to cell below

[nickharthan]

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

 

[shail]

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

 

[sajay]

Dear Nic!,
Hope you mean by
"=IF(AND(Sheet2!N6<0,Sheet2!B6="North"=TRUE),Sheet2!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

 

[Gord Dibben]

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

 

[nickharthan]

thanks,

i've followed Gords advice.

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