| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How best to...?
Why am I thinking of work stuff on the weekend?!? I'm hoping someone out there has something to teach a newer user. This is a super group that seems to be very patient. *Sheet1*: I have a table that is 6x19 - $K$8:$P$26. There could be values in any of the fields, and their location within the table could be anywhere. K|L|M|N|O|P OPB|OPC|IPC|OFA|OFB|810 IPA|OFE|IPD||876 OPA|OPD|IPE||878 IPB|IFE|IFD||804 The 3 digit alpha values would not be duplicated in $K$8:$O$26. *Sheet2*: I need to take the value of a cell (say R119 is OFE, although there will be a number of them throughout Sheet2) find a match in Sheet1 $K$8:$O$26 ($L$9) of the and return a corresponding value from Sheet1 $P$8:$P$26 ($P$9 - 876) that is on the same row as the Sheet2 R119 value. Vlookup is really the only function I have used up until now, but I tried to pull some formulas together after viewing the Contextures site (probably even some really creative, never before tried combinations :Bgr ) using Index, Match, Vlookup & derivatives of, but have not been successful. Unless my lookup value is in column K, which then returns my numeric value. Any insight would be greatly appreciate...my Excel VBA for dummies is on order at my local bookstore. I am not necessarily looking for VBA on this, just hoping a few functions will do the trick. Thanks~ -- Trixie ~TRIXIE ------------------------------------------------------------------------ Trixie's Profile: http://www.thecodecage.com/forumz/member.php?userid=438 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117444 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How best to...?
Trixie,
This seems to work okay - I'm assuming that all your numeric values are in column P.... =SUMPRODUCT((Sheet1!$K$8:$O$26=R119)*(Sheet1!$P$8: $P$26)) HTH, Bernie MS Excel MVP "Trixie" wrote in message ... Why am I thinking of work stuff on the weekend?!? I'm hoping someone out there has something to teach a newer user. This is a super group that seems to be very patient. *Sheet1*: I have a table that is 6x19 - $K$8:$P$26. There could be values in any of the fields, and their location within the table could be anywhere. K|L|M|N|O|P OPB|OPC|IPC|OFA|OFB|810 IPA|OFE|IPD||876 OPA|OPD|IPE||878 IPB|IFE|IFD||804 The 3 digit alpha values would not be duplicated in $K$8:$O$26. *Sheet2*: I need to take the value of a cell (say R119 is OFE, although there will be a number of them throughout Sheet2) find a match in Sheet1 $K$8:$O$26 ($L$9) of the and return a corresponding value from Sheet1 $P$8:$P$26 ($P$9 - 876) that is on the same row as the Sheet2 R119 value. Vlookup is really the only function I have used up until now, but I tried to pull some formulas together after viewing the Contextures site (probably even some really creative, never before tried combinations :Bgr ) using Index, Match, Vlookup & derivatives of, but have not been successful. Unless my lookup value is in column K, which then returns my numeric value. Any insight would be greatly appreciate...my Excel VBA for dummies is on order at my local bookstore. I am not necessarily looking for VBA on this, just hoping a few functions will do the trick. Thanks~ -- Trixie ~TRIXIE ------------------------------------------------------------------------ Trixie's Profile: http://www.thecodecage.com/forumz/member.php?userid=438 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117444 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How best to...?
Try this:
=SUMPRODUCT((K8:O26=Sheet2!R119)*P8:P26) -- Biff Microsoft Excel MVP "Trixie" wrote in message ... Why am I thinking of work stuff on the weekend?!? I'm hoping someone out there has something to teach a newer user. This is a super group that seems to be very patient. *Sheet1*: I have a table that is 6x19 - $K$8:$P$26. There could be values in any of the fields, and their location within the table could be anywhere. K|L|M|N|O|P OPB|OPC|IPC|OFA|OFB|810 IPA|OFE|IPD||876 OPA|OPD|IPE||878 IPB|IFE|IFD||804 The 3 digit alpha values would not be duplicated in $K$8:$O$26. *Sheet2*: I need to take the value of a cell (say R119 is OFE, although there will be a number of them throughout Sheet2) find a match in Sheet1 $K$8:$O$26 ($L$9) of the and return a corresponding value from Sheet1 $P$8:$P$26 ($P$9 - 876) that is on the same row as the Sheet2 R119 value. Vlookup is really the only function I have used up until now, but I tried to pull some formulas together after viewing the Contextures site (probably even some really creative, never before tried combinations :Bgr ) using Index, Match, Vlookup & derivatives of, but have not been successful. Unless my lookup value is in column K, which then returns my numeric value. Any insight would be greatly appreciate...my Excel VBA for dummies is on order at my local bookstore. I am not necessarily looking for VBA on this, just hoping a few functions will do the trick. Thanks~ -- Trixie ~TRIXIE ------------------------------------------------------------------------ Trixie's Profile: http://www.thecodecage.com/forumz/member.php?userid=438 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117444 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How best to...?
SWEEEEET!!! Thanks Bernie, this gave me exactly what I was looking for. I am now off to find out everything I can about the 'SUMPRODUCT' function so I know exactly what it is doing! It amazes me how you guys can figure this out in such a short time while I have struggled for DAYS with it. Guess that's why you're the MVP & get paid the big bucks Blink1 This place is the BEST...luv Thanks~ Bernie Deitrick;422211 Wrote: Trixie, This seems to work okay - I'm assuming that all your numeric values are in column P.... =SUMPRODUCT((Sheet1!$K$8:$O$26=R119)*(Sheet1!$P$8: $P$26)) HTH, Bernie MS Excel MVP "Trixie" wrote in message ... Why am I thinking of work stuff on the weekend?!? I'm hoping someone out there has something to teach a newer user. This is a super group that seems to be very patient. *Sheet1*: I have a table that is 6x19 - $K$8:$P$26. There could be values in any of the fields, and their location within the table could be anywhere. K|L|M|N|O|P OPB|OPC|IPC|OFA|OFB|810 IPA|OFE|IPD||876 OPA|OPD|IPE||878 IPB|IFE|IFD||804 The 3 digit alpha values would not be duplicated in $K$8:$O$26. *Sheet2*: I need to take the value of a cell (say R119 is OFE, although there will be a number of them throughout Sheet2) find a match in Sheet1 $K$8:$O$26 ($L$9) of the and return a corresponding value from Sheet1 $P$8:$P$26 ($P$9 - 876) that is on the same row as the Sheet2 R119 value. Vlookup is really the only function I have used up until now, but I tried to pull some formulas together after viewing the Contextures site (probably even some really creative, never before tried combinations :Bgr ) using Index, Match, Vlookup & derivatives of, but have not been successful. Unless my lookup value is in column K, which then returns my numeric value. Any insight would be greatly appreciate...my Excel VBA for dummies is on order at my local bookstore. I am not necessarily looking for VBA on this, just hoping a few functions will do the trick. Thanks~ -- Trixie ~TRIXIE ------------------------------------------------------------------------ Trixie's Profile: 'The Code Cage Forums - View Profile: Trixie' (http://www.thecodecage.com/forumz/member.php?userid=438) View this thread: 'How best to...? - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=117444) -- Trixie ~TRIXIE ------------------------------------------------------------------------ Trixie's Profile: http://www.thecodecage.com/forumz/member.php?userid=438 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117444 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How best to...?
Trixie
Bob Phillips explains sumproduct() in much more detail here.. http://www.xldynamic.com/source/xld.SUMPRODUCT.html If this post helps click Yes --------------- Jacob Skaria "Trixie" wrote: SWEEEEET!!! Thanks Bernie, this gave me exactly what I was looking for. I am now off to find out everything I can about the 'SUMPRODUCT' function so I know exactly what it is doing! It amazes me how you guys can figure this out in such a short time while I have struggled for DAYS with it. Guess that's why you're the MVP & get paid the big bucks Blink1 This place is the BEST...luv Thanks~ Bernie Deitrick;422211 Wrote: Trixie, This seems to work okay - I'm assuming that all your numeric values are in column P.... =SUMPRODUCT((Sheet1!$K$8:$O$26=R119)*(Sheet1!$P$8: $P$26)) HTH, Bernie MS Excel MVP "Trixie" wrote in message ... Why am I thinking of work stuff on the weekend?!? I'm hoping someone out there has something to teach a newer user. This is a super group that seems to be very patient. *Sheet1*: I have a table that is 6x19 - $K$8:$P$26. There could be values in any of the fields, and their location within the table could be anywhere. K|L|M|N|O|P OPB|OPC|IPC|OFA|OFB|810 IPA|OFE|IPD||876 OPA|OPD|IPE||878 IPB|IFE|IFD||804 The 3 digit alpha values would not be duplicated in $K$8:$O$26. *Sheet2*: I need to take the value of a cell (say R119 is OFE, although there will be a number of them throughout Sheet2) find a match in Sheet1 $K$8:$O$26 ($L$9) of the and return a corresponding value from Sheet1 $P$8:$P$26 ($P$9 - 876) that is on the same row as the Sheet2 R119 value. Vlookup is really the only function I have used up until now, but I tried to pull some formulas together after viewing the Contextures site (probably even some really creative, never before tried combinations :Bgr ) using Index, Match, Vlookup & derivatives of, but have not been successful. Unless my lookup value is in column K, which then returns my numeric value. Any insight would be greatly appreciate...my Excel VBA for dummies is on order at my local bookstore. I am not necessarily looking for VBA on this, just hoping a few functions will do the trick. Thanks~ -- Trixie ~TRIXIE ------------------------------------------------------------------------ Trixie's Profile: 'The Code Cage Forums - View Profile: Trixie' (http://www.thecodecage.com/forumz/member.php?userid=438) View this thread: 'How best to...? - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=117444) -- Trixie ~TRIXIE ------------------------------------------------------------------------ Trixie's Profile: http://www.thecodecage.com/forumz/member.php?userid=438 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117444 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How best to...?
Something seems to be wrong with your link... try it out.
-- Rick (MVP - Excel) "Jacob Skaria" wrote in message ... Trixie Bob Phillips explains sumproduct() in much more detail here.. http://www.xldynamic.com/source/xld.SUMPRODUCT.html If this post helps click Yes --------------- Jacob Skaria "Trixie" wrote: SWEEEEET!!! Thanks Bernie, this gave me exactly what I was looking for. I am now off to find out everything I can about the 'SUMPRODUCT' function so I know exactly what it is doing! It amazes me how you guys can figure this out in such a short time while I have struggled for DAYS with it. Guess that's why you're the MVP & get paid the big bucks Blink1 This place is the BEST...luv Thanks~ Bernie Deitrick;422211 Wrote: Trixie, This seems to work okay - I'm assuming that all your numeric values are in column P.... =SUMPRODUCT((Sheet1!$K$8:$O$26=R119)*(Sheet1!$P$8: $P$26)) HTH, Bernie MS Excel MVP "Trixie" wrote in message ... Why am I thinking of work stuff on the weekend?!? I'm hoping someone out there has something to teach a newer user. This is a super group that seems to be very patient. *Sheet1*: I have a table that is 6x19 - $K$8:$P$26. There could be values in any of the fields, and their location within the table could be anywhere. K|L|M|N|O|P OPB|OPC|IPC|OFA|OFB|810 IPA|OFE|IPD||876 OPA|OPD|IPE||878 IPB|IFE|IFD||804 The 3 digit alpha values would not be duplicated in $K$8:$O$26. *Sheet2*: I need to take the value of a cell (say R119 is OFE, although there will be a number of them throughout Sheet2) find a match in Sheet1 $K$8:$O$26 ($L$9) of the and return a corresponding value from Sheet1 $P$8:$P$26 ($P$9 - 876) that is on the same row as the Sheet2 R119 value. Vlookup is really the only function I have used up until now, but I tried to pull some formulas together after viewing the Contextures site (probably even some really creative, never before tried combinations :Bgr ) using Index, Match, Vlookup & derivatives of, but have not been successful. Unless my lookup value is in column K, which then returns my numeric value. Any insight would be greatly appreciate...my Excel VBA for dummies is on order at my local bookstore. I am not necessarily looking for VBA on this, just hoping a few functions will do the trick. Thanks~ -- Trixie ~TRIXIE ------------------------------------------------------------------------ Trixie's Profile: 'The Code Cage Forums - View Profile: Trixie' (http://www.thecodecage.com/forumz/member.php?userid=438) View this thread: 'How best to...? - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=117444) -- Trixie ~TRIXIE ------------------------------------------------------------------------ Trixie's Profile: http://www.thecodecage.com/forumz/member.php?userid=438 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117444 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How best to...?
Thanks Rick. Not sure thats been moved recently...
http://www.lqnet.com/Excel/sumproduct.aspx If this post helps click Yes --------------- Jacob Skaria "Rick Rothstein" wrote: Something seems to be wrong with your link... try it out. -- Rick (MVP - Excel) "Jacob Skaria" wrote in message ... Trixie Bob Phillips explains sumproduct() in much more detail here.. http://www.xldynamic.com/source/xld.SUMPRODUCT.html If this post helps click Yes --------------- Jacob Skaria "Trixie" wrote: SWEEEEET!!! Thanks Bernie, this gave me exactly what I was looking for. I am now off to find out everything I can about the 'SUMPRODUCT' function so I know exactly what it is doing! It amazes me how you guys can figure this out in such a short time while I have struggled for DAYS with it. Guess that's why you're the MVP & get paid the big bucks Blink1 This place is the BEST...luv Thanks~ Bernie Deitrick;422211 Wrote: Trixie, This seems to work okay - I'm assuming that all your numeric values are in column P.... =SUMPRODUCT((Sheet1!$K$8:$O$26=R119)*(Sheet1!$P$8: $P$26)) HTH, Bernie MS Excel MVP "Trixie" wrote in message ... Why am I thinking of work stuff on the weekend?!? I'm hoping someone out there has something to teach a newer user. This is a super group that seems to be very patient. *Sheet1*: I have a table that is 6x19 - $K$8:$P$26. There could be values in any of the fields, and their location within the table could be anywhere. K|L|M|N|O|P OPB|OPC|IPC|OFA|OFB|810 IPA|OFE|IPD||876 OPA|OPD|IPE||878 IPB|IFE|IFD||804 The 3 digit alpha values would not be duplicated in $K$8:$O$26. *Sheet2*: I need to take the value of a cell (say R119 is OFE, although there will be a number of them throughout Sheet2) find a match in Sheet1 $K$8:$O$26 ($L$9) of the and return a corresponding value from Sheet1 $P$8:$P$26 ($P$9 - 876) that is on the same row as the Sheet2 R119 value. Vlookup is really the only function I have used up until now, but I tried to pull some formulas together after viewing the Contextures site (probably even some really creative, never before tried combinations :Bgr ) using Index, Match, Vlookup & derivatives of, but have not been successful. Unless my lookup value is in column K, which then returns my numeric value. Any insight would be greatly appreciate...my Excel VBA for dummies is on order at my local bookstore. I am not necessarily looking for VBA on this, just hoping a few functions will do the trick. Thanks~ -- Trixie ~TRIXIE ------------------------------------------------------------------------ Trixie's Profile: 'The Code Cage Forums - View Profile: Trixie' (http://www.thecodecage.com/forumz/member.php?userid=438) View this thread: 'How best to...? - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=117444) -- Trixie ~TRIXIE ------------------------------------------------------------------------ Trixie's Profile: http://www.thecodecage.com/forumz/member.php?userid=438 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117444 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How best to...?
You guys are AWESOME!! Based on some research I did, including the link Jacob provided, I can understand what the formula Bernie posted does. I've learned a new function within Excel aha! I was further able to define the table ranges with names; again thanks to the folks here at the site. I tried to click the 'Thanks' button in a couple of places in this thread, but receive an error: Warning: array_merge() [function.array-merge]: Argument #2 is not an array in [path]/post_thanks.php on line 57 Please consider yourselves THANKED with much appreciation. -- Trixie ~TRIXIE ------------------------------------------------------------------------ Trixie's Profile: http://www.thecodecage.com/forumz/member.php?userid=438 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117444 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
