If AND query
 

Hi guys, I am trying to establish if I can use the AND function with IF in
the following formula. The formula below retuns a row number based on the
criteria B6 in current sheet ( Sheet 2 ) if it matches the data in cell C4 of
Sheet 1, I would like the formula to return the same data ( row number )
based in C4 of Sheet 1 and E4 of Sheet 1 ,,,, I've tried adding an AND
statment but couldn't get it to work.




=IF(B6="","",IF(B6=Sheet1!$C$4,ROW(),""))

If AND query
 

John,

I'm not sure I understand what you mean but maybe this

=IF(B6="","",IF(B6=Sheet1!$C$4,ROW()&" "&ROW(Sheet1!$C$4),""))

Mike

"John Moore" wrote:

Hi guys, I am trying to establish if I can use the AND function with IF in
the following formula. The formula below retuns a row number based on the
criteria B6 in current sheet ( Sheet 2 ) if it matches the data in cell C4 of
Sheet 1, I would like the formula to return the same data ( row number )
based in C4 of Sheet 1 and E4 of Sheet 1 ,,,, I've tried adding an AND
statment but couldn't get it to work.




=IF(B6="","",IF(B6=Sheet1!$C$4,ROW(),""))

If AND query
 

Hi, not quite what I am looking for ,,,, I need the data to look something
like this ...
formula being in Sheet2

