Formula Help please

  • Thread starter Thread starter 9mm Parabellum
  • Start date Start date
9

9mm Parabellum

Name IN CA NY
Beth x
Jim x
Tim x
Lee x
Doug x
Sally x

Which formula (and how) should I use if I want to generate a new sheet
with all the names of people that come from IN only?

Thanks,
Mike
 
What i tend to do is;

if i need to know the original order of this list, I number each row 1 ->
wherever.

Then select the data and sort it by IN (asc or desc doesn't matter, it's the
grouping that will help), then copy the IN group over to a new worksheet.

You can then re-sort by the original #s.
 
Yes, that will work as well but since it's a huge spreadsheet, I want t
be able to put new data on the master sheet and then print out the res
of the sheets with the data already sorted.

Thanks for the help though. :)

Mik
 
Mike,

Let's say your list was on Sheet2 in the range A1:D7

You want the new list to appear on Sheet3 in A2:D(however far)

In A2
=INDEX(Sheet2!A2:A7,MATCH("x",Sheet2!B2:B7,0))
In A3
=INDEX(INDIRECT("Sheet2!A" & MATCH(Sheet2!B10,
Sheet2!$A$1:$A$7,0) + 1 & ":A7"),MATCH("x",INDIRECT("Sheet2!B"
& MATCH(Sheet2!B10,Sheet2!$A$1:$A$7,0) + 1 & ":B7"),0))

Then drag the formula in A3 down.

Note you'll need to change the ranges to fit your data...

Dan E
 
-----Original Message-----
Name IN CA NY
Beth x
Jim x
Tim x
Lee x
Doug x
Sally x

Which formula (and how) should I use if I want to generate a new sheet
with all the names of people that come from IN only?

Thanks,
Mike
Click to expand...
 
9mm Parabellum 9mm.Parabellum.yoy4z@excelforum-nospam.
Click to expand...
Name IN CA NY
Beth x
Jim x
Tim x
Lee x
Doug x
Sally x

Which formula (and how) should I use if I want to generate a new sheet
with all the names of people that come from IN only?
Click to expand...


1. Assign a named range to the 'name' and 'IN' columns. I used 'IN.'

2. One Sheet #2, enter this formula in A1

=IF(Sheet1!B2="x",VLOOKUP(Sheet1!A2,IN,1),"")

Copy down, as needed and filter the blanks
 
Back
Top