Full disclosure this is a cross-forum thread (excelgure.ca) since I need to find a solution quickly!
https://www.excelguru.ca/forums/sho...abs-with-multiple-criteria-array-and-indirect
Hi Everyone, I'm spinning my wheels here between index match and sumproduct arrays but I think the indirect for multiple tabs is whats killing me. Here's the scenario, I have a GL data dumps (one tab for each month) and I need to map the GL account to the financial statement line items. Sounds simple but there are multiple 'stores' in each GL as well and for some reason when I use indirect its not working for the tabs (I'm using tab names instead of tables for the indirect reference). Another thing that I think always clouds my mind is I like to create formulas I can 1) Drag and 2) easily use in the future by adding data... I've also attached a sheet for reference - located in other forum since Mr Excel doesnt let you add files.
Thanks so much in advance!
Raw data in tabs (two tabs for two months as example):
<tbody>
</tbody>
Mapping from GL to Financials:
<tbody>
</tbody>
Final Product(s):
<tbody>
</tbody>
https://www.excelguru.ca/forums/sho...abs-with-multiple-criteria-array-and-indirect
Hi Everyone, I'm spinning my wheels here between index match and sumproduct arrays but I think the indirect for multiple tabs is whats killing me. Here's the scenario, I have a GL data dumps (one tab for each month) and I need to map the GL account to the financial statement line items. Sounds simple but there are multiple 'stores' in each GL as well and for some reason when I use indirect its not working for the tabs (I'm using tab names instead of tables for the indirect reference). Another thing that I think always clouds my mind is I like to create formulas I can 1) Drag and 2) easily use in the future by adding data... I've also attached a sheet for reference - located in other forum since Mr Excel doesnt let you add files.
Thanks so much in advance!
Raw data in tabs (two tabs for two months as example):
Tab - January | |||
GL Account | Store A | Store B | Store C |
5011 · Apples | 10.00 | 11.00 | 15.00 |
5012 · Bananas | 20.00 | 12.00 | 16.00 |
5013 · Grapes | 30.00 | 13.00 | 17.00 |
5014 · Water | 40.00 | 14.00 | 18.00 |
Tab - February | |||
GL Account | Store A | Store B | Store C |
5011 · Apples | 15.00 | 16.00 | 20.00 |
5012 · Bananas | 25.00 | 17.00 | 21.00 |
5013 · Grapes | 35.00 | 18.00 | 22.00 |
5014 · Water | 45.00 | 19.00 | 23.00 |
<tbody>
</tbody>
Mapping from GL to Financials:
GL Name | Statement Name |
5011 · Apples | Revenue - Fruit |
5012 · Bananas | Revenue - Fruit |
5013 · Grapes | Revenue - Fruit |
5014 · Water | Revenue - Drinks |
<tbody>
</tbody>
Final Product(s):
Financial Summary - January | |||
Store A | Store B | Store C | |
Revenue - Fruit | 60.00 | 36.00 | 48.00 |
Revenue - Drinks | 40.00 | 14.00 | 18.00 |
Financial Summary - February | |||
Store A | Store B | Store C | |
Revenue - Fruit | 75.00 | 51.00 | 63.00 |
Revenue - Drinks | 45.00 | 19.00 | 23.00 |
<tbody>
</tbody>