| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
programming delima
I will try to explain my problem. I am tring to get excel to go to lets say
cell O26 which has a drop down text menu of three categories. It then needs to go to cells AR3:AZ3 and match the header fo the column. Once it is done then I need it to gp to cell O58 which is a numberic number and then go back to cells below AR3:AZ3 and find the closest match (not exact number) and then once both are done go back to the left and produce the number listed there. ie like this: O26 = metal AR3:AZ3 = level copper metal zinc O58 is say 28 under metal the numbers are 20, 30, 40, 80,120,170,230.300,380,470,570, under copper is 18,27,45,72,108.153.207.270.342.423.513. zinc is another 11 set of numbers.Since the numbers under metal are 20 and then 30 it shoudl pick up 20 then go to the left where AR3 is 0,1,2,3,4,5,6,7,8,9,10 and put the appropiate number in G58. the correct answer is 0. I have tried vlookup etc, i have tried match with index but this is well over my head, any help is greatly appreciated. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
programming delima
"Don Guillett" wrote: If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You send a clear explanation of what you want 3. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "Khanjohn" wrote in message ... I will try to explain my problem. I am tring to get excel to go to lets say cell O26 which has a drop down text menu of three categories. It then needs to go to cells AR3:AZ3 and match the header fo the column. Once it is done then I need it to gp to cell O58 which is a numberic number and then go back to cells below AR3:AZ3 and find the closest match (not exact number) and then once both are done go back to the left and produce the number listed there. ie like this: O26 = metal AR3:AZ3 = level copper metal zinc O58 is say 28 under metal the numbers are 20, 30, 40, 80,120,170,230.300,380,470,570, under copper is 18,27,45,72,108.153.207.270.342.423.513. zinc is another 11 set of numbers.Since the numbers under metal are 20 and then 30 it shoudl pick up 20 then go to the left where AR3 is 0,1,2,3,4,5,6,7,8,9,10 and put the appropiate number in G58. the correct answer is 0. I have tried vlookup etc, i have tried match with index but this is well over my head, any help is greatly appreciated. thanks for the response. I have done as you requested and hopefully you can help me out. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
programming delima
I got your file and tried to respond but my reply was refused?????
I really don't understand what you are doing? You are going to have to get very specific. Who and where are you, who do you work for and what is this for? -- Don Guillett Microsoft MVP Excel SalesAid Software "khanjohn" wrote in message ... "Don Guillett" wrote: If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You send a clear explanation of what you want 3. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "Khanjohn" wrote in message ... I will try to explain my problem. I am tring to get excel to go to lets say cell O26 which has a drop down text menu of three categories. It then needs to go to cells AR3:AZ3 and match the header fo the column. Once it is done then I need it to gp to cell O58 which is a numberic number and then go back to cells below AR3:AZ3 and find the closest match (not exact number) and then once both are done go back to the left and produce the number listed there. ie like this: O26 = metal AR3:AZ3 = level copper metal zinc O58 is say 28 under metal the numbers are 20, 30, 40, 80,120,170,230.300,380,470,570, under copper is 18,27,45,72,108.153.207.270.342.423.513. zinc is another 11 set of numbers.Since the numbers under metal are 20 and then 30 it shoudl pick up 20 then go to the left where AR3 is 0,1,2,3,4,5,6,7,8,9,10 and put the appropiate number in G58. the correct answer is 0. I have tried vlookup etc, i have tried match with index but this is well over my head, any help is greatly appreciated. thanks for the response. I have done as you requested and hopefully you can help me out. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
programming delima
I have no clue why you would have gotten a refusal to my email address. I
replied back to you again explaining who, what etc. I am not sure hwo to make it much clearer than I did. "Don Guillett" wrote: I got your file and tried to respond but my reply was refused????? I really don't understand what you are doing? You are going to have to get very specific. Who and where are you, who do you work for and what is this for? -- Don Guillett Microsoft MVP Excel SalesAid Software "khanjohn" wrote in message ... "Don Guillett" wrote: If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You send a clear explanation of what you want 3. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "Khanjohn" wrote in message ... I will try to explain my problem. I am tring to get excel to go to lets say cell O26 which has a drop down text menu of three categories. It then needs to go to cells AR3:AZ3 and match the header fo the column. Once it is done then I need it to gp to cell O58 which is a numberic number and then go back to cells below AR3:AZ3 and find the closest match (not exact number) and then once both are done go back to the left and produce the number listed there. ie like this: O26 = metal AR3:AZ3 = level copper metal zinc O58 is say 28 under metal the numbers are 20, 30, 40, 80,120,170,230.300,380,470,570, under copper is 18,27,45,72,108.153.207.270.342.423.513. zinc is another 11 set of numbers.Since the numbers under metal are 20 and then 30 it shoudl pick up 20 then go to the left where AR3 is 0,1,2,3,4,5,6,7,8,9,10 and put the appropiate number in G58. the correct answer is 0. I have tried vlookup etc, i have tried match with index but this is well over my head, any help is greatly appreciated. thanks for the response. I have done as you requested and hopefully you can help me out. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
programming delima
This is an array formula that finds the closest match to cell o26 based on
the drop down Array formulas must be entered using ctrl+shift+enter instead of just enter =IF($O$26="Standard",INDEX($AE$27:$AE$38,MATCH(MIN (ABS($AE$27:$AE$38-S42)),ABS($AE$27:$AE$38-S42),0),1),IF($O$26="Fast",INDEX($AF$27:$AF$38,MAT CH(MIN(ABS($AF$27:$AF$38-S42)),ABS($AF$27:$AF$38-S42),0),1),INDEX($AG$27:$AG$38,MATCH(MIN(ABS($AG$2 7:$AG$38-S42)),ABS($AG$27:$AG$38-S42),0),1))) -- Don Guillett Microsoft MVP Excel SalesAid Software "khanjohn" wrote in message ... I have no clue why you would have gotten a refusal to my email address. I replied back to you again explaining who, what etc. I am not sure hwo to make it much clearer than I did. "Don Guillett" wrote: I got your file and tried to respond but my reply was refused????? I really don't understand what you are doing? You are going to have to get very specific. Who and where are you, who do you work for and what is this for? -- Don Guillett Microsoft MVP Excel SalesAid Software "khanjohn" wrote in message ... "Don Guillett" wrote: If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You send a clear explanation of what you want 3. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "Khanjohn" wrote in message ... I will try to explain my problem. I am tring to get excel to go to lets say cell O26 which has a drop down text menu of three categories. It then needs to go to cells AR3:AZ3 and match the header fo the column. Once it is done then I need it to gp to cell O58 which is a numberic number and then go back to cells below AR3:AZ3 and find the closest match (not exact number) and then once both are done go back to the left and produce the number listed there. ie like this: O26 = metal AR3:AZ3 = level copper metal zinc O58 is say 28 under metal the numbers are 20, 30, 40, 80,120,170,230.300,380,470,570, under copper is 18,27,45,72,108.153.207.270.342.423.513. zinc is another 11 set of numbers.Since the numbers under metal are 20 and then 30 it shoudl pick up 20 then go to the left where AR3 is 0,1,2,3,4,5,6,7,8,9,10 and put the appropiate number in G58. the correct answer is 0. I have tried vlookup etc, i have tried match with index but this is well over my head, any help is greatly appreciated. thanks for the response. I have done as you requested and hopefully you can help me out. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
programming delima
Thank you but this really has confused me. the index for standard would be
AG27:AG37, the index for fast is AI27;AI37 and for slow is AK27:AK37 and Level is Ac27:AC37. I am not sure where you got -S42 from as nothing I had is in that block as it is blank. Let me try once more to explain. O26 contains the drop down menu for standard, fast and slow. I know it should index/match the columns from the chart, what it also needs to do is match the number found in O58 and match it against the proper column (standard,fast,slow), once it has done both it then needs to find the number from Level that corresponds to the number it found from O58 and then place that value in G58. so in my example that i put in all the colors it should be drop down(O26 = fast. then it finds the fast column and should i guess index it. then it gets the 25 from O58 and looks down the fast column and finds the closest which is 18 (i messed up oin example and put 20, sorry). then it goes to the left to Level column and finds 0 and places it in G58. i then have to do that for the next 100+ in columns starting at O59+ and G59+ "Don Guillett" wrote: This is an array formula that finds the closest match to cell o26 based on the drop down Array formulas must be entered using ctrl+shift+enter instead of just enter =IF($O$26="Standard",INDEX($AE$27:$AE$38,MATCH(MIN (ABS($AE$27:$AE$38-S42)),ABS($AE$27:$AE$38-S42),0),1),IF($O$26="Fast",INDEX($AF$27:$AF$38,MAT CH(MIN(ABS($AF$27:$AF$38-S42)),ABS($AF$27:$AF$38-S42),0),1),INDEX($AG$27:$AG$38,MATCH(MIN(ABS($AG$2 7:$AG$38-S42)),ABS($AG$27:$AG$38-S42),0),1))) -- Don Guillett Microsoft MVP Excel SalesAid Software "khanjohn" wrote in message ... I have no clue why you would have gotten a refusal to my email address. I replied back to you again explaining who, what etc. I am not sure hwo to make it much clearer than I did. "Don Guillett" wrote: I got your file and tried to respond but my reply was refused????? I really don't understand what you are doing? You are going to have to get very specific. Who and where are you, who do you work for and what is this for? -- Don Guillett Microsoft MVP Excel SalesAid Software "khanjohn" wrote in message ... "Don Guillett" wrote: If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You send a clear explanation of what you want 3. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "Khanjohn" wrote in message ... I will try to explain my problem. I am tring to get excel to go to lets say cell O26 which has a drop down text menu of three categories. It then needs to go to cells AR3:AZ3 and match the header fo the column. Once it is done then I need it to gp to cell O58 which is a numberic number and then go back to cells below AR3:AZ3 and find the closest match (not exact number) and then once both are done go back to the left and produce the number listed there. ie like this: O26 = metal AR3:AZ3 = level copper metal zinc O58 is say 28 under metal the numbers are 20, 30, 40, 80,120,170,230.300,380,470,570, under copper is 18,27,45,72,108.153.207.270.342.423.513. zinc is another 11 set of numbers.Since the numbers under metal are 20 and then 30 it shoudl pick up 20 then go to the left where AR3 is 0,1,2,3,4,5,6,7,8,9,10 and put the appropiate number in G58. the correct answer is 0. I have tried vlookup etc, i have tried match with index but this is well over my head, any help is greatly appreciated. thanks for the response. I have done as you requested and hopefully you can help me out. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
programming delima
I copied some things to a new area for MY testing. I will try again to send
you the workbook. -- Don Guillett Microsoft MVP Excel SalesAid Software "khanjohn" wrote in message ... Thank you but this really has confused me. the index for standard would be AG27:AG37, the index for fast is AI27;AI37 and for slow is AK27:AK37 and Level is Ac27:AC37. I am not sure where you got -S42 from as nothing I had is in that block as it is blank. Let me try once more to explain. O26 contains the drop down menu for standard, fast and slow. I know it should index/match the columns from the chart, what it also needs to do is match the number found in O58 and match it against the proper column (standard,fast,slow), once it has done both it then needs to find the number from Level that corresponds to the number it found from O58 and then place that value in G58. so in my example that i put in all the colors it should be drop down(O26 = fast. then it finds the fast column and should i guess index it. then it gets the 25 from O58 and looks down the fast column and finds the closest which is 18 (i messed up oin example and put 20, sorry). then it goes to the left to Level column and finds 0 and places it in G58. i then have to do that for the next 100+ in columns starting at O59+ and G59+ "Don Guillett" wrote: This is an array formula that finds the closest match to cell o26 based on the drop down Array formulas must be entered using ctrl+shift+enter instead of just enter =IF($O$26="Standard",INDEX($AE$27:$AE$38,MATCH(MIN (ABS($AE$27:$AE$38-S42)),ABS($AE$27:$AE$38-S42),0),1),IF($O$26="Fast",INDEX($AF$27:$AF$38,MAT CH(MIN(ABS($AF$27:$AF$38-S42)),ABS($AF$27:$AF$38-S42),0),1),INDEX($AG$27:$AG$38,MATCH(MIN(ABS($AG$2 7:$AG$38-S42)),ABS($AG$27:$AG$38-S42),0),1))) -- Don Guillett Microsoft MVP Excel SalesAid Software "khanjohn" wrote in message ... I have no clue why you would have gotten a refusal to my email address. I replied back to you again explaining who, what etc. I am not sure hwo to make it much clearer than I did. "Don Guillett" wrote: I got your file and tried to respond but my reply was refused????? I really don't understand what you are doing? You are going to have to get very specific. Who and where are you, who do you work for and what is this for? -- Don Guillett Microsoft MVP Excel SalesAid Software "khanjohn" wrote in message ... "Don Guillett" wrote: If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You send a clear explanation of what you want 3. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "Khanjohn" wrote in message ... I will try to explain my problem. I am tring to get excel to go to lets say cell O26 which has a drop down text menu of three categories. It then needs to go to cells AR3:AZ3 and match the header fo the column. Once it is done then I need it to gp to cell O58 which is a numberic number and then go back to cells below AR3:AZ3 and find the closest match (not exact number) and then once both are done go back to the left and produce the number listed there. ie like this: O26 = metal AR3:AZ3 = level copper metal zinc O58 is say 28 under metal the numbers are 20, 30, 40, 80,120,170,230.300,380,470,570, under copper is 18,27,45,72,108.153.207.270.342.423.513. zinc is another 11 set of numbers.Since the numbers under metal are 20 and then 30 it shoudl pick up 20 then go to the left where AR3 is 0,1,2,3,4,5,6,7,8,9,10 and put the appropiate number in G58. the correct answer is 0. I have tried vlookup etc, i have tried match with index but this is well over my head, any help is greatly appreciated. thanks for the response. I have done as you requested and hopefully you can help me out. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
programming delima
Sent msg and got this from ...Mail Administrator
This Message was undeliverable due to the following reason: Your message was not delivered because the return address was refused. The return address was " The following websites may contain more information to assist you: http://help.rr.com/HMSLogic/rrmail.aspx http://security.rr.com/help.htm http://security.rr.com/contact.htm Please do not reply to this message, as it will go to an unread mailbox -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... I copied some things to a new area for MY testing. I will try again to send you the workbook. -- Don Guillett Microsoft MVP Excel SalesAid Software "khanjohn" wrote in message ... Thank you but this really has confused me. the index for standard would be AG27:AG37, the index for fast is AI27;AI37 and for slow is AK27:AK37 and Level is Ac27:AC37. I am not sure where you got -S42 from as nothing I had is in that block as it is blank. Let me try once more to explain. O26 contains the drop down menu for standard, fast and slow. I know it should index/match the columns from the chart, what it also needs to do is match the number found in O58 and match it against the proper column (standard,fast,slow), once it has done both it then needs to find the number from Level that corresponds to the number it found from O58 and then place that value in G58. so in my example that i put in all the colors it should be drop down(O26 = fast. then it finds the fast column and should i guess index it. then it gets the 25 from O58 and looks down the fast column and finds the closest which is 18 (i messed up oin example and put 20, sorry). then it goes to the left to Level column and finds 0 and places it in G58. i then have to do that for the next 100+ in columns starting at O59+ and G59+ "Don Guillett" wrote: This is an array formula that finds the closest match to cell o26 based on the drop down Array formulas must be entered using ctrl+shift+enter instead of just enter =IF($O$26="Standard",INDEX($AE$27:$AE$38,MATCH(MIN (ABS($AE$27:$AE$38-S42)),ABS($AE$27:$AE$38-S42),0),1),IF($O$26="Fast",INDEX($AF$27:$AF$38,MAT CH(MIN(ABS($AF$27:$AF$38-S42)),ABS($AF$27:$AF$38-S42),0),1),INDEX($AG$27:$AG$38,MATCH(MIN(ABS($AG$2 7:$AG$38-S42)),ABS($AG$27:$AG$38-S42),0),1))) -- Don Guillett Microsoft MVP Excel SalesAid Software "khanjohn" wrote in message ... I have no clue why you would have gotten a refusal to my email address. I replied back to you again explaining who, what etc. I am not sure hwo to make it much clearer than I did. "Don Guillett" wrote: I got your file and tried to respond but my reply was refused????? I really don't understand what you are doing? You are going to have to get very specific. Who and where are you, who do you work for and what is this for? -- Don Guillett Microsoft MVP Excel SalesAid Software "khanjohn" wrote in message ... "Don Guillett" wrote: If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You send a clear explanation of what you want 3. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "Khanjohn" wrote in message ... I will try to explain my problem. I am tring to get excel to go to lets say cell O26 which has a drop down text menu of three categories. It then needs to go to cells AR3:AZ3 and match the header fo the column. Once it is done then I need it to gp to cell O58 which is a numberic number and then go back to cells below AR3:AZ3 and find the closest match (not exact number) and then once both are done go back to the left and produce the number listed there. ie like this: O26 = metal AR3:AZ3 = level copper metal zinc O58 is say 28 under metal the numbers are 20, 30, 40, 80,120,170,230.300,380,470,570, under copper is 18,27,45,72,108.153.207.270.342.423.513. zinc is another 11 set of numbers.Since the numbers under metal are 20 and then 30 it shoudl pick up 20 then go to the left where AR3 is 0,1,2,3,4,5,6,7,8,9,10 and put the appropiate number in G58. the correct answer is 0. I have tried vlookup etc, i have tried match with index but this is well over my head, any help is greatly appreciated. thanks for the response. I have done as you requested and hopefully you can help me out. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
programming delima
i sent you my RR account to send the file to.
"Don Guillett" wrote: Sent msg and got this from ...Mail Administrator This Message was undeliverable due to the following reason: Your message was not delivered because the return address was refused. The return address was " The following websites may contain more information to assist you: http://help.rr.com/HMSLogic/rrmail.aspx http://security.rr.com/help.htm http://security.rr.com/contact.htm Please do not reply to this message, as it will go to an unread mailbox -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... I copied some things to a new area for MY testing. I will try again to send you the workbook. -- Don Guillett Microsoft MVP Excel SalesAid Software "khanjohn" wrote in message ... Thank you but this really has confused me. the index for standard would be AG27:AG37, the index for fast is AI27;AI37 and for slow is AK27:AK37 and Level is Ac27:AC37. I am not sure where you got -S42 from as nothing I had is in that block as it is blank. Let me try once more to explain. O26 contains the drop down menu for standard, fast and slow. I know it should index/match the columns from the chart, what it also needs to do is match the number found in O58 and match it against the proper column (standard,fast,slow), once it has done both it then needs to find the number from Level that corresponds to the number it found from O58 and then place that value in G58. so in my example that i put in all the colors it should be drop down(O26 = fast. then it finds the fast column and should i guess index it. then it gets the 25 from O58 and looks down the fast column and finds the closest which is 18 (i messed up oin example and put 20, sorry). then it goes to the left to Level column and finds 0 and places it in G58. i then have to do that for the next 100+ in columns starting at O59+ and G59+ "Don Guillett" wrote: This is an array formula that finds the closest match to cell o26 based on the drop down Array formulas must be entered using ctrl+shift+enter instead of just enter =IF($O$26="Standard",INDEX($AE$27:$AE$38,MATCH(MIN (ABS($AE$27:$AE$38-S42)),ABS($AE$27:$AE$38-S42),0),1),IF($O$26="Fast",INDEX($AF$27:$AF$38,MAT CH(MIN(ABS($AF$27:$AF$38-S42)),ABS($AF$27:$AF$38-S42),0),1),INDEX($AG$27:$AG$38,MATCH(MIN(ABS($AG$2 7:$AG$38-S42)),ABS($AG$27:$AG$38-S42),0),1))) -- Don Guillett Microsoft MVP Excel SalesAid Software "khanjohn" wrote in message ... I have no clue why you would have gotten a refusal to my email address. I replied back to you again explaining who, what etc. I am not sure hwo to make it much clearer than I did. "Don Guillett" wrote: I got your file and tried to respond but my reply was refused????? I really don't understand what you are doing? You are going to have to get very specific. Who and where are you, who do you work for and what is this for? -- Don Guillett Microsoft MVP Excel SalesAid Software "khanjohn" wrote in message ... "Don Guillett" wrote: If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You send a clear explanation of what you want 3. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "Khanjohn" wrote in message ... I will try to explain my problem. I am tring to get excel to go to lets say cell O26 which has a drop down text menu of three categories. It then needs to go to cells AR3:AZ3 and match the header fo the column. Once it is done then I need it to gp to cell O58 which is a numberic number and then go back to cells below AR3:AZ3 and find the closest match (not exact number) and then once both are done go back to the left and produce the number listed there. ie like this: O26 = metal AR3:AZ3 = level copper metal zinc O58 is say 28 under metal the numbers are 20, 30, 40, 80,120,170,230.300,380,470,570, under copper is 18,27,45,72,108.153.207.270.342.423.513. zinc is another 11 set of numbers.Since the numbers under metal are 20 and then 30 it shoudl pick up 20 then go to the left where AR3 is 0,1,2,3,4,5,6,7,8,9,10 and put the appropiate number in G58. the correct answer is 0. I have tried vlookup etc, i have tried match with index but this is well over my head, any help is greatly appreciated. thanks for the response. I have done as you requested and hopefully you can help me out. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| XLL programming for Mac. | Excel Programming | |||
| vba programming | Excel Discussion (Misc queries) | |||
| Numbers disguised as Text - Sorting Delima | Excel Discussion (Misc queries) | |||
| Programming | Excel Programming | |||
| new to VBA programming | Excel Programming | |||
Linear Mode
