Max values from 2 ws and a range

[wgechter]

Attached is an excel file. There are two worksheets. In Worksheet 1 are 2 names with multiple dates and lab values. In Worksheet 2 is the name with a blank space for labs and dates. I need put in the blank lab and date fields on worksheet 2 the most recent date with associated lab value.

Thanks to Steveg on a very similar topic we are using the formula =SUMPRODUCT(--(Sheet1!$A$1:$A$12198=A1),--(Sheet1!$C$1:$C$12198=B1),(Sheet1!$F$2:$F$12198)) to get a value if there was only one to chose from on seperate worksheets. The only problem I am having with this formula in my old case, is that it is not working when the values are textual.

Thanks for the help,
Wendi

[Gary L Brown]

we don't attache or look at workbooks around here.
if you can't explain your issue...
break it down into individual questions and ask 1 question per issue
otherwise I can GUARENTEE you that you will not get an answer

--
Gary Brown



"wgechter" wrote:

>
> Attached is an excel file. There are two worksheets. In Worksheet 1
> are 2 names with multiple dates and lab values. In Worksheet 2 is the
> name with a blank space for labs and dates. I need put in the blank
> lab and date fields on worksheet 2 the most recent date with associated
> lab value.
>
> Thanks to Steveg on a very similar topic we are using the formula
> =SUMPRODUCT(--(Sheet1!$A$1:$A$12198=A1),--(Sheet1!$C$1:$C$12198=B1),(Sheet1!$F$2:$F$12198))
> to get a value if there was only one to chose from on seperate
> worksheets. The only problem I am having with this formula in my old
> case, is that it is not working when the values are textual.
>
> Thanks for the help,
> Wendi
>
>
> +-------------------------------------------------------------------+
> |Filename: Forum help.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=4272 |
> +-------------------------------------------------------------------+
>
> --
> wgechter
> ------------------------------------------------------------------------
> wgechter's Profile: http://www.excelforum.com/member.php...o&userid=30799
> View this thread: http://www.excelforum.com/showthread...hreadid=505535
>
>

[Morrigan]

See attachment


Hope it helps

we don't attache or look at workbooks around here.
if you can't explain your issue...
break it down into individual questions and ask 1 question per issue
otherwise I can GUARENTEE you that you will not get an answer

--
Gary Brown



"wgechter" wrote:

>
> Attached is an excel file. There are two worksheets. In Worksheet 1
> are 2 names with multiple dates and lab values. In Worksheet 2 is the
> name with a blank space for labs and dates. I need put in the blank
> lab and date fields on worksheet 2 the most recent date with associated
> lab value.
>
> Thanks to Steveg on a very similar topic we are using the formula
> =SUMPRODUCT(--(Sheet1!$A$1:$A$12198=A1),--(Sheet1!$C$1:$C$12198=B1),(Sheet1!$F$2:$F$12198))
> to get a value if there was only one to chose from on seperate
> worksheets. The only problem I am having with this formula in my old
> case, is that it is not working when the values are textual.
>
> Thanks for the help,
> Wendi
>
>
> +-------------------------------------------------------------------+
> |Filename: Forum help.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=4272 |
> +-------------------------------------------------------------------+
>
> --
> wgechter
> ------------------------------------------------------------------------
> wgechter's Profile: http://www.excelforum.com/member.php...o&userid=30799
> View this thread: http://www.excelforum.com/showthread...hreadid=505535
>
>

[wgechter]

Thanks for the help Morrigan unfortunately the formula assumes that I know the most recent date. In my real spreadsheet I have thousands of patients with 1 to 20 different dates and I want the formula to be able to find the most recent date of that one patient and their associated value. Does that make sense? Thanks for helping, Wendi

[Gary L Brown]

Morrigan just took a VERY courageous move by opening a file from someone he
COULD NOT TRUST!!! Please recognize his RISK by giving him a 'Yes' to his
helpful post.!!!
Morrigan, you've just risked/lost one of your nine lives. Hope your LUCK
continues!
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"wgechter" wrote:

>
> Thanks for the help Morrigan unfortunately the formula assumes that I
> know the most recent date. In my real spreadsheet I have thousands of
> patients with 1 to 20 different dates and I want the formula to be able
> to find the most recent date of that one patient and their associated
> value. Does that make sense? Thanks for helping, Wendi
>
>
> --
> wgechter
> ------------------------------------------------------------------------
> wgechter's Profile: http://www.excelforum.com/member.php...o&userid=30799
> View this thread: http://www.excelforum.com/showthread...hreadid=505535
>
>

[Morrigan]

I think you lost me.

The formula in column E finds the most recent date for each patient and outputs it. The formula in colum D searches for the corresponding value and outputs. If that's not what you are looking for, explain more.

Thanks for the help Morrigan unfortunately the formula assumes that I know the most recent date. In my real spreadsheet I have thousands of patients with 1 to 20 different dates and I want the formula to be able to find the most recent date of that one patient and their associated value. Does that make sense? Thanks for helping, Wendi

[wgechter]

Morrigan, yes that is exactly what I was looking for. I should have looked more closely. Thanks for the response!! At least someone is very helpful!!