xXcarl86Xx
New Member
- Joined
- Sep 21, 2015
- Messages
- 1
Hi,
I am looking for a way to populate a table in excel using another table that is populated manually and a table used for a lookup function.
When the user enters a number (Priority Level) into the “summary table”, I would like excel to look at the corresponding skill (Tax, Bens etc) and Agent Name, and populate the output table in the format shown (Skill Number “No.”, Skill Name “Name”, Priority Level “Pri.”) for each skill within the corresponding skill group (which can be anywhere from 1 skill to 10 skills). Excel will need to repeat this for every skill with a number in the summary table (multiple skills per agent).
Sheet 1 (Summary Table)
<tbody>
</tbody>
Sheet 2 (Skill Lookup)
<tbody>
</tbody>
Sheet 1 (Output Table)
<tbody>
</tbody>
I imagine that VBA will need to be used but have no idea where to start with VBA and have exhausted all of my excel knowledge in trying to get the output I need using formulas.
Thanks in advance,
Carl
I am looking for a way to populate a table in excel using another table that is populated manually and a table used for a lookup function.
When the user enters a number (Priority Level) into the “summary table”, I would like excel to look at the corresponding skill (Tax, Bens etc) and Agent Name, and populate the output table in the format shown (Skill Number “No.”, Skill Name “Name”, Priority Level “Pri.”) for each skill within the corresponding skill group (which can be anywhere from 1 skill to 10 skills). Excel will need to repeat this for every skill with a number in the summary table (multiple skills per agent).
Sheet 1 (Summary Table)
| Tax | Bens | SBS | Parking | Libraries | Registrars | Children’s | School Ad | Members | |
| Amar Shabir | 5 | 5 | 7 | ||||||
| Amy Hookham | 5 | 3 | |||||||
| Anne Marie Hole | 5 | 7 | 9 | ||||||
| Annette Ramsay | 7 | 9 | 5 | ||||||
| Antonia McNulty | 5 | 3 | |||||||
| Austin Warren | 5 | 7 | |||||||
| Brian McAuliffe | 7 | 5 | 3 | ||||||
| Carol Duffin | 9 | 7 | 5 |
<tbody>
</tbody>
Sheet 2 (Skill Lookup)
| Skill Name | Skill No. | Skill Group |
| LGS LBB ASC Social | 765 | Adult Social |
| LBB AT BB Payments | 660 | Assisted Travel |
| LBB AT BB Appl | 661 | Assisted Travel |
| LBB AT BB Other | 662 | Assisted Travel |
| LBB AT DFP Appl | 663 | Assisted Travel |
| LGS LBB Assst Travel | 747 | Assisted Travel |
| TandB HB New Claims | 677 | Bens |
| TandB HB Change Circ | 678 | Bens |
| TandB HB Chase Up Q | 679 | Bens |
| TandB HB Overpayment | 680 | Bens |
| TandB HB Pay Letters | 681 | Bens |
| TandB HB Landlord | 682 | Bens |
| TandB HB Other Enq | 683 | Bens |
| TandB HB Chase NOSP | 691 | Bens |
| LGS LBB HB New Claim | 743 | Bens |
| LGS LBB HB Enquiry | 744 | Bens |
| Barnet Bens O/Flow | 1191 | Bens |
| Barnet N/Claims | 1199 | Bens |
| LGS LBB CS Prior Ref | 762 | Children's |
| LGS LBB CS Child Unb | 763 | Children's |
| LGS LBB CS Default | 764 | Children's |
| LGS_Default_Aux | 219 | Default |
| LGS LBB FYI | 761 | FYI |
| LGS LBB Libraries | 655 | Libraries |
| LBB Reserve Book | 666 | Libraries |
| LBB Renew Book Tri | 667 | Libraries |
| LBB Library Pay Fine | 671 | Libraries |
| LBB Library Memb Enq | 672 | Libraries |
| LBB Members Enquiry | 760 | Member's |
| LGS LBB Park Per Ref | 768 | Parking |
| LGS LBB Other Per Enq | 769 | Parking |
| LGS LBB Park PCN War | 770 | Parking |
| LGS LBB Births | 649 | Registrars |
| LGS LBB Deaths | 650 | Registrars |
| LGS LBB Married CP | 651 | Registrars |
| LGS LBB DM Certs | 652 | Registrars |
| LGS LBB Registars D | 653 | Registrars |
| LGS LBB School Admit | 654 | School Admissions |
| LBB SA Wait List | 664 | School Admissions |
| LBB SA Gen Enq | 665 | School Admissions |
| LBB Sch Ad App Main | 689 | School Admissions |
| LBB Sch Ad App in Yr | 690 | School Admissions |
| LGS LBB Street Clean | 645 | Street Scene |
| LGS LBB Parks | 646 | Street Scene |
| LBB SBS Parks Ground | 646 | Street Scene |
| LBB SBS Rep Problem | 684 | Street Scene |
| LBB SBS Domestic Bin | 685 | Street Scene |
| LBB SBS Non Dom Bin | 686 | Street Scene |
| LGS LBB Street Serv | 736 | Street Scene |
| LGS LBB SBS Agents | 757 | Street Scene |
| LBB NR St Based Serv | 757 | Street Scene |
| LGS LBB SBS Env Ops | 759 | Street Scene |
| LGS LBB Switchboard | 656 | Switchboard |
| LBB TandB Remind IVR | 668 | Tax |
| LBB TandB Summons | 669 | Tax |
| LBB TandB CT Dis Pay | 670 | Tax |
| LBB TandB CT Dir Deb | 673 | Tax |
| LBB CT Moves 1 | 674 | Tax |
| LBB TandB CT Dis OL | 675 | Tax |
| LBB TandB CT Refund | 676 | Tax |
| LBB CT Text Message | 687 | Tax |
| LBB CT Moves 2 | 688 | Tax |
| LGS LBB CT DD | 737 | Tax |
| LGS LBB CT Moves | 738 | Tax |
| LGS LBB CT Bill Enq | 739 | Tax |
| LGS LBB CT Recovery | 740 | Tax |
| Barnet Ctax O/Flow | 1179 | Tax |
| Barnet Recovery | 1196 | Tax |
| Barnet DD | 1197 | Tax |
| Barnet Moves | 1198 | Tax |
| LGS LBB Youth Serv | 767 | Youth |
<tbody>
</tbody>
Sheet 1 (Output Table)
| Advisor Name | SC. | Skill 1 | Name | Pri. | Skill 2 | Name | Pri. | Skill 3 | Name | Pri. | Skill 5 | Name | Pri. | Skill 6 | Name | Pri. |
| No. | No. | No. | No. | No. | ||||||||||||
| Amar Shabir | 11 | 654 | LGS LBB School Admit | 5 | 761 | LGS LBB FYI | 4 | 650 | LGS LBB Deaths | 7 | 651 | LGS LBB Married CP | 7 | 652 | LGS LBB DM Certs | 7 |
| Amy Hookham | 24 | 739 | LGS LBB CT Bill Enq | 5 | 737 | LGS LBB CT DD | 5 | 738 | LGS LBB CT Moves | 5 | 740 | LGS LBB CT Recovery | 5 | 744 | LGS LBB HB Enquiry | 7 |
| Anne Marie Hole | 10 | 645 | LGS LBB Street Clean | 5 | 646 | LGS LBB Parks | 5 | 736 | LGS LBB Street Serv | 5 | 757 | LGS LBB SBS Agents | 5 | 646 | LBB SBS Parks Ground | 5 |
| Annette Ramsay | 24 | 739 | LGS LBB CT Bill Enq | 7 | 737 | LGS LBB CT DD | 7 | 738 | LGS LBB CT Moves | 7 | 744 | LGS LBB HB Enquiry | 5 | 743 | LGS LBB HB New Claim | 5 |
| Antonia McNulty | 8 | 747 | LGS LBB Assst Travel | 5 | 660 | LBB AT BB Payments | 5 | 661 | LBB AT BB Appl | 5 | 662 | LBB AT BB Other | 5 | 663 | LBB AT DFP Appl | 5 |
| Austin Warren | 8 | 747 | LGS LBB Assst Travel | 5 | 660 | LBB AT BB Payments | 5 | 661 | LBB AT BB Appl | 5 | 662 | LBB AT BB Other | 5 | 663 | LBB AT DFP Appl | 5 |
| Brian McAuliffe | 11 | 744 | LGS LBB HB Enquiry | 5 | 743 | LGS LBB HB New Claim | 5 | 677 | TandB HB New Claims | 5 | 678 | TandB HB Change Circ | 5 | 679 | TandB HB Chase Up Q | 5 |
| Carol Duffin | 16 | 656 | LGS LBB Switchboard | 9 | 655 | LGS LBB Libraries | 5 | 667 | LBB Renew Book Tri | 5 | 666 | LBB Reserve Book | 5 | 672 | LBB Library Memb Enq | 5 |
<tbody>
</tbody>
I imagine that VBA will need to be used but have no idea where to start with VBA and have exhausted all of my excel knowledge in trying to get the output I need using formulas.
Thanks in advance,
Carl
