Manipulating ranges in a different excel file
Hello,
My excel file collects information from another excel file, where I have three columns: start_time, end_time, and another cell that is suppose to contain a name of the person. Within one range (3-250) I need to subtract end_time from start_time and see if it's less than 16 minutes, if so, if the third cell, does not contain the name, I need to count all the occurances and record the number into the cell of the correct file. I cannot quite figure out how to perform subtraction within the range (in this case: column G - column F): =SUMPRODUCT(--(('path[FileA.xls]Tab1'!G3:G250 [...???...] TIME(0,15,59))*(LEN('path[FileA.xls]Tab1'!Q3:Q250)=0))) Thanks a ton for your advice! |
Manipulating ranges in a different excel file
See my reply in microsoft.public.excel
In the future, please don't multi-post: you can address one message to multiple groups, and that will prevent confusion like this. Bernie "Lenchik" wrote in message ... Hello, My excel file collects information from another excel file, where I have three columns: start_time, end_time, and another cell that is suppose to contain a name of the person. Within one range (3-250) I need to subtract end_time from start_time and see if it's less than 16 minutes, if so, if the third cell, does not contain the name, I need to count all the occurances and record the number into the cell of the correct file. I cannot quite figure out how to perform subtraction within the range (in this case: column G - column F): =SUMPRODUCT(--(('path[FileA.xls]Tab1'!G3:G250 [...???...] TIME(0,15,59))*(LEN('path[FileA.xls]Tab1'!Q3:Q250)=0))) Thanks a ton for your advice! |
Manipulating ranges in a different excel file
Try it like this:
I'm assuming "path" is a placeholder. =SUMPRODUCT(--('path[FileA.xls]Tab1'!G3:G250-'path[FileA.xls]Tab1'!F3:F250TIME(0,15,59)),--('path[FileA.xls]Tab1'!Q3:Q250="")) -- Biff Microsoft Excel MVP "Lenchik" wrote in message ... Hello, My excel file collects information from another excel file, where I have three columns: start_time, end_time, and another cell that is suppose to contain a name of the person. Within one range (3-250) I need to subtract end_time from start_time and see if it's less than 16 minutes, if so, if the third cell, does not contain the name, I need to count all the occurances and record the number into the cell of the correct file. I cannot quite figure out how to perform subtraction within the range (in this case: column G - column F): =SUMPRODUCT(--(('path[FileA.xls]Tab1'!G3:G250 [...???...] TIME(0,15,59))*(LEN('path[FileA.xls]Tab1'!Q3:Q250)=0))) Thanks a ton for your advice! |
Manipulating ranges in a different excel file
Assuming your times are in Col A&B and Names in Col D
then in C1 enter =B1-A1 and copy down... then in the other file enter this in, say, A1 =SUMPRODUCT(--(('PATH[FileName.xls]TABNAME'!$C$1:$C100)<TIME(0,16,00)), --(('PATH[FileName.xls]TABNAME'!$D$1:$D$6)<"D1")) and copy down To get my email id paste my address in an Excel cell and press Enter... Adjust 100 to your range "Lenchik" wrote: Hello, My excel file collects information from another excel file, where I have three columns: start_time, end_time, and another cell that is suppose to contain a name of the person. Within one range (3-250) I need to subtract end_time from start_time and see if it's less than 16 minutes, if so, if the third cell, does not contain the name, I need to count all the occurances and record the number into the cell of the correct file. I cannot quite figure out how to perform subtraction within the range (in this case: column G - column F): =SUMPRODUCT(--(('path[FileA.xls]Tab1'!G3:G250 [...???...] TIME(0,15,59))*(LEN('path[FileA.xls]Tab1'!Q3:Q250)=0))) Thanks a ton for your advice! |
All times are GMT +1. The time now is 01:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com