| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
link workbooks
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 a 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.(^_^) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
link workbooks
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) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Syahira" wrote: 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 a 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.(^_^) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
link workbooks
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: 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) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Syahira" wrote: 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 a 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.(^_^) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
link workbooks
=SUMIF(A!C:C;A2;A!B:B)
I think this will work better . :-) Fingerjob skrev: 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: 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) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Syahira" wrote: 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 a 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.(^_^) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
link workbooks
What if it is a different workbook?
"Fingerjob" wrote: 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: 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) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Syahira" wrote: 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 a 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.(^_^) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
link workbooks
=SUMIF([workbook1]A!C:C;A2;[workbook1]A!B:B)
Syahira skrev: What if it is a different workbook? "Fingerjob" wrote: 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: 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) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Syahira" wrote: 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 a 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.(^_^) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
link workbooks
"Syahira" wrote:
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). -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I merge and link workbooks into one summary workbook? | Excel Worksheet Functions | |||
| how do I link cells between two different two different workbooks. | Excel Worksheet Functions | |||
| Can you link many workbooks to one template? And Update? | Excel Discussion (Misc queries) | |||
| Variable Link to Workbooks | Excel Worksheet Functions | |||
| How to Link named ranges from multiple Workbooks into a single Wo. | Excel Discussion (Misc queries) | |||
Linear Mode
