| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If And Match With a MAX and Offset
I have looked and tried various posts ideas, but can't quite get this
working. The formula is: =IF(AND(MATCH(C3,'[Endcap Comments.xls]Rep Comments'!$D:$D,0),MAX('[Endcap Comments.xls]Rep Comments'!$H:$H)),OFFSET('[Endcap Comments.xls]Rep Comments'!$A:$A,0,9),"") What I am expecting it to do is: IF the match from c3 and D:D is true AND the MAX is true for the previous match then offset to give me the resulting data. It is returning data but not what is offset by 0 rows and 9 columns. It seems to be just random... Thanks Hans |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If And Match With a MAX and Offset
I can't quite make out what you want, but one suggestion is this: Match
and Max do not return true / false, which is what AND wants for arguments. In both the case of Match and Max, however, the return will be evaluated as either true or false, and that may explain why it looks random. Hope this gets you started. Dom wrote: I have looked and tried various posts ideas, but can't quite get this working. The formula is: =IF(AND(MATCH(C3,'[Endcap Comments.xls]Rep Comments'!$D:$D,0),MAX('[Endcap Comments.xls]Rep Comments'!$H:$H)),OFFSET('[Endcap Comments.xls]Rep Comments'!$A:$A,0,9),"") What I am expecting it to do is: IF the match from c3 and D:D is true AND the MAX is true for the previous match then offset to give me the resulting data. It is returning data but not what is offset by 0 rows and 9 columns. It seems to be just random... Thanks Hans |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If And Match With a MAX and Offset
Hi Hans: I have 2 suggestions, but you may not like either of them.
1. Move the whole thing over to Access. What you want is more in the nature of a database than a spreadsheet. 2. Keep it in Excel, but write a VBA function that does exactly what you want, instead of using a worksheet function. I can help with the function, but you need to know your way about the VB Editor. Dom wrote: Dom, This is frustrating to say the least. I just read somewhere in a post the same thing you stated, so let me give it another go. In Sheet1 I have the Store#'s in column C. I need a formula to look in Sheet2 and find the same store number in column D (Store Numbers can exist multiple times, hence the MAX), then look in Sheet2 column H and IF the date is the MAX date for that store then look in column J for the data I need returned to Sheet1 column E. Does this make any sense? Thanks Hans wrote: I can't quite make out what you want, but one suggestion is this: Match and Max do not return true / false, which is what AND wants for arguments. In both the case of Match and Max, however, the return will be evaluated as either true or false, and that may explain why it looks random. Hope this gets you started. Dom wrote: I have looked and tried various posts ideas, but can't quite get this working. The formula is: =IF(AND(MATCH(C3,'[Endcap Comments.xls]Rep Comments'!$D:$D,0),MAX('[Endcap Comments.xls]Rep Comments'!$H:$H)),OFFSET('[Endcap Comments.xls]Rep Comments'!$A:$A,0,9),"") What I am expecting it to do is: IF the match from c3 and D:D is true AND the MAX is true for the previous match then offset to give me the resulting data. It is returning data but not what is offset by 0 rows and 9 columns. It seems to be just random... Thanks Hans |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If And Match With a MAX and Offset
Dom,
Typically I use formulas to work the data and once I can confirm that I am getting the correct data...I start converting to VBA...I know it is the LONG way around, but still fairly new to the VBA game...So any help you could give me will be appreciated! Hey, if you can help me to get it to work I will drink a beer and say a toast to you! Hans wrote: Hi Hans: I have 2 suggestions, but you may not like either of them. 1. Move the whole thing over to Access. What you want is more in the nature of a database than a spreadsheet. 2. Keep it in Excel, but write a VBA function that does exactly what you want, instead of using a worksheet function. I can help with the function, but you need to know your way about the VB Editor. Dom wrote: Dom, This is frustrating to say the least. I just read somewhere in a post the same thing you stated, so let me give it another go. In Sheet1 I have the Store#'s in column C. I need a formula to look in Sheet2 and find the same store number in column D (Store Numbers can exist multiple times, hence the MAX), then look in Sheet2 column H and IF the date is the MAX date for that store then look in column J for the data I need returned to Sheet1 column E. Does this make any sense? Thanks Hans wrote: I can't quite make out what you want, but one suggestion is this: Match and Max do not return true / false, which is what AND wants for arguments. In both the case of Match and Max, however, the return will be evaluated as either true or false, and that may explain why it looks random. Hope this gets you started. Dom wrote: I have looked and tried various posts ideas, but can't quite get this working. The formula is: =IF(AND(MATCH(C3,'[Endcap Comments.xls]Rep Comments'!$D:$D,0),MAX('[Endcap Comments.xls]Rep Comments'!$H:$H)),OFFSET('[Endcap Comments.xls]Rep Comments'!$A:$A,0,9),"") What I am expecting it to do is: IF the match from c3 and D:D is true AND the MAX is true for the previous match then offset to give me the resulting data. It is returning data but not what is offset by 0 rows and 9 columns. It seems to be just random... Thanks Hans |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If And Match With a MAX and Offset
Here it is: It seems to work on this side, anyway.
The call is, for example, =MyFunc(C9,Sheet2!$D$11:$J$18). What the arguments refer to should be obvious. You put the formula in the cell you want the return. -------------------------------------------------- Public Function MyFunc(StoreID As Variant, r As Range) As Variant Const p_StoreCol As Long = 1 Const p_DateCol As Long = 5 Const p_ReturnCol As Long = 7 Dim FRow As Long Dim LRow As Long Dim i As Long Dim T_Return As Variant Dim T_Date As Date Dim T_Caught As Boolean FRow = 1 LRow = r.Rows.Count T_Caught = False For i = FRow To LRow If (r.Cells(i, p_StoreCol).Value = StoreID) Then If (r.Cells(i, p_DateCol).Value T_Date) Then T_Caught = True T_Date = r.Cells(i, p_DateCol).Value T_Return = r.Cells(i, p_ReturnCol).Value End If End If Next i MyFunc = "n/a" If (T_Caught) Then MyFunc = T_Return End Function --------------------------------------------------------- Hope this helps, Dom wrote: Dom, Typically I use formulas to work the data and once I can confirm that I am getting the correct data...I start converting to VBA...I know it is the LONG way around, but still fairly new to the VBA game...So any help you could give me will be appreciated! Hey, if you can help me to get it to work I will drink a beer and say a toast to you! Hans wrote: Hi Hans: I have 2 suggestions, but you may not like either of them. 1. Move the whole thing over to Access. What you want is more in the nature of a database than a spreadsheet. 2. Keep it in Excel, but write a VBA function that does exactly what you want, instead of using a worksheet function. I can help with the function, but you need to know your way about the VB Editor. Dom wrote: Dom, This is frustrating to say the least. I just read somewhere in a post the same thing you stated, so let me give it another go. In Sheet1 I have the Store#'s in column C. I need a formula to look in Sheet2 and find the same store number in column D (Store Numbers can exist multiple times, hence the MAX), then look in Sheet2 column H and IF the date is the MAX date for that store then look in column J for the data I need returned to Sheet1 column E. Does this make any sense? Thanks Hans wrote: I can't quite make out what you want, but one suggestion is this: Match and Max do not return true / false, which is what AND wants for arguments. In both the case of Match and Max, however, the return will be evaluated as either true or false, and that may explain why it looks random. Hope this gets you started. Dom wrote: I have looked and tried various posts ideas, but can't quite get this working. The formula is: =IF(AND(MATCH(C3,'[Endcap Comments.xls]Rep Comments'!$D:$D,0),MAX('[Endcap Comments.xls]Rep Comments'!$H:$H)),OFFSET('[Endcap Comments.xls]Rep Comments'!$A:$A,0,9),"") What I am expecting it to do is: IF the match from c3 and D:D is true AND the MAX is true for the previous match then offset to give me the resulting data. It is returning data but not what is offset by 0 rows and 9 columns. It seems to be just random... Thanks Hans |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If And Match With a MAX and Offset
Hans,
Here's a solution for you (not the most elegant, but it works): =SUM(IF(Sheet2!$H$3:$H$5000=MAX(IF(C3=Sheet2!$D$3: $D$5000,Sheet2!$H$3:$H$5000)),IF(C3=Sheet2!$D$3:$D $5000,Sheet2!$JI$3:$J$5000))) You have to include references to rows - in my example it's 3 and 5000. This is an array formula, so you must press CTRL+SHIFT+ENTER to add braces {} around it. If you have multiple entries for the same store for the same Max date, they will be added. To select Min or Max from the multiple entries, change SUM accordingly; don't forget CTRL+SHIFT+ENTER. " wrote: Dom, This is frustrating to say the least. I just read somewhere in a post the same thing you stated, so let me give it another go. In Sheet1 I have the Store#'s in column C. I need a formula to look in Sheet2 and find the same store number in column D (Store Numbers can exist multiple times, hence the MAX), then look in Sheet2 column H and IF the date is the MAX date for that store then look in column J for the data I need returned to Sheet1 column E. Does this make any sense? Thanks Hans wrote: I can't quite make out what you want, but one suggestion is this: Match and Max do not return true / false, which is what AND wants for arguments. In both the case of Match and Max, however, the return will be evaluated as either true or false, and that may explain why it looks random. Hope this gets you started. Dom wrote: I have looked and tried various posts ideas, but can't quite get this working. The formula is: =IF(AND(MATCH(C3,'[Endcap Comments.xls]Rep Comments'!$D:$D,0),MAX('[Endcap Comments.xls]Rep Comments'!$H:$H)),OFFSET('[Endcap Comments.xls]Rep Comments'!$A:$A,0,9),"") What I am expecting it to do is: IF the match from c3 and D:D is true AND the MAX is true for the previous match then offset to give me the resulting data. It is returning data but not what is offset by 0 rows and 9 columns. It seems to be just random... Thanks Hans |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If And Match With a MAX and Offset
Hey Guys,
Thanks for the help...long time in responding, got pulled into another project all of a sudden and did not get back to this for a few days. Anyway, I will give a shout back once I can and ket you know how it turned out... Again Thanks!!! Hans txilya wrote: Hans, Here's a solution for you (not the most elegant, but it works): =SUM(IF(Sheet2!$H$3:$H$5000=MAX(IF(C3=Sheet2!$D$3: $D$5000,Sheet2!$H$3:$H$5000)),IF(C3=Sheet2!$D$3:$D $5000,Sheet2!$JI$3:$J$5000))) You have to include references to rows - in my example it's 3 and 5000. This is an array formula, so you must press CTRL+SHIFT+ENTER to add braces {} around it. If you have multiple entries for the same store for the same Max date, they will be added. To select Min or Max from the multiple entries, change SUM accordingly; don't forget CTRL+SHIFT+ENTER. " wrote: Dom, This is frustrating to say the least. I just read somewhere in a post the same thing you stated, so let me give it another go. In Sheet1 I have the Store#'s in column C. I need a formula to look in Sheet2 and find the same store number in column D (Store Numbers can exist multiple times, hence the MAX), then look in Sheet2 column H and IF the date is the MAX date for that store then look in column J for the data I need returned to Sheet1 column E. Does this make any sense? Thanks Hans wrote: I can't quite make out what you want, but one suggestion is this: Match and Max do not return true / false, which is what AND wants for arguments. In both the case of Match and Max, however, the return will be evaluated as either true or false, and that may explain why it looks random. Hope this gets you started. Dom wrote: I have looked and tried various posts ideas, but can't quite get this working. The formula is: =IF(AND(MATCH(C3,'[Endcap Comments.xls]Rep Comments'!$D:$D,0),MAX('[Endcap Comments.xls]Rep Comments'!$H:$H)),OFFSET('[Endcap Comments.xls]Rep Comments'!$A:$A,0,9),"") What I am expecting it to do is: IF the match from c3 and D:D is true AND the MAX is true for the previous match then offset to give me the resulting data. It is returning data but not what is offset by 0 rows and 9 columns. It seems to be just random... Thanks Hans |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
