link workbooks

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Let say I have 2 wookbooks.
WB A
Branch Name No. of participant Instructor
Nebula 5 Ellis
Pentagon 4 Winnie
Maine 6 Kenny
Corne 7 Toby

WB B
Instructor Name No. of participant Fee
Toby 7 840
Kenny 6 720
Ellis 5 600
Winnie 4 480

My questions are:
1) How can I link, let say the number of participant from WB A to WB B and
make sure that it is link together with instructor name.
2) If I use to just type"=" in WB B in number of participants colunm and
link it to WB A in the same column, when I sort out WB A, it get totally
wrong, how can I fix this?

Thank you for helping me.(^_^)
 
One way is via INDEX / MATCH ..

Assuming the source table below is in sheet: A, within A1:C5
Branch Name No. of participant Instructor
Nebula 5 Ellis
Pentagon 4 Winnie
Maine 6 Kenny
Corne 7 Toby
Click to expand...

and this table is in sheet: B, within A1:C5
Instructor Name No. of participant Fee
Toby 7 840
Kenny 6 720
Ellis 5 600
Winnie 4 480
Click to expand...

Put in B2:
=IF(A2="","",INDEX(A!B:B,MATCH(A2,A!C:C,0)))
Copy down as far as required

(For simplicity, I assumed you mean sheets rather than books)

---
 
Or(assume the same placement as MAX)
Write in B2 sheet B
=SUMIF(B!C:C;A2;B!B:B)
Copy down as far as required

Best regards

Petter Bøhler


Max skrev:
 
What if it is a different workbook?

Fingerjob said:
Or(assume the same placement as MAX)
Write in B2 sheet B
=SUMIF(B!C:C;A2;B!B:B)
Copy down as far as required

Best regards

Petter Bøhler


Max skrev:
Click to expand...
 
Syahira said:
What if it is a different workbook?
Click to expand...

Then ..
Put in B2:
=IF(A2="","",INDEX(A!B:B,MATCH(A2,A!C:C,0)))
Click to expand...

would become:
=IF(A2="","",INDEX([A.xls]Sheet1!B:B,MATCH(A2,[A.xls]Sheet1!C:C,0)))

assuming the source table is in Sheet1 in A.xls
and A.xls is simultaneously open

If A.xls is closed, Excel will then place the full path of A.xls into the
formula, which becomes inevitably much longer. You can see this by closing
A.xls after effecting the formula above with A.xls open. It's much simpler to
try and keep things all within one workbook (can be in different sheets).

---
 
Back
Top