Goldenboy23
New Member
- Joined
- Oct 17, 2017
- Messages
- 11
[h=2][/h]
I have an issue whereby i have 5 different sheets of information Each sheet signifies a different category. Each sheet contains two columns. One with a code and the other with the value of that code in that particular category.
Example;
Sheet 1:
<tbody>
</tbody>
Sheet 2:
<tbody>
</tbody>
Sheet 3:
<tbody>
</tbody>
Sheet 4:
<tbody>
</tbody>
Sheet 5:
<tbody>
</tbody>
My aim is to produce a front sheet which contains all the information from the 5 different sheets combined, however, this has to be aligned in code order.
Example;
<tbody>
</tbody>
My initial thought was to create a V lookup. However there is no one column which contains complete data. However, columns 5 and 8 combined contain the full data.
In addition columns g and j will have respective calculations.
Any ideas for a formula to solve this issue ? i am thinking along the lines of Index and match but cannot get the result i desire.
Any help will be greatly appreciated.
P.S. the front sheet should contain 12 columns.
Example;
Sheet 1:
| Open | |
| 71224 | $20.00 |
| 71848 | $25.00 |
| 71229 | $15.00 |
| 71999 | $50.00 |
| 72333 | $20.00 |
| 72481 | $40.00 |
| 71266 | $20.00 |
<tbody>
</tbody>
Sheet 2:
| Invoice | |
| 71224 | $10.00 |
| 71848 | $20.00 |
| 71999 | $60.00 |
| 72481 | $60.00 |
| 72666 | $200.00 |
| 72899 | $60.00 |
| 71266 | $70.00 |
<tbody>
</tbody>
Sheet 3:
| Payment | |
| 71224 | $10.00 |
| 71266 | $70.00 |
| 72333 | $20.00 |
| 72481 | $100.00 |
| 72666 | $200.00 |
<tbody>
</tbody>
Sheet 4:
| Control Check | |
| 71848 | $45.00 |
| 71226 | $20.00 |
| 71224 | $20.00 |
| 71999 | $110.00 |
| 72418 | $25.00 |
| 72899 | $60.00 |
| 71229 | $15.00 |
<tbody>
</tbody>
Sheet 5:
| General check | |
| 71848 | $45.00 |
| 71226 | $20.00 |
| 71224 | $10.00 |
| 71999 | $110.00 |
| 72418 | $25.00 |
| 71229 | $15.00 |
| 72899 | $60.00 |
<tbody>
</tbody>
My aim is to produce a front sheet which contains all the information from the 5 different sheets combined, however, this has to be aligned in code order.
Example;
| Open | Invoice | Payment | "=B+E-H" | control check | "=k-i" | General Check | |||||||
| 71224 | $20.00 | 71224 | $10.00 | 71224 | $10.00 | $20.00 | 71224 | $20.00 | $0.00 | 71224 | $10.00 | ||
| 71229 | $15.00 | $15.00 | 71229 | $15.00 | $0.00 | 71229 | $15.00 | ||||||
| 71266 | $20.00 | 71266 | $70.00 | 71266 | $70.00 | $20.00 | 71226 | $20.00 | $0.00 | 71226 | $20.00 | ||
| 71848 | $25.00 | 71848 | $20.00 | $45.00 | 71848 | $45.00 | $0.00 | 71848 | $45.00 | ||||
| 71999 | $50.00 | 71999 | $60.00 | $110.00 | 71999 | $110.00 | $0.00 | 71999 | $110.00 | ||||
| 72333 | $20.00 | 72333 | $20.00 | $0.00 | $0.00 | ||||||||
| 72666 | $200.00 | 72666 | $200.00 | $0.00 | $0.00 | ||||||||
| 72481 | $40.00 | 72481 | $60.00 | 72481 | $100.00 | $0.00 | 72418 | $25.00 | $25.00 | 72418 | $25.00 | ||
| 72899 | $60.00 | $60.00 | 72899 | $60.00 | $0.00 | 72899 | $60.00 |
<tbody>
</tbody>
My initial thought was to create a V lookup. However there is no one column which contains complete data. However, columns 5 and 8 combined contain the full data.
In addition columns g and j will have respective calculations.
Any ideas for a formula to solve this issue ? i am thinking along the lines of Index and match but cannot get the result i desire.
Any help will be greatly appreciated.
P.S. the front sheet should contain 12 columns.
