If function - move to cell below

N

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
 
S

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
 
S

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
 
G

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
 
N

nickharthan

thanks,

i've followed Gords advice.

nick

Gord said:
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
Click to expand...
 
Top