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 R1C35, 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!


--
punsterr
------------------------------------------------------------------------
punsterr's Profile: http://www.excelforum.com/member.php...o&userid=23961
View this thread: http://www.excelforum.com/showthread...hreadid=375860

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)DataFilterAdvanced 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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=375860

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.


--
punsterr
------------------------------------------------------------------------
punsterr's Profile: http://www.excelforum.com/member.php...o&userid=23961
View this thread: http://www.excelforum.com/showthread...hreadid=375860

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 |
+-------------------------------------------------------------------+

--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=375860