• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to return last value in a column from another workbook?

F

FriendlyJoe

New Member
Thanks in advance for any help given, I am far from an Excel expert so any help at all is appreciated.


Onto the problem:

Let's say I have two workbooks Blue and Green. Green holds a running count of data entries with their entry number (102,103,104 etc). I would like Blue to be able to reference the last entry number on Green (and then add 1 to it).


Things to note:

Green will most likely NOT be open when Blue is pulled up. I've read that may cause problems.

The last entry number on Green will be changing constantly so I would like it to be "looked-up" each time Blue is opened.


I've tried using =Index but keep getting errors. I assume its because I'm trying to reference the workbook via the filesystem and the context is screwy. I've seen some people saying to use VLookup but that code makes my eyes cross.


Right now what I have is:

=INDEX('C:CC.xls'!B:B,MATCH(9.99999999999999E+307,'C:CC.xls'!B:B))+1

I totally copied that from a website and tried to insert the file reference but no luck so far.


Again, thanks for any help you can offer!
 
Hi Joe ,


I am not sure about what formula to use , but the correct syntax for referring to a cell or range address is to use the file name ( workbook name ) and the worksheet name ; you need to use :


=INDEX('C:[CC.xls]Green'!B:B,MATCH(9.99999999999999E+307,'C:[CC.xls]Green'!B:B))+1


Narayan
 
Aha! Those silly syntax errors get me every time! Still getting the error but maybe now its the 'right' one haha.


Thanks Nara!
 
Whoa, timeout. Nara's code actually worked!


You are the man NARA! THANK YOU SO MUCH!


Now for tweaking and implementation!


Thank you a million times over!
 
I have another question in case anyone is still looking at this thread. (trying not to make another one so quickly)


If I were to email this file (Blue) to someone, will it record the reference and save it as a value rather than a formula? Stated plainly, will they see 109 or #REF! (or some other error)?


I tried this just now and it appeared to work properly (emailed it to a coworker who doesn't have access to the referenced file "green") but I'm wondering if this is the way its intended to function or can I expect problems down the road?
 
Hi Joe ,


I tried out the following :


1. I copied your formula , linking it to an external file on the computer. It displayed the correct value.


2. I renamed the external file.


3. I opened the file which had the externally linked formula ; when the prompt came whether to update links or not , I clicked on OK , and I got an error message that some links could not be updated.


I think you should face the same problem when the file is opened on a remote computer where the linked file ( Green ) is not available.


Narayan
 
You must log in or register to reply here.
Back
Top