Avatar of developingprogrammer
developingprogrammer
 asked on

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

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
Microsoft Excel

Avatar of undefined
Last Comment
developingprogrammer

8/22/2022 - Mon
Korbus

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

ASKER
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 = (
barry houdini

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
developingprogrammer

ASKER
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!!
developingprogrammer

ASKER
oh, is it that sumproduct with 1 array means that array multiplied by 1 and summed?
developingprogrammer

ASKER
oh and what's the difference between Refresh All and Update Values? thanks!!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
barry houdini

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
developingprogrammer

ASKER
fantastic, thanks so much for your help barry!!
developingprogrammer

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