formula in one file and data in another file.

[lezawang]

Hi
I want to write a formula =sum() and select cells from different file as an argument to this function.
Lets say I have the formula in a file called ABC.xlsx and the data in another file called XYZ.xlsx.
I noticed that to do that I need to open both file in the same excel session (program) otherwise I wont be able to select cells from XYZ file. For example if I opened ABC.xlsx and then went to File-->open-->XYZ.xlsx
then it will work, means I can select cells from XYZ
But If i click on Excel program again to open a new session. One session has ABC and another session has XYZ
then I would be able to select cells from XYZ. Is that by design? Thank you.

 

[FDibbins]

Not really sure what you are asking here?
Do you mean you need to have both files open to construct the formula?
(if you know the cell/range refs, no you don't, but is simpler because the formula will then also pick up file paths)

Or doi you mean you need to have both files open for the calc to work properly?
(with a simple =SUM() of SUMIF() function, no you don't, but with SUMIFS() you do)

If that was not what you meant, try explaining again please?

 

[lezawang]

Thanks for your reply. No what I meant is that. I have the formula in one file called ABC. Now I want to select cells that are in another file lets say XYZ.
I have Excel Icon on my desktop. I double click on Excel icon and open Excel then go File-->open-->ABC. Now I double click on Excel icon again and open File-->Open-->XYZ. Now I have 2 excel program opened and each one has a file, one ABC and another one is XYZ. in this case, if I type =sum( in ABC and try to select cells in XYZ, I wont be able to select cells. So I close XYZ. Now I have one file open which is ABC. I click on File-->Open-->XYZ. Now I have 2 files open but in the same Excel window. Now I type in ABC =sum( and go to XYZ and select cells, then it will work. Thank you.

 

[FDibbins]

OK so why no just do it the way that works for you, why make things difficult lol?