link workbooks

[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.(^_^)

 

[Guest]

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

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

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)

---

 

[Guest]

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:

 

[Guest]

=SUMIF(A!C:C;A2;A!B:B)
I think this will work better .


Fingerjob skrev:

 

[Guest]

What if it is a different workbook?

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:

 

[Guest]

=SUMIF([workbook1]A!C:C;A2;[workbook1]A!B:B)

Syahira skrev:

 

[Guest]

What if it is a different workbook?

Then ..

Put in B2:
=IF(A2="","",INDEX(A!B:B,MATCH(A2,A!C:C,0)))

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).

---