Sumproduct Problem
 

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

Hi!

Try this:

array entered:

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

Biff

"Andibevan" wrote in message
...
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

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

"Biff" wrote in message
...
Hi!

Try this:

array entered:

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

Biff

"Andibevan" wrote in message
...
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

Correct, not possible!

Biff

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

"Biff" wrote in message
...
Hi!

Try this:

array entered:

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

Biff

"Andibevan" wrote in message
...
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

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

"Biff" wrote in message
...
Correct, not possible!

Biff

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

"Biff" wrote in message
...
Hi!

Try this:

array entered:

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

Biff

"Andibevan" wrote in message
...
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