Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
COURSE of the MONTH
14 days, 8 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
formula referencing another workbook gives #value! error when other workbook is closed
Posted on 2012-12-30
hey guys, i've got a consolidation workbook and many child workbooks. i've linked them up using a countif formula. everything works fine when the child workbooks are open. when the childworkbooks are closed and i try to Update Values via the below, i get #value! error. why am i getting this and why is it when my child workbooks are open they update just fine?
guys and what is the difference between refresh all and update values?
here are the consolidation workbook and one of the child workbooks attached
Consol-Test.xlsFreddie-PAR-Checklist.xls
guys and what is the difference between refresh all and update values?
here are the consolidation workbook and one of the child workbooks attached
Consol-Test.xlsFreddie-PAR-Checklist.xls
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
6
2
9 Comments
I found this answer: http://www.access-programmers.co.uk/forums/showthread.php?t=104582
which basically says you want to put the full path in the file refence:
COUNTIF('[closed_excel_fil
-->
COUNTIF('[C:\USers\icc\des
Your first screen shot makes me think this shouldnn't be neccessary, but guess it's worth a shot. (I have not tested this myself)
which basically says you want to put the full path in the file refence:
COUNTIF('[closed_excel_fil
-->
COUNTIF('[C:\USers\icc\des
Your first screen shot makes me think this shouldnn't be neccessary, but guess it's worth a shot. (I have not tested this myself)
Thanks korbus, it already is the full path name. If the child workbook is opened it will not show the path name. If the workbook is closed then excel will automatically show the full path name. I didn't type in the path, I used = and then clicked on the range in the child workbook. So unfortunately this doesn't solve the problem = (
COUNTIF function doesn't work with closed workbooks -
See here for advice from microsoft - they suggest an "array formula" which is another alternative, but SUMPRODUCT doesn't need CTRL+SHIFT+ENTER......so try using SUMPRODUCT like this
=SUMPRODUCT(('[Freddie-PAR
As you say, you can use that version without the path, if the child worbook is open (but that will still work when that workbook is closed)
regards, barry
See here for advice from microsoft - they suggest an "array formula" which is another alternative, but SUMPRODUCT doesn't need CTRL+SHIFT+ENTER......so try using SUMPRODUCT like this
=SUMPRODUCT(('[Freddie-PAR
As you say, you can use that version without the path, if the child worbook is open (but that will still work when that workbook is closed)
regards, barry
whao barry, the formula works like a charm! thanks so much!!
if you don't mind could you explain how the formula works? i read up on sumproduct and it being the summation of the product of the arrays, but the way you're using the formula only has 1 array and you're adding 0.
here's what i understand so far:
('[Freddie-PAR Checklist.xls]STP_PTE'!$A$
+ 0 --------> you're adding 0 to convert boolean to number
sumproduct(......) -------------> here's where i'm a little lost. you've only got 1 array so how come it gives the right result? could you share barry? thanks so much!!
if you don't mind could you explain how the formula works? i read up on sumproduct and it being the summation of the product of the arrays, but the way you're using the formula only has 1 array and you're adding 0.
here's what i understand so far:
('[Freddie-PAR Checklist.xls]STP_PTE'!$A$
+ 0 --------> you're adding 0 to convert boolean to number
sumproduct(......) -------------> here's where i'm a little lost. you've only got 1 array so how come it gives the right result? could you share barry? thanks so much!!
Sorry for the delay, it was my bedtime here.....
You only really need SUM, i.e. this will work
=SUM(('[Freddie-PAR Checklist.xls]STP_PTE'!$A$
You compare each value against C4 which gives you an "array" of TRUE/FALSE values and +0 is a "coercer", converting TRUE/FALSE to 1/0, (you could use *1 or others) then SUM sums the 1s giving a count of cells in your range that meet the condition
......but that formula is an "array formula", i.e. it needs to be confirmed with CTRL+SHIFT+ENTER to get the correct result (and you see { and } around the formula)
using SUMPRODUCT means that you don't need CSE. As you say there is nothing to multiply here - when you use SUMPRODUCT with a single array or range SUMPRODUCT just sums the values in that array or range just like SUM, i.e. =SUMPRODUCT({2,3,4}) =9
regards, barry
You only really need SUM, i.e. this will work
=SUM(('[Freddie-PAR Checklist.xls]STP_PTE'!$A$
You compare each value against C4 which gives you an "array" of TRUE/FALSE values and +0 is a "coercer", converting TRUE/FALSE to 1/0, (you could use *1 or others) then SUM sums the 1s giving a count of cells in your range that meet the condition
......but that formula is an "array formula", i.e. it needs to be confirmed with CTRL+SHIFT+ENTER to get the correct result (and you see { and } around the formula)
using SUMPRODUCT means that you don't need CSE. As you say there is nothing to multiply here - when you use SUMPRODUCT with a single array or range SUMPRODUCT just sums the values in that array or range just like SUM, i.e. =SUMPRODUCT({2,3,4}) =9
regards, barry
Featured Post
Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Introduction
This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results.
Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Suggested Courses
Course of the Month14 days, 8 hours left to enroll
771 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.