Re: If And Match With a MAX and Offset
- From: "dolivastro@xxxxxxxxx" <dolivastro@xxxxxxxxx>
- Date: 18 Oct 2006 12:40:44 -0700
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
hansjhamm@xxxxxxxxx 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
dolivastro@xxxxxxxxx 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
hansjhamm@xxxxxxxxx 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
.
- Follow-Ups:
- Re: If And Match With a MAX and Offset
- From: hansjhamm@xxxxxxxxx
- Re: If And Match With a MAX and Offset
- References:
- If And Match With a MAX and Offset
- From: hansjhamm@xxxxxxxxx
- Re: If And Match With a MAX and Offset
- From: dolivastro@xxxxxxxxx
- Re: If And Match With a MAX and Offset
- From: hansjhamm@xxxxxxxxx
- If And Match With a MAX and Offset
- Prev by Date: Re: Go to and play
- Next by Date: Re: formula : =(column A)+(column B)-(column C). Why won't it work?
- Previous by thread: Re: If And Match With a MAX and Offset
- Next by thread: Re: If And Match With a MAX and Offset
- Index(es):
Relevant Pages
|
