Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max values from 2 ws and a range
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max values from 2 ws and a range
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max values from 2 ws and a range
See attachment Hope it helps Gary L Brown Wrote: 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 +-------------------------------------------------------------------+ |Filename: Forum help-R1.zip | |Download: http://www.excelforum.com/attachment.php?postid=4273 | +-------------------------------------------------------------------+ -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=505535 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max values from 2 ws and a range
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max values from 2 ws and a range
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. Isn't that how you want? 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 -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=505535 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max values from 2 ws and a range
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!! -- wgechter ------------------------------------------------------------------------ wgechter's Profile: http://www.excelforum.com/member.php...o&userid=30799 View this thread: http://www.excelforum.com/showthread...hreadid=505535 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Last 2 values in range | Excel Discussion (Misc queries) | |||
How many values appear more than once in a range? | Excel Worksheet Functions | |||
Checking ALL values in a range | Excel Discussion (Misc queries) | |||
How to move Y-axis values when X range is -a to +b | Charts and Charting in Excel | |||
Can you average data in 1 column based on a range of values in another? | Excel Worksheet Functions |