Forum home » Delegate support and help forum » Microsoft Excel Training and help » Formulas using various workbooks.
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Formulas using various workbooks.
Resolved · Low Priority · Version 2007
Reply Thu 23 Jun 2011, 11:34Delegate Megan said...
Megan has attended:
Excel Advanced course
Formulas using various workbooks.
Hi,
I have a workbook which uses SUMIF functions to sum data from another workbook.
Unfortunately the formulas only work if both workbooks are open, otherwise i get a #VALUE result.
Is there any way I can set it up so that the other workbook does not have to be open?
For upcoming training course dates see: Pricing & availability
RE: Formulas using various workbooks.
Hi Megan
Thanks for your question. Could you clarify something for me?
When you open the workbook, are you prompted to update the links to the external workbook?
Thanks
Stephen
Reply Fri 24 Jun 2011, 14:11Delegate Megan said...
RE: Formulas using various workbooks.
Hi Stephen,
No there isn't any prompts.
Thanks,
RE: Formulas using various workbooks.
Hi Megan
Thanks for your reply
The first thing to check are the security settings on Excel
Click on the Office Button and go to Options. Click on Trust Centre and select External Content. Under Security Settings For workbook links, the second option "Prompt user....", should be selected. I suspect that in your case the third option which disables all links might be active. If this is true select the second option, close all the files and reopen
If this is not the case, or if it doesn't work please get back to me
regards
Stephen
Reply Wed 29 Jun 2011, 16:51
Edited on Wed 29 Jun 2011, 16:52Delegate Megan said...
RE: Formulas using various workbooks.
Hi Stephen,
Thankyou for your reply,
Unfortunately my settings were already setup like you indicated and so it is still not working. even though it is set up to prompt I still do not get the prompts either.
Any other ideas?
Kind regards,
Megan
RE: Formulas using various workbooks.
Hi Megan
Thanks for your question
I have carried out a little research and discovered several references that say that sumif only works if the referenced workbook is open.
I was not aware of this until now.
I am continuing to look at the problem to understand
1. Why this is the case
2. If there is a way round it
If I learn anything of interest or use, then I will get back to you
Regards
Stephen
Reply Thu 30 Jun 2011, 09:57Delegate Megan said...
RE: Formulas using various workbooks.
Thank you
RE: Formulas using various workbooks.
Hi Megan
Just a follow up. I can confirm that SUMIF like SUMIFs, COUNTIF, COUNTIFS can not be used with closed target files.
The reasons are apparently highly technical and are caused by the structure of the algorithm.
It might be possible to get the same effect with complex nested IF AND and OR functions, but without seeing the data, it is difficult to be precise
Regards
Stephen
Reply Fri 1 Jul 2011, 10:12Delegate Megan said...
RE: Formulas using various workbooks.
Thankyou stephen,
I will look at the data again and see if I can do it another way.
Thankyou for your help.
|