Can the IF() function loop through cells looking for a certain condition?

E

Eric

Let me first explain what I am trying to accomplish:

I have 2 sheets; one named "Jobs", and the other named "Requests".
Now on the "Jobs" sheet, each row contains a job, and the last column
of each row, I enter an "R", if that job was a request.

Here's an example of the "Jobs" sheet data:
-------------------------------------------------
Date Invoice# Customer Name Request?

05/19/04 543218 Larry R
05/19/04 987345 Moe
05/19/04 343529 Fred R
05/19/04 652434 Bubba
-------------------------------------------------

Here's an example of the "Requests" sheet data:
------------------------------------
Date Invoice# Customer Name

This is where I want to have an IF() function loop through the "Jobs"
sheet and when it finds an "R" in the "Requests?" column, I would like
it to enter the "Date", "Invoice#" and "Customer Name" columns
automatically, and keep going through the rows of jobs on the "Jobs"
sheet.
------------------------------------

The ideal situation is that the entire "Requests" sheet is filled in
automatically as I add/remove jobs on the "Jobs" sheet. So if I
entered the data mentioned above, then I would like to see the
following information automatically entered on the "Requests" sheet:
------------------------------------
Date Invoice# Customer Name

05/19/04 543218 Larry
05/19/04 343529 Fred
Because these were the jobs with an "R" in the "Request?" column of
the "Jobs" sheet.
 
H

Harlan Grove

Eric said:
I have 2 sheets; one named "Jobs", and the other named "Requests".
Now on the "Jobs" sheet, each row contains a job, and the last column
of each row, I enter an "R", if that job was a request.

Here's an example of the "Jobs" sheet data:
-------------------------------------------------
Date Invoice# Customer Name Request?

05/19/04 543218 Larry R
05/19/04 987345 Moe
05/19/04 343529 Fred R
05/19/04 652434 Bubba
-------------------------------------------------

Here's an example of the "Requests" sheet data:
------------------------------------
Date Invoice# Customer Name

This is where I want to have an IF() function loop through the "Jobs"
sheet and when it finds an "R" in the "Requests?" column, I would
like it to enter the "Date", "Invoice#" and "Customer Name" columns
automatically, and keep going through the rows of jobs on the "Jobs"
sheet.
------------------------------------

The ideal situation is that the entire "Requests" sheet is filled in
automatically as I add/remove jobs on the "Jobs" sheet. So if I
entered the data mentioned above, then I would like to see the
following information automatically entered on the "Requests" sheet:
------------------------------------
Date Invoice# Customer Name

05/19/04 543218 Larry
05/19/04 343529 Fred
Because these were the jobs with an "R" in the "Request?" column of
the "Jobs" sheet.
------------------------------------
Click to expand...
....
----+----1----+----1----+----1----+----1----+----1----+----1----+----1--
This sort of thing is best done using Advanced Filters, but they're not
as simple to use as they should be when multiple worksheets are
involved. And they're not automatic like formulas.

So a formula approach. I'll assume there's no blank line between your
column headings and the first job record in the Jobs table, and that it
spans 4 adjacent columns. I'll also assume you've named the Jobs table
JobsTbl. With the top row of your Requests table filled with column
headings matching the ones in JobsTbl that you want to pull and these
headings in Requests!A1:C1, select Requests!A2:C2 and enter the array
formula

=IF(COUNTIF(INDEX(JobsTbl,0,4),"R")>=ROW(A1),INDEX(JobsTbl,
SMALL(IF(INDEX(JobsTbl,0,4)="R",ROW(JobsTbl)),ROW(A1))
-CELL("Row",JobsTbl)+1,{1,2,3}),"")

This should fill in the first record. With this 3-cell range still selected,
fill it down as far as needed (into as many rows as there are in JobsTbl to
be assured you never miss a record).
 
F

Frank Kabel

Hi
though I really like Harlan's approach you may consider using a simple
Autofilter ('Data - Filter - Autofilter') on the first page to simply
show only th Jobs with an 'R' in the last column.
 
Top