3

Please help. I'm stumped! I'm not very familiar with Excel formulas.

My workbook is two pages. The first page is intended to be a reference page that is included in a morning report. The second page is a data collection page that we want our operators to fill out.

There are two criteria that should bring information from the data page to the reference page.

  1. Is the work complete? If the response is yes, the cell value would be Y and if the value is no, the cell value would be N. I only want to see rows that contain a cell value of N in a column labeled Work Completed.

  2. This criteria looks for values to identify a piece of equipment. I would like a formula to identify a piece of equipment, look to see if the work is completed and if not copy the entire row to the reference sheet.

Is this possible to do with a formula? Can anyone help?

1
  • 3
    Screenshots of the pages or at least samples of how it looks like would help. For instance, where would that copied row be inserted into the reference sheet? How do you identify which row should be copied? The first question is possible with Data Validation, and the second will index/match I would think.
    – Jerry
    Sep 6, 2013 at 21:50

1 Answer 1

2

I'm going to work off a few assumptions:

  1. There is a cell, A1, on your report sheet where someone can enter the piece of equipment they are interested in.
  2. There are two columns, A and B, in your 'data collection' page: The equipment name and the y/n for completeness
  3. For now you only want to see 'completed' rows on your report page (this could be expanded with subtle changes to the formulas).
  4. Completed rows on the report page will start at Row 2

First, you need a way to identify that the equipment entered on your report tab's cell A1 matches each row on the datacollection's column A and that the work is complete in column B You could do this with an "and" formula in column C of your data collection tab.

=AND(A1=reference!$A$1, B1='Y')

This will return TRUE if both conditions are met and FALSE if either or both are not met.

Next we have to get a little higher level on this. We need get the TRUE rows from the data entry sheet over to the report sheet. We don't want the report sheet to have a bunch of blank rows in between the TRUE rows that are brought over. Otherwise we could just do a little vlookup or index/match on TRUE and call it a day. Instead, we can use RANK to establish the order in which the rows are brought over from the data collection page to the reference page.

To do this, in column D on the data collection sheet we need to RANK the TRUE's from 1 to whatever the count of TRUE rows is. Since there is no way to say that one TRUE row ranks higher than another, we will use ROW() for the row number to establish the rank. In Column D enter:

=IF(C1=TRUE, ROW(), 0)

All this will do is write the row in Column D when the criteria established in Column C is met. Now to add the RANK, in Column E enter:

=RANK(D1,D1:D14)*(D1>0)

This does two things. First it determines the rank of the row starting at 1 and going to whatever the number of "TRUE" rows there are in the first 14 rows (increase that to cover all of your data entry rows). Second the *(D1>0) puts a 0 in column E if the value in Column D is 0 because we don't want to even consider the rank if it's a FALSE row.

Now you should have ranked values in Column E that are in descending order based on whether the criteria was TRUE and the row number. Now we need to drag these values over to the report sheet. To do this, in the report sheet starting at Cell A2 start numbering 1,2,3,4,... down the column. These will "match" up to the ranks in your data collection sheet in column E.

Now to get the first column of matching rows, enter the following in your report sheet's column B (starting at B1):

=INDEX(Sheet2!A:A, MATCH(Sheet1!A2, Sheet2!E:E,0), 1)

This is using the index function which allows you to pull the value from a specific row and column in a range. Our range is Sheet2!A:A (the data collection sheet's, column A). Our row is whichever rank (sheet2!E:E) value matches our numbers that we entered into our reporting sheet's column A. Our column, is just 1. Look up "index match in excel" in google to understand this one more if this is new to you. You can pull your data collection column B the same way. In column B of your report sheet (Starting at B2) enter the following:

=INDEX(Sheet2!B:B, MATCH(Sheet1!A2, Sheet2!E:E,0), 1)

That's it for my simple assumptions. Essentially you need three columns to determine whether your criteria was met in the data collection tab and then to rank that data from 1 to n. And you need to use index match (or vlookup if you rearrange things a bit) to pull those ranked rows to your reporting sheet. You can copy this same logic to rank the FALSE's in case you would like to bring over rows marked as incomplete in your data collection tab.

Lastly, after you wrap your head around this, consider moving columns C,D, E in the data collection tab to some other tab so the folks doing the data collection don't monkey up your formulas. The Index/Match function will still work fine and your data collection peeps won't see the gory details of your workbook's inner workings.

You must log in to answer this question.