Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct problem | Excel Worksheet Functions | |||
Problem using sumproduct | Excel Worksheet Functions | |||
Problem with sumproduct and month=1 | Excel Worksheet Functions | |||
Another Sumproduct & #N/A problem | Excel Worksheet Functions | |||
SUMPRODUCT problem | Excel Worksheet Functions |