# Cell reference use to link to different workbook.

1. ## Cell reference use to link to different workbook.

Apologies if this is a stupid question.

I have 40 workbooks, each of which contains the leave for an employee.

I have one workbook that "scrapes" data from everyone's leave book and presents them in one easy to read sheet. Now I can get it to scrape quite cheerfully but instead of me having to define the cell and workbook it goes lookign for I'd like it to use another cell to get the name from.

ie At the moment a cell in the "overview of leave" workbook is called

='[Smith J.xlsx]Current'!\$B\$2

But instead of me having to type "Smith J" i'd like to just reference cell A1 (say) in teh overview workbook - which would contain the name!

I've tried using INDIRECT but I failed because I am too stupid.

2. ## Re: Cell reference use to link to different workbook.

The following works for me:

Formula:

With "Smith J" in A1

3. ## Re: Cell reference use to link to different workbook.

Thanks so much (off to try it!)

4. ## Re: Cell reference use to link to different workbook.

And, of course, the "Smith J" workbook needs to be open as well as the summary workbook.

Let us know if it works for you.

5. ## Re: Cell reference use to link to different workbook.

No for some reason despite EVERY website telling me that INDIRECT wouldn't work if the other workbooks are closed I thought it would work differently for me.

So I don't know what I was doing wrong before but I broke down the formula, put individual terms in to individual cells then combined them with INDIRECT. I was very pleased with myself when it worked. (I have never understood why sometimes I have to put in the ' symbol. Seemingly at random. But it seems I randomly put them in the right place.)

So to restate teh problem, I have 40 leavebooks that I want to scrape data from, is there any way to do this without either typing the formula in long hand ie

='[Smith J.xlsx]Current'!\$B\$2
='[Bloggs S.xlsx]Current'!\$B\$2

etc

I mean it doesn't take too long but there is more data I would like other than just B2. I have already spent way more time trying to automate the process than I would ever spend just typing them out. But there's a principle* at stake here, not to mention the joy of grabbing that little handle and watching the cells autofill. One of life's great pleasures.

*I don't know what that principle is, but I suspect that it is quite a stupid
one.

6. ## Re: Cell reference use to link to different workbook.

I did something similar at work a couple days ago. I think this should do it:
=MID(CELL("filename",A1),SEARCH("[",CELL("filename",A1))+1,SEARCH(".",CELL("filename",A1))-1-SEARCH("[",CELL("filename",A1)))

Select a cell on the target workbook substitute each of the "A1" (4). I did a row for each of the worksheets I needed (I pulled data from multiple worksheets rather than workbooks), then did the indirect functions into adjacent cells on the row.

-----------
Perhaps if you input the file path instead of "indirect" it will work with the target workbook(s) closed (I certainly want to try that).

7. ## Re: Cell reference use to link to different workbook.

Intentionally Blank: Yes, the referenced workbooks need to be open. I think that logically makes sense - how can Excel get the referenced data if the workbook is not open?

I have never understood why sometimes I have to put in the ' symbol. Seemingly at random.
Excel wraps the pathname/filename in single quotes if and only if it contains spaces or other special characters. Adding the apostrophes even if there are no special characters in the path/filename will always work.

I'm not exactly sure what the residual problem is at this point. Is it that it is tedious to open dozens of employee files? Could you have separate worksheets for each employee rather than separate workbooks? If you can't do that then it would be possible to, say, automatically open all workbooks in a particular folder, but I think all of these types of solutions involve VBA so you'd need a macro enabled workbook. Another "half way" option: The standard Excel "Open" dialog allows you to select multiple files to open (hold CTRL or SHIFT).

8. ## Re: Cell reference use to link to different workbook.

Just in case I haven't grasped your problem fully, here's three workbooks that I think address what you want:

Smith J.xlsx is the leave book for J Smith

Bloggs S.xlsx is the leave book for J Smith

OverviewOfLeave.xlsx is the "scraped" summary of leave for all employees. I also added a "Job title" column to show how this would work for additional data beyond "Leave" without the need to re-type employee names. B2:C2 can be simply copied down to cover all 40 employees.

All three workbooks need to reside in the same folder (that can be changed however you require)

Let me know if I'm missing something.

9. ## Re: Cell reference use to link to different workbook.

That's exactly what I'm trying to do but you're right in your earlier post that the reference files need to be open.

If i just type in the names (or "click" them in) then this isn't the case.

(So to be clear if a cell is " =INDIRECT("'["&\$A2&".xlsx]Current'!c\$2") " then it works if the destination is open. IF the cell is " ='C:\Users\Pete\Downloads\New folder\[Smith J.xlsx]Current'!\$C\$2 " then the source workbook doesn't needto be open, but I have to change the name in the path by hand. (ie I have to change "Smith J" to "Bloggs S").

10. ## Re: Cell reference use to link to different workbook.

OK, now I understand I think. If you only have the Overview file open then any references to closed employee-specific files that use INDIRECT show as #REF! rather than the value from the employee-specific file.

Unfortunately there is no simple solution. There are VBA and add-in based possibilities to at least investigate. For example, see the following thread: http://www.excelforum.com/excel-gene...nal-files.html. Although the final #13 post won't work in your case, see posts #3, #4, #7,#9. I have not as yet checked out any of these suggestions, but it's something to look into.

11. ## Re: Cell reference use to link to different workbook.

On a closer look I didn't get much of value from my reference in post #10 above. I'm looking at creating a VBA solution.

12. ## Re: Cell reference use to link to different workbook.

Here is a VBA "solution". I have created a User Defined Function (UDF) called "PullClosedData". It takes 2 parameters:
1) a file name (no path and no extension) like: "Smith J"
2) a string in the form of a reference like: "B2:B2". This should be a single cell reference into the file noted in the first parameter. The UDF will return the contents of this cell.

Attached is an updated OverviewOfLeave.xlsm that utilizes this UDF and the previously uploaded two example employee files.

YOU WILL NEED TO CHANGE THE "Pathname" CONSTANT AT THE TOP OF THE VBA MODULE TO REFLECT THE LOCATION OF THE EMPLOYEE FILES ON YOUR SYSTEM - and, of course enable macros.

I say "solution" in quotes because in the background each use of the UDF not only opens and closes the appropriate employee file it also needs to create then close a whole new instance of Excel (without this new instance of Excel UDFs cannot open a closed workbook). As a result this is going to be very very slow.

Here's the VBA: it's rough and ready, it would need further work if you want to pursue this approach.

Finally - can't you instead consolidate your 40 employee workbooks into 40 worksheets within the Overview workbook. You could then use INDIRECT and all these problems would then go away!

13. ## Re: Cell reference use to link to different workbook.

Geoff that's really helpful. I genuinely thought it must be something that I was doing wrong.

Teh "overview" file isn't really for me but for my boss - so it's more important that it works smoothly than the length of time it takes me to compose the underlying cells. As such I'll just do it "long hand". That doesn't mean this hasn't been a very useful exercise though and thanks a lot for your help.

14. ## Re: Cell reference use to link to different workbook.

Thanks. no problem. I learned a lot too!

And thanks for the rep!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1