Crisis in Closed/Open Files

juzzthefuzz

New Member
Joined
Mar 4, 2009
Messages
4
Im making a sort of program in excel that can count a number of chips from certain tables in a casino night. There are 7 different files which I need data from for the 8th file which then sends data the other 7 files again.
When all the files are open, the LOOKUP function works fine as the LOOKUP link location for one of the files which are open. Yet when the files are closed, I get a #REF! error and I dont know what to do.

Please help!!1!!!

Juzzthefuzz
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Hi
we need more details of what you lookup and from where. You can lookup from closed files but you can't push data into them.
ravi
 
Upvote 0
Its not pushing any data atall.
Im using the lookup function.
One file uses a sheet to take data from all files and the rest take data from that one file.
Its using LOOKUP 2 way in effect.
such as in one cell it will be
=SUM(40+(LOOKUP(A2,'J:\[Poker 1.xls]Sheet1'!$A$2:$A$60:'J:\[Poker 1.xls]Sheet1'!$G$2:$G$60))+LOOKUP(A2,'J:\[Poker 2.xls]Sheet1'!$A$2:$A$60:'J:\[Poker 2.xls]Sheet1'!$G$2:$G$60)+LOOKUP(A2,'J:\[Texas 1.xls]Sheet1'!$A$2:$A$60:'J:\[Texas 1.xls]Sheet1'!$G$2:$G$60)+LOOKUP(A2,'J:\[Texas 2.xls]Sheet1'!$A$2:$A$60:'J:\[Texas 2.xls]Sheet1'!$G$2:$G$60)+LOOKUP(A2,'J:\[Roulette.xls]Sheet1'!$A$2:$A$60:'J:\[Roulette.xls]Sheet1'!$G$2:$G$60)+LOOKUP(A2,'J:\[Blackjack 1.xls]Sheet1'!$A$2:$A$60:'J:\[Blackjack 1.xls]Sheet1'!$G$2:$G$60)+LOOKUP(A2,'J:\[Blackjack 2.xls]Sheet1'!$A$2:$A$60:'J:\[Blackjack 2.xls]Sheet1'!$G$2:$G$60))

and in the other smaller files it'll be
=LOOKUP(A2,'[Casino Template (version 1).xls]Person ID'!$A$2:$A$60:'[Casino Template (version 1).xls]Person ID'!$D$2:$D$60)

Any Ideas?
Thanks
 
Upvote 0
Well, I don't know if this is the problem, but your range references are far overcomplicated

This

=LOOKUP(A2,'[Casino Template (version 1).xls]Person ID'!$A$2:$A$60:'[Casino Template (version 1).xls]Person ID'!$D$2:$D$60)

can be changed to this

=LOOKUP(A2,'[Casino Template (version 1).xls]Person ID'!$A$2:$D$60)

You don't need to specify drive/book/sheet on both the Left and Right columns of the table...

Looks like you have that setup on SEVERAL ranges in the formula...
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top