Max Date

T

Tufail

hello all,
i am trying to get max date in "Sheet1" from sheet "Sal&Pmt" but #VALUE!
erroe is coming out, any proper way ?

=IF($B5="","",MAX('Sal&Pmt'!F7:F9996,'Sal&Pmt'!H7:H9996)) << upto here
showing, but when i am trying to match data with =$B5 (Sheet1) then problam
is start.

=IF($B5="","",MAX('Sal&Pmt'!F7:F9996,'Sal&Pmt'!H7:H9996=$B5))
 
J

Jacob Skaria

If you are trying to get the maximum from f7:f9996, h7:h996 and B5 below is
the formula...

=IF($B5="","",MAX('Sal&Pmt'!F7:F9996,'Sal&Pmt'!H7:H9996,$B5))

If this post helps click Yes
 
T

Tufail

thanks for your quick post, but actually what i want, in Sal&Pmt list
H7:H9996" i have country name (Japan USA etc.etc) same i have in Sheet1 so i
want B5~ equal same country name max date from Sal&Pmt list, hope i could
explain well, that's why i am using =B5 instid of , B5.
 
F

Francis

am not following you very well,
would you post a sample on how both sheets look like
and what you want to achieve?
It help us not to guess what you are after.
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another
 
T

Tufail

oh sorry ! i try again here :

"Sal&Pmt" Sheet
Col_F Col_H
01-Jan-2009 Japan
02-Jan-2009 Singapore
03-Jan-2009 Japan
20-Feb-2009 Singapore

Sheet1
Col_B Col_C (formula cell)
Japan here actually i need max date of above sheet as a 03-Jan-2009
Singapore here actually i need max date of above sheet as a 20-Feb-2009
USA
UK

Sorry in advance if i still couldn't explained well :(
-----------------------------------------------------------------------------------------------
 
D

Don Guillett

This is an ARRAY formula that must be entered using ctrl+shift+enter.
Assumes col F is real dates.
=MAX(IF((h1:h4="Japan"),F1:F4))
 
J

Jacob Skaria

Suppose in cell Sheet1 B1 you have the country . The below formula will
return the highest date.. Copy the formula and format the cell to date...

=SUMPRODUCT(MAX(('Sal&Pmt'!H1:H100=C1)*('Sal&Pmt'!F1:F100)))


If this post helps click Yes
 
F

Francis

one way, try this in C2
=LOOKUP(2,1/('Sheet2'!$H$2:$H$10000=$B2),'Sheet2'!$F$2:$F$10000)
and copy down

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another
 
Top