Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional moves
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional moves
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" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional moves
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" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional moves
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" wrote: 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" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional moves
Thanks
"Miguel Zapico" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keeping conditional formatting when sorting | Excel Discussion (Misc queries) | |||
Conditional Formatting when inserting a row | Excel Worksheet Functions | |||
Conditional Format Not Working | Excel Discussion (Misc queries) | |||
Conditional formula - language needed | Excel Worksheet Functions | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |