|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#11
June 11th, 2009, 09:27 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match multiple values
Don, Jerrr:nuts, you're making up some serious formula's/programming for my skills. Is it possible to modify the web query or am I stuck with how it comes in. If so it means I have to run a macro or similar (like you suggest) to keep things automated, my data are always freshly imported when I open the file. Let try what doctor "Guillett" prescribed and see where I get stuck. Thanks, W -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=105263 
|
|
#13
June 13th, 2009, 04:34 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match multiple values
I looked at this again and a major modification to your col a:c formulas along with major mods to the sumproduct formula negate the need for macros. -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... OK. I have looked at this some more and it appears that the only data that comes from the net is in the yellow section (cols d:h). Is it always the SAME url? Then, it appears that YOU create the formulas that are causing the problems. I think you need a macro to massage your data to get rid of the blank rows and then have the macro create your formulas and your sumproduct formulas will work. If you can't do this then you need to have custom programming. All of this CAN be done with ONE mouse click. Contact me privately, if desired. -- Don Guillett Microsoft MVP Excel SalesAid Software "willemeulen" wrote in message ... Don, Jerrr:nuts, you're making up some serious formula's/programming for my skills. Is it possible to modify the web query or am I stuck with how it comes in. If so it means I have to run a macro or similar (like you suggest) to keep things automated, my data are always freshly imported when I open the file. Let try what doctor "Guillett" prescribed and see where I get stuck. Thanks, W -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=105263
|
|
#14
June 14th, 2009, 07:11 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match multiple values
willemeulen;376188 Wrote: I need to match 3 values as follows: B5 match with sheet1!A3:A194 C5 match with sheet1!B3:B194 D5 match with sheet1!C3:C194 Return value from column sheet1!H3:H194 W Try This INDEX(Sheet1!$H$3:$H$194,MATCH(COMPARE!B5&COMPARE! C5&COMPARE!D5,Sheet1!$A$3:$A$194&Sheet1!$B$3:$B$19 4&Sheet1!$C$3:$C$194,0)) Confirmed with CTRL+SHIFT+ENTER -- hardeep.kanwar ------------------------------------------------------------------------ hardeep.kanwar's Profile: http://www.thecodecage.com/forumz/member.php?userid=170 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=105263
|
|
#15
June 14th, 2009, 03:00 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match multiple values
This solution worked better than mine and did not require a lot of rewrite.
I would only remove the reference to compare! as the formula is on the compare sheet already. Don't forget to array enter. INDEX(Sheet1!$H$3:$H$194,MATCH(COMPARE!B5&COMPARE! C5&COMPARE!D5,Sheet1!$A$3:$A$194&Sheet1!$B$3:$B$19 4&Sheet1!$C$3:$C$194,0)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... I looked at this again and a major modification to your col a:c formulas along with major mods to the sumproduct formula negate the need for macros. -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... OK. I have looked at this some more and it appears that the only data that comes from the net is in the yellow section (cols d:h). Is it always the SAME url? Then, it appears that YOU create the formulas that are causing the problems. I think you need a macro to massage your data to get rid of the blank rows and then have the macro create your formulas and your sumproduct formulas will work. If you can't do this then you need to have custom programming. All of this CAN be done with ONE mouse click. Contact me privately, if desired. -- Don Guillett Microsoft MVP Excel SalesAid Software "willemeulen" wrote in message ... Don, Jerrr:nuts, you're making up some serious formula's/programming for my skills. Is it possible to modify the web query or am I stuck with how it comes in. If so it means I have to run a macro or similar (like you suggest) to keep things automated, my data are always freshly imported when I open the file. Let try what doctor "Guillett" prescribed and see where I get stuck. Thanks, W -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=105263
|
|
#16
June 15th, 2009, 08:48 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match multiple values
This took a while to get figured out, thanks guys for the efforts put in but the last solution suggested works. For some reason the sumproduct function did not want to work, even if I removed all formulas and only focussed on the the first section of the lookup collumn it refused to return the price, alsmost gave up on it. For me its still a mystery why it did not want to work while now it works with gaps and everything. Thanks guys. W -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=105263
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
