Pulling "MIN" Data From Another Sheet (Error Detection)

[PinkFloydEffect]

This community is always so helpful, hoping you can pull another one off for me

I have attached the workbook with the goal of pulling information from the sheet "CallTek" into the "Daily Tickets Report" sheet cell D6. Currently I am using the following function: =(CallTek!P2)

This works only if I presort the data on the sheet "CallTek" so that the oldest ticket date is first in cell. The problem is if the reference cell is null than it displays "0" when I want it to display "N/A"

Someone on this site provided a function that works in the other rows in column D - only if they are in a date form like #/#/## however if the reference cell says "# days ago" it breaks the function. This function provided was: =IF(COUNT(SheetName!M:M)=0,"N/A",MIN(SheetName!M:M))

Goal: Display "# of days ago" or "N/A" if reference cell is null -AND- if possible pull the oldest value such as "2-months ago" over "2-days ago"

[menem]

I'm not sure what you want, but you may try LOOKUP function

=LOOKUP(2,1/(CallTek!$J:$J=G6),CallTek!D:D)

Regards.

[PinkFloydEffect]

I'm not sure what you want, but you may try LOOKUP function

=LOOKUP(2,1/(CallTek!$J:$J=G6),CallTek!D:D)

Regards.

This does appear to work, however I do not want the hash symbol before the N/A so maybe it would require an additional argument to display a string when #N/A is returned?

[wk9128]

No #N/A is returned

How about this

Please Login or Register  to view this content.

[menem]

If you don't want # sign before #N/A

please try

=IF(ISNA(LOOKUP(2,1/(CallTek!$J:$J=G6),CallTek!D:D)),"N/A",LOOKUP(2,1/(CallTek!$J:$J=G6),CallTek!D:D))
above formula will show N/A when it get #N/A error, if any others error occured it will show that error value.

or

=IFError(LOOKUP(2,1/(CallTek!$J:$J=G6),CallTek!D:D),"N/A")
above formula will show N/A every time it get error

Regards.

[PinkFloydEffect]

Another user (JeteMc) ended up providing a solution for both the N/A and the date format which works!

=IF(B7=0,"N/A",TODAY()-LEFT(CallTek!Z2,SEARCH(" ",CallTek!Z2)))

[menem]

Completed all answer.
Please verify.

D2
=AGGREGATE(15,6,FreshDesk!$C:$C/(1/(FreshDesk!$B$1:$B$1000="Open")),1)

D6
=IF(I6=TODAY(),"N/A",I6)
need helper cells. I6
=TODAY()-IFERROR(AGGREGATE(14,6,VALUE(LEFT(CallTek!$D$1:$D$1000,FIND(" d",CallTek!$D$1:$D$1000)))/(1/(CallTek!$J$1:$J$1000=$H6)),1),0)

D14
=IFERROR(INDEX(Kentrone!$G:$G,MATCH($H14,Kentrone!$J:$J,0)),"N/A")

D21
=IFERROR(AGGREGATE(15,6,VALUE(LEFT(CRM!$F$1:$F$1000,FIND(",",CRM!$F$1:$F$1000)-1))/(1/(CRM!$D$1:$D$1000=$A21)),1),"")

Regards.

Note : I've limited data area only row 1 to 1000 for speed up of calculation.