Sumproduct Problem

[Andibevan]

Hi All,

I have a spreadsheet that documents details about files contained within a
document repository. There is a data sheet containing an audit history of
all actions that I run formulas against.

I use the following formula to successfully return the date when the last
file was checked in:-

={1*((MAX((File_Name=$B21)*(Val_Type="check in")*(Date_Val))))}
Where File_Name is a named range containing files names

Val_Type - contains actions stored as text

Date_Val - date of action

The range "Val_Folder" contains the file location.

How would I use sum product to return the File location for most recently
saved specific file.

I have been trying this but keep getting #Value:-

={((MAX((File_Name=$B21)*(Date_Val)*(Val_Folder))))}

Any ideas?

Andi

 

[Biff]

Hi!

Try this:

array entered:

=INDEX(Val_Folder,MATCH(MAX(IF(File_Name=$B21,Date_Val)),Date_Val,0))

Biff

 

[Andibevan]

Nice on Biff - am I right in thinking it is very difficult to get sumproduct
to return a text value (if not impossible?)?

 

[Biff]

Correct, not possible!

Biff

Nice on Biff - am I right in thinking it is very difficult to get
sumproduct
to return a text value (if not impossible?)?

 

[Andibevan]

There goes another half an hour I'll never get back