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
Solved

formula referencing another workbook gives #value! error when other workbook is closed

Posted on 2012-12-30
9
360 Views
Last Modified: 2013-01-14
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?

update values
guys and what is the difference between refresh all and update values?refresh all
here are the consolidation workbook and one of the child workbooks attached
Consol-Test.xlsFreddie-PAR-Checklist.xls
0
Comment
Question by:developingprogrammer
  • 6
  • 2
9 Comments
 
LVL 10

Expert Comment

by:Korbus
ID: 38731191
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_filename.xls]
-->
COUNTIF('[C:\USers\icc\destop\par\closed_excel_filename.xls]

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)
0
 

Author Comment

by:developingprogrammer
ID: 38731214
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 = (
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 38731275
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 Checklist.xls]STP_PTE'!$A$10:$A$65536=C$4)+0)

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
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:developingprogrammer
ID: 38731736
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$10:$A$65536=C$4) ------> this part you're using a boolean comparison. since true = 1 and false = 0, we have a number for true

+ 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!!
0
 

Author Comment

by:developingprogrammer
ID: 38731738
oh, is it that sumproduct with 1 array means that array multiplied by 1 and summed?
0
 

Author Comment

by:developingprogrammer
ID: 38731740
oh and what's the difference between Refresh All and Update Values? thanks!!
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 38732336
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$10:$A$65536=C$4)+0)

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
0
 

Author Comment

by:developingprogrammer
ID: 38777139
fantastic, thanks so much for your help barry!!
0
 

Author Comment

by:developingprogrammer
ID: 38777141
and so sorry for my late reply too!!! you're super prompt and i really appreciate barry!! = ))) and great formula by the way!!! = ))
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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 will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question