Which to use - if, vlookup, match, index, offset, vba?

[punsterr]

Hi,

In Worksheet 1, we have the following:

Column A Column B Column C

Description 1 Value 1a Value 1b
Description 2 Value 2a Value 2b
...
Description 20 Value 20a Value 20b

In Worksheet 2, I would like to create a list of items from Worksheet 1 that meet a certain criteria. Trick is, I don't want to have 20 open rows in Worksheet 2 and copy/paste a formula. I only want to list the items that exceed the threshhold.

For example, I want to create a list of items for which the value in Column C exceeds 5. If R1C3>5, then list R1C1, R1C2, R1C3. If not, find the next row in which the value in Column C exceeds 5, then pick up all three columns of info for that particular item. Each time it finds a True response, it adds a line in Worksheet 2 to list the next item that meets the criteria.

Finally, I want to add one last line item that sums up all of the values in Column C that did NOT meet the criteria. (Basically, list out all items that exceed 5, plus one "Other" amount to sum up all the items not listed separately. I suppose this could be accomplished by adding each of the individual remaining items or by taking a total of the values in Column C of Worksheet 1 and subtracting the items listed out separately.)

I appreciate your help!

[Ron Coderre]

Seems like this could be handled by an Advanced Filter:

1)Make sure there are column headings for your data (Desc, Val1, Val2)
2)Go to Sheet 2
3)A1: Val2, A2: >5
4)A5: Desc, B5: Val1, C5: Val2

5)Select A5:C6
6)Data>Filter>Advanced Filter
Copy to another Location
List Range: Sheet1!A1:C100
Criteria Range: Sheet2!A1:A2
Copy to: Sheet2!A5:C5
Click [OK]

That should pull all items from Sheet 1 where Column C is greater than 5.

If that works for you, then we can work on totalling the non-matching items.

Does that help?

Ron

[punsterr]

Thanks for your response. While that method seems like it might work, I'd prefer to perform the action via a formula or macro. I'm going to have other people in my office using this template, so I don't want to have to explain to them that they need to browse through menus, click on Filters, etc.

I only have a limited amount of space on Worksheet 2, so I'd like to have it insert lines only if the threshhold is met for each item.

[Ron Coderre]

OK...No advanced filter.

How about a pivot table? (See attached jpeg)

Once it is set up, users only need to click Refresh on the pivot table to get the latest data.

Is that still too techie for them or would that meet your needs?

Ron