Hi there!
Recently I have enter into working industry for a short while and my supervisor wants me to help her troubleshoot a problem which she can't solve. Sadly, I am a complete idiot at excel as I never study it before.
Here is the problem, she want to grab a specific data from a Excel document A to Excel document B and count the number of time the data is shown. Let me put an example,
Excel Document A is the source file and Excel Document B is the destination file. Inititally, she used COUNTIF to do that but it failed (that's why she asked me to troubleshoot). So I went to check on the Internet and found out that SUMPRODUCT can do the same as COUNTIF. From what I read, SUMPRODUCT is able to auto update the data in the destination file (without opening it) when the source file is being updated. (Need clarification on this!)
Hence, I went to try it. The only problem I am facing is that the data in the closed destination file is not updating by itself when I updated the source data. The only time it will update is when either both the documents are open or when I manually F2 and hit enter on the data.
This is my formula,
=SUMPRODUCT(--('C:\Documents and Settings\TestSubject07\Desktop\TestDummy\[Dummy Idiot Source.xlsx]Sheet1'!$C$5:$C$19=C6))
I am using Excel 2007 too. Really need advise and help on this, thanks alot!
Note: Feel free to ask me more questions, I will provide a screenshot or the file itself if possible too.
Recently I have enter into working industry for a short while and my supervisor wants me to help her troubleshoot a problem which she can't solve. Sadly, I am a complete idiot at excel as I never study it before.
Here is the problem, she want to grab a specific data from a Excel document A to Excel document B and count the number of time the data is shown. Let me put an example,
Excel Document A is the source file and Excel Document B is the destination file. Inititally, she used COUNTIF to do that but it failed (that's why she asked me to troubleshoot). So I went to check on the Internet and found out that SUMPRODUCT can do the same as COUNTIF. From what I read, SUMPRODUCT is able to auto update the data in the destination file (without opening it) when the source file is being updated. (Need clarification on this!)
Hence, I went to try it. The only problem I am facing is that the data in the closed destination file is not updating by itself when I updated the source data. The only time it will update is when either both the documents are open or when I manually F2 and hit enter on the data.
This is my formula,
=SUMPRODUCT(--('C:\Documents and Settings\TestSubject07\Desktop\TestDummy\[Dummy Idiot Source.xlsx]Sheet1'!$C$5:$C$19=C6))
I am using Excel 2007 too. Really need advise and help on this, thanks alot!
Note: Feel free to ask me more questions, I will provide a screenshot or the file itself if possible too.
