Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date function to find most recent order
i have a calculations page that references a client page for information. i
also use an indirect formula so i only have to change one reference # on my sheet for the mose recent info. here is a formula that works now: =SUMPRODUCT(--(INDIRECT("CLIENT!$I$2:$I"&$O$1)="SUNBELT"),--(INDIRECT("CLIENT!$Q$2:$Q"&$O$1))) i tried to use these 2 formulas to find the most recent date a client has ordered the client name for this example is homeside This is what i tried to adapt from a previous posting answer: =MAX(IF--(INDIRECT("CLIENT!$I$2:$I"&$O$1)="HOMESIDE"),--(INDIRECT("CLIENT!$E$2:$E"&$O$1))) the response to this formula is #name? ... so i think i am close, but have never tried to use the max(if function and am not familiar w/ it finally i tried to simplify it some and came up w/ this: =MAX(IF("CLIENT!$I$2:$I$2345"="HOMESIDE","CLIENT!$ E$1:$E$")) the response to this one is 1/0/1900 finally the original posting was answered that i need to use and array ctrl + shift + enter (i have no clue why this works but on the client page only.. not on the summary page |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date function to find most recent order
You don't need a double unary in this equation
=MAX(IF(INDIRECT("CLIENT!$I$2:$I"&$O$1)="HOMESIDE" ),(INDIRECT("CLIENT!$E$2:$ E"&$O$1))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "floridasurfn" wrote in message ... i have a calculations page that references a client page for information. i also use an indirect formula so i only have to change one reference # on my sheet for the mose recent info. here is a formula that works now: =SUMPRODUCT(--(INDIRECT("CLIENT!$I$2:$I"&$O$1)="SUNBELT"),--(INDIRECT("CLIEN T!$Q$2:$Q"&$O$1))) i tried to use these 2 formulas to find the most recent date a client has ordered the client name for this example is homeside This is what i tried to adapt from a previous posting answer: =MAX(IF--(INDIRECT("CLIENT!$I$2:$I"&$O$1)="HOMESIDE"),--(INDIRECT("CLIENT!$E $2:$E"&$O$1))) the response to this formula is #name? ... so i think i am close, but have never tried to use the max(if function and am not familiar w/ it finally i tried to simplify it some and came up w/ this: =MAX(IF("CLIENT!$I$2:$I$2345"="HOMESIDE","CLIENT!$ E$1:$E$")) the response to this one is 1/0/1900 finally the original posting was answered that i need to use and array ctrl + shift + enter (i have no clue why this works but on the client page only.. not on the summary page |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help to find a date (latest date) from a column | Excel Worksheet Functions | |||
Need help to find a date (latest date) from a column | Excel Worksheet Functions | |||
I NEED A FUNCTION TO FIND IF THERE IS AN INTERSECTION BETWEEN 2 DATE RANGES | Excel Worksheet Functions | |||
Date Function | Excel Discussion (Misc queries) | |||
Calculating days between current date and a date in future NETWORKDAYS() function | Excel Worksheet Functions |