Looking up the next value

[tcolan]

I'm trying to create a list based on the value in a column of another table. I can't use pivot tables.

So the data look like this:

ID# Status
1A Open
2A Escalated
3B Closed
4R Escalated
5E Open
etc.

From this table I want to create a list that just contains the 'Escalated' issues and ID#s. ie:

ID# Status
2A Escalated
4R Escalated

I know I can use a vlookup to get the first value, but what is the easiest way to get all the subsequent values with the fact that the source table is not static and can be any size?

Thanks,
Tom

 

[AnotherHiggins]

How about just using AutoFilter?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[tcolan]

unfortunately I can't use autofilter...

 

[mintjulep]

Query.

 

[SkipVought]

Hi,

faq68-5829. You can even make it a parameter query, by supplying a value in a cell.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

Why can you not use autofilter?

If you really can;t, can you use Advanced filter? this has an option to copy records to a different location

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question