ChooseCols Function to a different file, troubleshooting

[ellison]

Hi,

Hi, I'm trying to use the CHOOSECOLS function in Excel, selecting info from a different file. But the array only returns expected results when the "target" file is open..
If the target file is closed, it returns a bunch of zeros....

Preview-1 = the file:

choose-column-array-formula.xlsx
	A
1	0
Sheet1

Cell Formulas
Range	Formula
A1:B6	A1	=CHOOSECOLS('K:\Formul&Instructions\Mr-Excel-questions\033-choose-column-external-array\[dummy-info.xlsx]Sheet1'!$A$1:$E$6,1,2)
Dynamic array formulas.

Preview-2: the table of the results when the target file is closed:

0	0
0	0
0	0
0	0
0	0
0	0

Preview-3: the table of the results when the target file is open:

H-1	H-2
alpha	one
beta	two
charlie	three
delta	four
echo	five

Things I've tried checking:
-only 1 instance of excel is open - yes
-both files are open & closed via Excel rather than windows explorer - yes
-the trust center settings for the location are a yes - yes
-the cells are not locked - yes


I've got feeling it may be something to do with my Excel settings as opposed to the actual choosecols function?!

Also did a windows update this week & I'm spotting this warning message coming up which I haven't often seen before:

Any idea what I can check next?

 

[Fluff]

I don't think choosecols works with closed files, but if you only wan the 1st 2 columns you can just use

='K:\Formul&Instructions\Mr-Excel-questions\033-choose-column-external-array\[dummy-info.xlsx]Sheet1'!$A$1:$B$6

 

[ellison]

Crumbs I didn't reaslist that, I've been cheerfully littering that new choosecols function around all day until I spotted a couple of gremlins..............!
Thanks coming back so quickly Mr Fluff, much appreciated

 

[Fluff]

You're welcome & thanks for the feedback.