| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need a Lookup of some sort cont.
Ok I tried the auto filter thing, not working the way i need it, here are the
specifics again, I have a chart that looks like this on the first worksheet: NAME AGE CHILDREN JOE SMITH 32 YES DEBBY ALLEN 19 NO CHRIS JONES 45 YES TOM WILLIAM 17 YES etc, etc...... OK this list has about 60-80 names on it....now i have another table on another page that has enough lines for exactly 10 of these names to be transfered to, I know that there will be only 10 peole from the list who answer NO to the CHILDREN column, so I wan to be able to somehow pull from the first list(60 people) and transfer the names of the 10 without kids to the spot I have on the second page...is this possible, i tried sort/filter, does not do what i need it to do, can i use a lookup somehow??? PLEASE HELP |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I need a Lookup of some sort cont.
Select the table of data
Data/ Autofilter Children/ No Copy Paste -- David Biddulph "HELPME" wrote in message ... Ok I tried the auto filter thing, not working the way i need it, here are the specifics again, I have a chart that looks like this on the first worksheet: NAME AGE CHILDREN JOE SMITH 32 YES DEBBY ALLEN 19 NO CHRIS JONES 45 YES TOM WILLIAM 17 YES etc, etc...... OK this list has about 60-80 names on it....now i have another table on another page that has enough lines for exactly 10 of these names to be transfered to, I know that there will be only 10 peole from the list who answer NO to the CHILDREN column, so I wan to be able to somehow pull from the first list(60 people) and transfer the names of the 10 without kids to the spot I have on the second page...is this possible, i tried sort/filter, does not do what i need it to do, can i use a lookup somehow??? PLEASE HELP |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I need a Lookup of some sort cont.
Ughhhh....lol...I know it can be done this way, but am looking to have it
fill the second table in automatically without having to cut and paste or copy and paste. The person who will be using this just wants the master list of names, and then the second table to populate itself without having to sort/filter cut and paste, there must be a way for this to work, if a macro is needed, i dont know how to do macros, but can learn, it hought a lookup could be used, but having problems since one list is 60-80 lines going into a table that is only 10 lines large. PLEASE HELP "David Biddulph" wrote: Select the table of data Data/ Autofilter Children/ No Copy Paste -- David Biddulph "HELPME" wrote in message ... Ok I tried the auto filter thing, not working the way i need it, here are the specifics again, I have a chart that looks like this on the first worksheet: NAME AGE CHILDREN JOE SMITH 32 YES DEBBY ALLEN 19 NO CHRIS JONES 45 YES TOM WILLIAM 17 YES etc, etc...... OK this list has about 60-80 names on it....now i have another table on another page that has enough lines for exactly 10 of these names to be transfered to, I know that there will be only 10 peole from the list who answer NO to the CHILDREN column, so I wan to be able to somehow pull from the first list(60 people) and transfer the names of the 10 without kids to the spot I have on the second page...is this possible, i tried sort/filter, does not do what i need it to do, can i use a lookup somehow??? PLEASE HELP |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I need a Lookup of some sort cont.
I wouldn't have wasted my time answering if I had realised that you had
already asked the question in another thread and got the answer. Please don't start a new thread in such situations; if you had continued the previous thread and explained why you weren't happy with the suggestion that had been made, then you might have stood more chance of getting the answer you wanted. -- David Biddulph "HELPME" wrote in message ... Ughhhh....lol...I know it can be done this way, but am looking to have it fill the second table in automatically without having to cut and paste or copy and paste. The person who will be using this just wants the master list of names, and then the second table to populate itself without having to sort/filter cut and paste, there must be a way for this to work, if a macro is needed, i dont know how to do macros, but can learn, it hought a lookup could be used, but having problems since one list is 60-80 lines going into a table that is only 10 lines large. PLEASE HELP "David Biddulph" wrote: Select the table of data Data/ Autofilter Children/ No Copy Paste -- David Biddulph "HELPME" wrote in message ... Ok I tried the auto filter thing, not working the way i need it, here are the specifics again, I have a chart that looks like this on the first worksheet: NAME AGE CHILDREN JOE SMITH 32 YES DEBBY ALLEN 19 NO CHRIS JONES 45 YES TOM WILLIAM 17 YES etc, etc...... OK this list has about 60-80 names on it....now i have another table on another page that has enough lines for exactly 10 of these names to be transfered to, I know that there will be only 10 peole from the list who answer NO to the CHILDREN column, so I wan to be able to somehow pull from the first list(60 people) and transfer the names of the 10 without kids to the spot I have on the second page...is this possible, i tried sort/filter, does not do what i need it to do, can i use a lookup somehow??? PLEASE HELP |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I need a Lookup of some sort cont.
So, you want just the names? And there will be no more than 10 that meet the
condition? Will there ever be less than 10 that meet the condition? Assume this data is on Sheet1 starting in cell A2: JOE SMITH 32 YES DEBBY ALLEN 19 NO CHRIS JONES 45 YES TOM WILLIAM 17 YES Enter this formula where you want the names listed. Enter this formula as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!C$2:C$5="no" ,ROW(A$2:A$5)-MIN(ROW(A$2:A$5))+1),ROWS($1:1))) Copy down 10 rows. If there may be less than 10 names that meet the condition then use this version to handle any errors (also arrary entered): =IF(ROWS($1:1)<=COUNTIF(Sheet1!C$2:C$5,"no"),INDEX (Sheet1!A$2:A$5,SMALL(IF(Sheet1!C$2:C$5="no",ROW(A $2:A$5)-MIN(ROW(A$2:A$5))+1),ROWS($1:1))),"") Biff "HELPME" wrote in message ... Ok I tried the auto filter thing, not working the way i need it, here are the specifics again, I have a chart that looks like this on the first worksheet: NAME AGE CHILDREN JOE SMITH 32 YES DEBBY ALLEN 19 NO CHRIS JONES 45 YES TOM WILLIAM 17 YES etc, etc...... OK this list has about 60-80 names on it....now i have another table on another page that has enough lines for exactly 10 of these names to be transfered to, I know that there will be only 10 peole from the list who answer NO to the CHILDREN column, so I wan to be able to somehow pull from the first list(60 people) and transfer the names of the 10 without kids to the spot I have on the second page...is this possible, i tried sort/filter, does not do what i need it to do, can i use a lookup somehow??? PLEASE HELP |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I need a Lookup of some sort cont.
Thank you so much T. Valko, works like a dream, I swear I dont understand how
people like you and everyone else who helps people on this board learn so much about Excel, I use it quite often, but i guess im still just at the basic level...anyway thanks again, and David Biddulph, sorry if i upset you, i appreciate your time and effort in helping me, did not think anything was wrong with starting a new thread, did not want people to tell me to use filter again since i had tried that, anyway sorry man. One last question, does anyone know how to link a macro to like a cool picture button to start it, thanks.... and what is considered advanced or expert level Excel, (pivot tables, using macros, or all these function formulas??? Trying to get better at all this, just curious as to what is considered the most advanced Excel stuff you can do??? Thanks again and Happy New Year "T. Valko" wrote: So, you want just the names? And there will be no more than 10 that meet the condition? Will there ever be less than 10 that meet the condition? Assume this data is on Sheet1 starting in cell A2: JOE SMITH 32 YES DEBBY ALLEN 19 NO CHRIS JONES 45 YES TOM WILLIAM 17 YES Enter this formula where you want the names listed. Enter this formula as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!C$2:C$5="no" ,ROW(A$2:A$5)-MIN(ROW(A$2:A$5))+1),ROWS($1:1))) Copy down 10 rows. If there may be less than 10 names that meet the condition then use this version to handle any errors (also arrary entered): =IF(ROWS($1:1)<=COUNTIF(Sheet1!C$2:C$5,"no"),INDEX (Sheet1!A$2:A$5,SMALL(IF(Sheet1!C$2:C$5="no",ROW(A $2:A$5)-MIN(ROW(A$2:A$5))+1),ROWS($1:1))),"") Biff "HELPME" wrote in message ... Ok I tried the auto filter thing, not working the way i need it, here are the specifics again, I have a chart that looks like this on the first worksheet: NAME AGE CHILDREN JOE SMITH 32 YES DEBBY ALLEN 19 NO CHRIS JONES 45 YES TOM WILLIAM 17 YES etc, etc...... OK this list has about 60-80 names on it....now i have another table on another page that has enough lines for exactly 10 of these names to be transfered to, I know that there will be only 10 peole from the list who answer NO to the CHILDREN column, so I wan to be able to somehow pull from the first list(60 people) and transfer the names of the 10 without kids to the spot I have on the second page...is this possible, i tried sort/filter, does not do what i need it to do, can i use a lookup somehow??? PLEASE HELP |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I need a Lookup of some sort cont.
You can assign a macro to any object, including a picture.
Insert the picture into your sheet then right-click and "Assign Macro" Gord Dibben MS Excel MVP On Sat, 30 Dec 2006 16:47:01 -0800, HELPME wrote: Thank you so much T. Valko, works like a dream, I swear I dont understand how people like you and everyone else who helps people on this board learn so much about Excel, I use it quite often, but i guess im still just at the basic level...anyway thanks again, and David Biddulph, sorry if i upset you, i appreciate your time and effort in helping me, did not think anything was wrong with starting a new thread, did not want people to tell me to use filter again since i had tried that, anyway sorry man. One last question, does anyone know how to link a macro to like a cool picture button to start it, thanks.... and what is considered advanced or expert level Excel, (pivot tables, using macros, or all these function formulas??? Trying to get better at all this, just curious as to what is considered the most advanced Excel stuff you can do??? Thanks again and Happy New Year "T. Valko" wrote: So, you want just the names? And there will be no more than 10 that meet the condition? Will there ever be less than 10 that meet the condition? Assume this data is on Sheet1 starting in cell A2: JOE SMITH 32 YES DEBBY ALLEN 19 NO CHRIS JONES 45 YES TOM WILLIAM 17 YES Enter this formula where you want the names listed. Enter this formula as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!C$2:C$5="no" ,ROW(A$2:A$5)-MIN(ROW(A$2:A$5))+1),ROWS($1:1))) Copy down 10 rows. If there may be less than 10 names that meet the condition then use this version to handle any errors (also arrary entered): =IF(ROWS($1:1)<=COUNTIF(Sheet1!C$2:C$5,"no"),INDEX (Sheet1!A$2:A$5,SMALL(IF(Sheet1!C$2:C$5="no",ROW(A $2:A$5)-MIN(ROW(A$2:A$5))+1),ROWS($1:1))),"") Biff "HELPME" wrote in message ... Ok I tried the auto filter thing, not working the way i need it, here are the specifics again, I have a chart that looks like this on the first worksheet: NAME AGE CHILDREN JOE SMITH 32 YES DEBBY ALLEN 19 NO CHRIS JONES 45 YES TOM WILLIAM 17 YES etc, etc...... OK this list has about 60-80 names on it....now i have another table on another page that has enough lines for exactly 10 of these names to be transfered to, I know that there will be only 10 peole from the list who answer NO to the CHILDREN column, so I wan to be able to somehow pull from the first list(60 people) and transfer the names of the 10 without kids to the spot I have on the second page...is this possible, i tried sort/filter, does not do what i need it to do, can i use a lookup somehow??? PLEASE HELP |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I need a Lookup of some sort cont.
You're welcome. Thanks for the feedback!
what is considered advanced or expert level Excel, (pivot tables, using macros, or all these function formulas??? Hmmm..... That's a hard question to answer since everybody has their own interpretation of "advanced" and "expert". ???? Biff "HELPME" wrote in message ... Thank you so much T. Valko, works like a dream, I swear I dont understand how people like you and everyone else who helps people on this board learn so much about Excel, I use it quite often, but i guess im still just at the basic level...anyway thanks again, and David Biddulph, sorry if i upset you, i appreciate your time and effort in helping me, did not think anything was wrong with starting a new thread, did not want people to tell me to use filter again since i had tried that, anyway sorry man. One last question, does anyone know how to link a macro to like a cool picture button to start it, thanks.... and what is considered advanced or expert level Excel, (pivot tables, using macros, or all these function formulas??? Trying to get better at all this, just curious as to what is considered the most advanced Excel stuff you can do??? Thanks again and Happy New Year "T. Valko" wrote: So, you want just the names? And there will be no more than 10 that meet the condition? Will there ever be less than 10 that meet the condition? Assume this data is on Sheet1 starting in cell A2: JOE SMITH 32 YES DEBBY ALLEN 19 NO CHRIS JONES 45 YES TOM WILLIAM 17 YES Enter this formula where you want the names listed. Enter this formula as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!C$2:C$5="no" ,ROW(A$2:A$5)-MIN(ROW(A$2:A$5))+1),ROWS($1:1))) Copy down 10 rows. If there may be less than 10 names that meet the condition then use this version to handle any errors (also arrary entered): =IF(ROWS($1:1)<=COUNTIF(Sheet1!C$2:C$5,"no"),INDEX (Sheet1!A$2:A$5,SMALL(IF(Sheet1!C$2:C$5="no",ROW(A $2:A$5)-MIN(ROW(A$2:A$5))+1),ROWS($1:1))),"") Biff "HELPME" wrote in message ... Ok I tried the auto filter thing, not working the way i need it, here are the specifics again, I have a chart that looks like this on the first worksheet: NAME AGE CHILDREN JOE SMITH 32 YES DEBBY ALLEN 19 NO CHRIS JONES 45 YES TOM WILLIAM 17 YES etc, etc...... OK this list has about 60-80 names on it....now i have another table on another page that has enough lines for exactly 10 of these names to be transfered to, I know that there will be only 10 peole from the list who answer NO to the CHILDREN column, so I wan to be able to somehow pull from the first list(60 people) and transfer the names of the 10 without kids to the spot I have on the second page...is this possible, i tried sort/filter, does not do what i need it to do, can i use a lookup somehow??? PLEASE HELP |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I need a Lookup of some sort cont.
In Sheet 1
Assuming a header in row 1 Names: A2:A100 Age: B2:B100 Children: C2:C100 In Sheet 2 Header in A1 A2 =IF(ISERR(SMALL(IF(Sheet1!$C$2:$C$100="no",ROW(IND IRECT("1:"&ROWS($A$2:$A$100)))),ROWS($1:1))),"",IN DEX(Sheet1!$A$2:$A$100,SMALL(IF(Sheet1!$C$2:$C$100 ="no",ROW(INDIRECT("1:"&ROWS($A$2:$A$100)))),ROWS( $1:1)))) Adjust your range to suit ctrl+shift+enter, not just enter copy by dragging the Fill handle down as far as needed "HELPME" wrote: Ok I tried the auto filter thing, not working the way i need it, here are the specifics again, I have a chart that looks like this on the first worksheet: NAME AGE CHILDREN JOE SMITH 32 YES DEBBY ALLEN 19 NO CHRIS JONES 45 YES TOM WILLIAM 17 YES etc, etc...... OK this list has about 60-80 names on it....now i have another table on another page that has enough lines for exactly 10 of these names to be transfered to, I know that there will be only 10 peole from the list who answer NO to the CHILDREN column, so I wan to be able to somehow pull from the first list(60 people) and transfer the names of the 10 without kids to the spot I have on the second page...is this possible, i tried sort/filter, does not do what i need it to do, can i use a lookup somehow??? PLEASE HELP |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I need a Lookup of some sort cont. - HELPM
I know what you need, it's hard for me to explain, I just happened upon this forum looking to help myself solve another issue... but you need a dynamic index. if you send me the file, I could send it back with the formula.
It will look something like this (brackets to indicate a matrix foluma): {=index(array, row, column)} to get the row dynamically: {=index(array, small(array,k), column)} which should look like this, referencing the "Children" column: {=index(array, small(if(array="Yes",row(array)),k), column)} to make k change dynamically, substitute with column(A:A) if dragging across columns or row(1:1) if dragging across rows... maybe someone already replied, I don't know... EggHeadCafe.com - .NET Developer Portal of Choice http://www.eggheadcafe.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sorting data on protected worksheet | Excel Discussion (Misc queries) | |||
| Filter, sort, lookup from other sheet | Excel Discussion (Misc queries) | |||
| HOW TO SORT A COLUMN THE SAME AS ANOTHER COLUMN WITH SIMILAR CONT. | Excel Worksheet Functions | |||
| A sort of lookup or match | Excel Worksheet Functions | |||
| Lookup and Sort Question | Excel Discussion (Misc queries) | |||
Hybrid Mode
