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 t
have other people in my office using this template, so I don't want t
have to explain to them that they need to browse through menus, clic
on Filters, etc.

I only have a limited amount of space on Worksheet 2, so I'd like t
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


+-------------------------------------------------------------------+
|Filename: Pivot1.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=3466 |
+-------------------------------------------------------------------+