| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Max Date
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)) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Max Date
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 --------------- Jacob Skaria "Tufail" wrote: 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)) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Max Date
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. Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Tufail" wrote: 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)) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Max Date
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 "Tufail" wrote: 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. Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Tufail" wrote: 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)) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Max Date
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 :( ----------------------------------------------------------------------------------------------- "Francis" wrote: 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 "Tufail" wrote: 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. Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Tufail" wrote: 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)) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Max Date
|
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Max Date
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 --------------- Jacob Skaria "Tufail" wrote: 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 :( ----------------------------------------------------------------------------------------------- "Francis" wrote: 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 "Tufail" wrote: 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. Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Tufail" wrote: 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)) |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Max Date
one way, try this in C2
=LOOKUP(2,1/('Sheet2'!$H$2:$H$10000=$B2),'Sheet2'!$F$2:$F$1000 0) 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 "Tufail" wrote: 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 :( ----------------------------------------------------------------------------------------------- "Francis" wrote: 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 "Tufail" wrote: 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. Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Tufail" wrote: 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)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Given a date, how do I get the 1st date and the last date of theprevious month? | Excel Worksheet Functions | |||
| Making a date go red, if date passes todays date. | Excel Worksheet Functions | |||
| how to get the random date between the start date and the end date? | Excel Worksheet Functions | |||
| Report Date - Date Recv = Days Late, but how to rid completed date | Excel Worksheet Functions | |||
| Date updates from worksheet to chart & changes date to a date series! Help!! | Charts and Charting in Excel | |||
Linear Mode
