Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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(),"")) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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(),"")) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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(),"")) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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(),"")) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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(),"")) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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(),"")) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert hard coded query criteria to Parameter Query | Excel Discussion (Misc queries) | |||
Excel 2007 / MS Query - editing existing query to another sheet | Excel Discussion (Misc queries) | |||
Importing Data via Web Query - Can values be passed to query? | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |