conditional moves

[wal50]

I have all records in one sheet with multiple records for each region. I
want to create a tab for each region containing all that region's records.
The number of records will be different for each region.
I've tried several variations of vlookups and change the selected column as
I copy across. This works but when I copy down, I get multiple copies of the
first record for that region. Like this:
=IF(ISERROR(VLOOKUP('All records'!$A$5,'All records'!$B$1:$H$30,1,FALSE)),"
",VLOOKUP('All records'!$A$5,'All records'!$B$1:$H$30,1,FALSE))
I used the iserror because I thought I could just copy the down and when the
lookup did't find any more matches it would print blanks, That didn't work
either. FYI: Column A od all records contains the region names and I was
incrementing the "1" as I copied across.

Anyway, none of this worked past the first record so any ideas would be
appreciated.
WAL

 

[Miguel Zapico]

You may use MATCH, INDEX and OFFSET instead of VLOOKUP. There are some
examples in:
http://cpearson.com/excel/lookups.htm

Hope this helps,
Miguel.

 

[wal50]

Thanks Miguel. This gets me closer to what I want. I understand how to use
this to get a specific value but how would I be able to use this to get all
the instances of a value. And I won't know how many there are. Are we
getting into VBA here or are there functions?
WAL50

 

[Miguel Zapico]

One solution can be the use of INDIRECT to trim the range you are looking at.
If you can have an additional column, use this array (enter with
CTRL+SHIFT+ENTER) formula in B2:
=B1 + MATCH(FALSE,ISERROR(MATCH(INDIRECT("'All records'!B" & 1 + B1 &
":$B$30"),'All records'!$A$5,0)),0)
This will give the row parameter for using the INDEX formula in A2:
=INDEX('All records'!$B$1:$H$30,B2,2)
You can change the final 2 to grab a different column in the range.

You can copy the formulas in a column, and they will give you an error when
all the values are shown. You can use conditional formating to hide the
errors, if you don't want to see them.

Hope this helps,
Miguel.

 

[wal50]

Thanks

One solution can be the use of INDIRECT to trim the range you are looking at.
If you can have an additional column, use this array (enter with
CTRL+SHIFT+ENTER) formula in B2:
=B1 + MATCH(FALSE,ISERROR(MATCH(INDIRECT("'All records'!B" & 1 + B1 &
":$B$30"),'All records'!$A$5,0)),0)
This will give the row parameter for using the INDEX formula in A2:
=INDEX('All records'!$B$1:$H$30,B2,2)
You can change the final 2 to grab a different column in the range.

You can copy the formulas in a column, and they will give you an error when
all the values are shown. You can use conditional formating to hide the
errors, if you don't want to see them.

Hope this helps,
Miguel.