Best way to Link Workbooks

HunterN

Active Member
Joined
Mar 19, 2002
Messages
479
Hi All You Excel People,

Here is an example of my Master Workbook:
Most is column headings, the two numbers go under Project Number, I can have many projects. (It doesnt look good when I submit it!)

FY 2002 CUMULATIVE COST ANALYSIS
MONTH BRANCH BRANCH PROJECT PROJECT
NUMBER CHIEF NAME NUMBER
--------------------------------------------- 7501 0251
"

I can go in and enter the Project Number in each row using another worksheet that holds the project numbers. There is also seperate workbooks for each Project that contains the other information that I need (plus more).
I need a way to move down the worksheet thru the Project Number column and use this
Project Number to open its appropriate workbook and retrieve the values from the Wkb
and enter them in the cells then move down to the next Project.


Has anyone done anything similar to this?

Your help would be appreciated. Thanks
This message was edited by HunterN on 2002-04-23 07:21
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
On 2002-04-23 07:19, HunterN wrote:
Hi All You Excel People,

Here is an example of my Master Workbook:
Most is column headings, the two numbers go under Project Number, I can have many projects. (It doesnt look good when I submit it!)

FY 2002 CUMULATIVE COST ANALYSIS
MONTH BRANCH BRANCH PROJECT PROJECT
NUMBER CHIEF NAME NUMBER
--------------------------------------------- 7501 0251
"

I can go in and enter the Project Number in each row using another worksheet that holds the project numbers. There is also seperate workbooks for each Project that contains the other information that I need (plus more).
I need a way to move down the worksheet thru the Project Number column and use this
Project Number to open its appropriate workbook and retrieve the values from the Wkb
and enter them in the cells then move down to the next Project.


Has anyone done anything similar to this?

Your help would be appreciated. Thanks
This message was edited by HunterN on 2002-04-23 07:21

so you'd have two seperate workbooks called "7501" and "0251" which hold the information you need on those rows ?

etc etc etc ......

(if anyone picks up on this post after I've gone to bed here on GMT time, I'm thinking =INDIRECT AND =VLOOKUP)
 
Upvote 0
Hi,

Various workbooks are all named as the Project Number (ex: 0251.xls, 0751.xls, etc.). These workbooks contain information such as the Project Number Project Name, branch chief and alot of financial columns.

When I create the new Master Spreadsheet the rows are data being pulled in from each Project workbook. I need a way to open a Project wkbk and grab the data from the spreadsheet and move down one row on the master spreadsheet and open the next project.

the current formula is ='C:My Project
 
Upvote 0
Nancy,

Foolishly, I was going to suggest a simple solution using =INDIRECT and the normal =VLOOKUP that we all love

however, =INDIRECT only likes workbooks that are open and my guess is that you would like this to work even when you don't have all those project workbooks open

would I be right ?

:(
 
Upvote 0
yes, but we need to specify which workbook and be able to copy the formula down....

as the workbooks are different names, we need to use indirect, at which point we fail cos they may be closed

Nancy wants to use a project number to open a workbook with that number in the name

VLOOKUP will do, but you'd have to type it in each time.....

indirect would solve it, but not if the book's closed

I'm stumped on this one mate !!

:(
 
Upvote 0
On 2002-04-24 15:45, Chris Davison wrote:
yes, but we need to specify which workbook and be able to copy the formula down....

as the workbooks are different names, we need to use indirect, at which point we fail cos they may be closed

Nancy wants to use a project number to open a workbook with that number in the name

VLOOKUP will do, but you'd have to type it in each time.....

indirect would solve it, but not if the book's closed

I'm stumped on this one mate !!

/board/images/smiles/icon_frown.gif

Chris,

I shouldn't get involved again in this thread, but this must be doable.

Suppose that in the Main WB, I have a lookup value in A1 in sheet Collect and I want to look it up in A1 in Table1 in WB1.

Create a worksheet in Project1.xls, say BBoard, and

in A1 enter:

=[Main.xls]Collect!A1

in B1 enter:

=VLOOKUP(A1,Table1,2,match-type)

Now,

in B1 in Collect of Main enter:

=[Project1.xls]BBoard!B1

You can generalize this to N project files that can stay closed.

I proposed this scheme to circumvent the problem of not being able to use SUMIF formulas that refer to closed workbooks.

Note. This idea is copy-righted. It can be freely used as long as it's recognized as such. :biggrin:

Aladin
 
Upvote 0
Yeah, I'd been trying to use the values already in the list which gave rise to the respective workbooks needing to be accessed, without having to further type them in manually.

No problem with you stepping into the thread, at least the poster has the best way forward now.

:)

[You've probably spotted I don't like resorting to manual entries though ! There must be a way to use this existing value in a cell to link to it's respective worksheet. One for a rainy day maybe ?!]

thanks though, and copyright noted
:biggrin:
 
Upvote 0
This is a very common question.. i hope you can try code below.


Sub assignvalue()
Dim assign As String
Application.ScreenUpdating = False
Workbooks.Open Filename:="c:my documentsCallingFileName.xls"
Workbooks("CallingFileName").Activate
Worksheets("sheet1").Select
assign = Range("a9").Value
Workbooks("nameofyourbook").Activate
Worksheets("Sheet1").Select
Range(b1).Value = assign
Workbooks("CallingFileName").Close
Application.ScreenUpdating = True

End Sub


ni****h desai
 
Upvote 0
This is a very common question.. i hope you can try code below.


Sub assignvalue()
Dim assign As String
Application.ScreenUpdating = False
Workbooks.Open Filename:="c:my documentsCallingFileName.xls"
Workbooks("CallingFileName").Activate
Worksheets("sheet1").Select
assign = Range("a9").Value
Workbooks("nameofyourbook").Activate
Worksheets("Sheet1").Select
Range(b1).Value = assign
Workbooks("CallingFileName").Close
Application.ScreenUpdating = True

End Sub


ni****h desai
http://www.pexcel.com
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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