Return Most current Date

[Johnboy]

Hi All,

Sheet 1 contains data for a one year
Column A = Date
Column B = Unique Customer ID
Column R = Transaction ID

On Sheet 2
Column A = Customer ID
Column B = I would like to have the most current date returned by Transaction ID (Sheet1 Column R) if equals QQA

Thanks in advance
Johnboy

 

[Aladin Akyurek]

Hi All,

Sheet 1 contains data for a one year
Column A = Date
Column B = Unique Customer ID
Column R = Transaction ID

On Sheet 2
Column A = Customer ID
Column B = I would like to have the most current date returned by Transaction ID (Sheet1 Column R) if equals QQA

Thanks in advance
Johnboy

Looks like you want...

Sheet2, B2, control+shift+enter, not just enter:

=MAX(IF(Sheet1!$B$2:$B$100=A2,IF(Sheet1!$R$2:$R$100="QQA",Sheet1!$A$2:$A$100)))

 

[Johnboy]

Thanks Aladin... Works Great

Johnboy

 

[Aladin Akyurek]

Thanks Aladin... Works Great

Johnboy

You are welcome. Thanks for providing feedback.

 

[Johnboy]

Ok ran into small problem, When inserting a row on Sheet1 changes range values on sheet2. Tried the following
{=MAX(INDIRECT(IF(Log!C7:C5000=A3,INDIRECT(IF(Log!R7:R5000="BPO",INDIRECT(Log!A7:A5000))))))}

With no luck, even played with different variations of INDIRECT.

Johnboy

 

[Aladin Akyurek]

Ok ran into small problem, When inserting a row on Sheet1 changes range values on sheet2. Tried the following
{=MAX(INDIRECT(IF(Log!C7:C5000=A3,INDIRECT(IF(Log!R7:R5000="BPO",INDIRECT(Log!A7:A5000))))))}

With no luck, even played with different variations of INDIRECT.

Johnboy

If you want to lock references the formula must process, try...

=MAX(IF(INDIRECT("Log!C7:C5000")=A3,IF(INDIRECT("Log!R7:R5000")="BPO",INDIRECT("Log!A7:A5000"))))

 

[Johnboy]

Thanks again Aladin, Forgot the quotes for the references. Most awesome

Johnboy

 

[Aladin Akyurek]

Thanks again Aladin, Forgot the quotes for the references. Most awesome

Johnboy

You are welcome.