=IF(B6="","",IF(B6=Sheet1!$C$4,AND(E6=Sheet1!$E$4, (ROW(),""))

so that it returns the row number in Sheet 2 based on the criteria in cells
C4 and E4 in Sheet 1 ......

Sheet 1 cell C4 contains name, cell E4 contains type ,,, in Sheet 2 column B
contains numerous entries with various names, column E contains various
types, so the formula would show only the row numbers that meet the two
criteria ,,, e.g. row 6, 10, 15, 25 etc etc etc


"Mike H" wrote:

John,

I'm not sure I understand what you mean but maybe this

=IF(B6="","",IF(B6=Sheet1!$C$4,ROW()&" "&ROW(Sheet1!$C$4),""))

Mike

"John Moore" wrote:

Hi guys, I am trying to establish if I can use the AND function with IF in
the following formula. The formula below retuns a row number based on the
criteria B6 in current sheet ( Sheet 2 ) if it matches the data in cell C4 of
Sheet 1, I would like the formula to return the same data ( row number )
based in C4 of Sheet 1 and E4 of Sheet 1 ,,,, I've tried adding an AND
statment but couldn't get it to work.




=IF(B6="","",IF(B6=Sheet1!$C$4,ROW(),""))

If AND query
 

John,

This is the correct syntax for the AND

=IF(B6="","",IF(AND(B6=Sheet1!$C$4,E6=Sheet1!$E$4) ,ROW(),""))

Mike

"John Moore" wrote:

Hi, not quite what I am looking for ,,,, I need the data to look something
like this ...
formula being in Sheet2

=IF(B6="","",IF(B6=Sheet1!$C$4,AND(E6=Sheet1!$E$4, (ROW(),""))

so that it returns the row number in Sheet 2 based on the criteria in cells
C4 and E4 in Sheet 1 ......

Sheet 1 cell C4 contains name, cell E4 contains type ,,, in Sheet 2 column B
contains numerous entries with various names, column E contains various
types, so the formula would show only the row numbers that meet the two
criteria ,,, e.g. row 6, 10, 15, 25 etc etc etc


"Mike H" wrote:

John,

I'm not sure I understand what you mean but maybe this

=IF(B6="","",IF(B6=Sheet1!$C$4,ROW()&" "&ROW(Sheet1!$C$4),""))

Mike

"John Moore" wrote:

Hi guys, I am trying to establish if I can use the AND function with IF in
the following formula. The formula below retuns a row number based on the
criteria B6 in current sheet ( Sheet 2 ) if it matches the data in cell C4 of
Sheet 1, I would like the formula to return the same data ( row number )
based in C4 of Sheet 1 and E4 of Sheet 1 ,,,, I've tried adding an AND
statment but couldn't get it to work.




=IF(B6="","",IF(B6=Sheet1!$C$4,ROW(),""))

If AND query
 

Hi Mike ,, works a treat, thanks for that ,,, the second part of my probelm
is to then have a formula in Sheet1 that returns data in Sheet2 based on the
two criteria ( cells C4 and E4 in Sheet 1 ). let me see if I can map this out.

In Sheet 1 I have name in C4 and type in E4. In Sheet 2 column A now shows
which row(s) contain the info I want to see based on these two criteria (
thanks to your solution ), now I want to have a formula in Sheet1, say
B8:B100, that shows every part number that matches the two criteria .... is
this possible?

"Mike H" wrote:

John,

This is the correct syntax for the AND

=IF(B6="","",IF(AND(B6=Sheet1!$C$4,E6=Sheet1!$E$4) ,ROW(),""))

Mike

"John Moore" wrote:

Hi, not quite what I am looking for ,,,, I need the data to look something
like this ...
formula being in Sheet2

=IF(B6="","",IF(B6=Sheet1!$C$4,AND(E6=Sheet1!$E$4, (ROW(),""))

so that it returns the row number in Sheet 2 based on the criteria in cells
C4 and E4 in Sheet 1 ......

Sheet 1 cell C4 contains name, cell E4 contains type ,,, in Sheet 2 column B
contains numerous entries with various names, column E contains various
types, so the formula would show only the row numbers that meet the two
criteria ,,, e.g. row 6, 10, 15, 25 etc etc etc


"Mike H" wrote:

John,

I'm not sure I understand what you mean but maybe this

=IF(B6="","",IF(B6=Sheet1!$C$4,ROW()&" "&ROW(Sheet1!$C$4),""))

Mike

"John Moore" wrote:

Hi guys, I am trying to establish if I can use the AND function with IF in
the following formula. The formula below retuns a row number based on the
criteria B6 in current sheet ( Sheet 2 ) if it matches the data in cell C4 of
Sheet 1, I would like the formula to return the same data ( row number )
based in C4 of Sheet 1 and E4 of Sheet 1 ,,,, I've tried adding an AND
statment but couldn't get it to work.




=IF(B6="","",IF(B6=Sheet1!$C$4,ROW(),""))

If AND query
 

It's ok , I managed to play about with another formula and have the results I
need, thanks for your help Mike, appreciate it.

"John Moore" wrote:

Hi Mike ,, works a treat, thanks for that ,,, the second part of my probelm
is to then have a formula in Sheet1 that returns data in Sheet2 based on the
two criteria ( cells C4 and E4 in Sheet 1 ). let me see if I can map this out.

In Sheet 1 I have name in C4 and type in E4. In Sheet 2 column A now shows
which row(s) contain the info I want to see based on these two criteria (
thanks to your solution ), now I want to have a formula in Sheet1, say
B8:B100, that shows every part number that matches the two criteria .... is
this possible?

"Mike H" wrote:

John,

This is the correct syntax for the AND

=IF(B6="","",IF(AND(B6=Sheet1!$C$4,E6=Sheet1!$E$4) ,ROW(),""))

Mike

"John Moore" wrote:

Hi, not quite what I am looking for ,,,, I need the data to look something
like this ...
formula being in Sheet2

=IF(B6="","",IF(B6=Sheet1!$C$4,AND(E6=Sheet1!$E$4, (ROW(),""))

so that it returns the row number in Sheet 2 based on the criteria in cells
C4 and E4 in Sheet 1 ......

Sheet 1 cell C4 contains name, cell E4 contains type ,,, in Sheet 2 column B
contains numerous entries with various names, column E contains various
types, so the formula would show only the row numbers that meet the two
criteria ,,, e.g. row 6, 10, 15, 25 etc etc etc


"Mike H" wrote:

John,

I'm not sure I understand what you mean but maybe this

=IF(B6="","",IF(B6=Sheet1!$C$4,ROW()&" "&ROW(Sheet1!$C$4),""))

Mike

"John Moore" wrote:

Hi guys, I am trying to establish if I can use the AND function with IF in
the following formula. The formula below retuns a row number based on the
criteria B6 in current sheet ( Sheet 2 ) if it matches the data in cell C4 of
Sheet 1, I would like the formula to return the same data ( row number )
based in C4 of Sheet 1 and E4 of Sheet 1 ,,,, I've tried adding an AND
statment but couldn't get it to work.




=IF(B6="","",IF(B6=Sheet1!$C$4,ROW(),""))