98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Formulas using various workbooks.
Formulas using various workbooks.
Resolved · Low Priority · Version 2007
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?
Fri 24 Jun 2011, 14:06Trainer Stephen said...
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
Fri 24 Jun 2011, 14:11Delegate Megan said...
RE: Formulas using various workbooks.
Hi Stephen,
No there isn't any prompts.
Thanks,
Wed 29 Jun 2011, 09:25Trainer Stephen said...
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
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
Thu 30 Jun 2011, 09:49Trainer Stephen said...
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
Thu 30 Jun 2011, 09:57Delegate Megan said...
RE: Formulas using various workbooks.
Thank you
Fri 1 Jul 2011, 10:10Trainer Stephen said...
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
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. |
Excel tip:Add Text to Displayed Numbers in Excel 2010To add text to a number in a cell, you need to go to the Home tab on the Ribbon, and click on the Cells group. Select Format Cells from the drop down menu then Custom from the Category list. In the Type box select General. After the word General, enter a space, then opening quotation marks, then the word you want to type and then closing quotation marks. Click on OK and you have your text! |
