formulas using various workbooks

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Formulas using various workbooks.

Formulas using various workbooks.

resolvedResolved · Low Priority · Version 2007

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?

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

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

Edited on Wed 29 Jun 2011, 16:52

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

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

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.


 

Excel tip:

Add Text to Displayed Numbers in Excel 2010

To 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!

View all Excel hints and tips


Server loaded in 0.08 secs.