Best way to Link Workbooks

[HunterN]

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 ]

[Chris Davison]

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)

[HunterN]

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

[Chris Davison]

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 ?

[Jack in the UK]

Chris Vlookup can look inside a closed workbook can it not?

[Chris Davison]

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 !!

[Aladin Akyurek]

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 !!

[img]/board/images/smiles/icon_frown.gif[/img]

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.

Aladin

[Chris Davison]

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

[nisht]

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


nishith desai

[nisht]

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


nishith desai
http://www.pexcel.com