Hello,
I'm trying to make life a little more efficient for some of our accountants but have hit an excel brick wall.
Background: We consolidate about 7 company's financials into one consolidated financial statement. For reasons unknown, these financials are not in a standardized format so I am constantly linking and relinking cells each month. It takes about 1-2 days total, let alone if there are any revision. Here's the key issue:
In excel, the accountants will provide me multiple accounts that can be consolidated into 1. For example:
In Raw excel sheet from Accountants (Sheet A)
<tbody>
</tbody>
What I'd like to do is something SUM all these figures with a criteria table in another sheet. For Example:
My excel sheet/working tab (Sheet B)
<tbody>
</tbody>
And then finally, my financial standardized financial Statement
<tbody>
</tbody>
Ideally with a formula like: =SUMIF(Vlookup(Sheet A Column A,Table B,2,False),="CASH",Sheet A Column B)
Is this possible with a formula? Currently I am creating an excel sheet that extracts the raw data and then I add my own columns, except as I said before these rows/columns always change.
Currently what I am doing:
Month 1 - Classification Sheet
<tbody>
</tbody>
Month 2 - Classification Sheet
(Assume values are the same and I don't reclassify it)
<tbody>
</tbody>
What I am trying to avoid is every month having to spend 5 hours reclassifying the accounts. Instead, just using a formula that references the account name (Column B) and sums it automatically.
Much help with be appreciated.
I'm trying to make life a little more efficient for some of our accountants but have hit an excel brick wall.
Background: We consolidate about 7 company's financials into one consolidated financial statement. For reasons unknown, these financials are not in a standardized format so I am constantly linking and relinking cells each month. It takes about 1-2 days total, let alone if there are any revision. Here's the key issue:
In excel, the accountants will provide me multiple accounts that can be consolidated into 1. For example:
In Raw excel sheet from Accountants (Sheet A)
Column A | Column B |
Cash on Hand | $1,000.00 |
Cash in the Bank | $500.00 |
Cash in Securities | $150.00 |
<tbody>
</tbody>
What I'd like to do is something SUM all these figures with a criteria table in another sheet. For Example:
My excel sheet/working tab (Sheet B)
Table B | |
Cash on Hand | Cash |
Cash in the Bank | Cash |
Cash in Securities | Cash |
<tbody>
</tbody>
And then finally, my financial standardized financial Statement
Cash | $1,650.00 |
<tbody>
</tbody>
Ideally with a formula like: =SUMIF(Vlookup(Sheet A Column A,Table B,2,False),="CASH",Sheet A Column B)
Is this possible with a formula? Currently I am creating an excel sheet that extracts the raw data and then I add my own columns, except as I said before these rows/columns always change.
Currently what I am doing:
Month 1 - Classification Sheet
Column A | Column B | Column C |
Cash | Cash on Hand | $1,000.00 |
Cash | Cash in the Bank | $500.00 |
Cash | Cash in Securities | $150.00 |
<tbody>
</tbody>
Month 2 - Classification Sheet
(Assume values are the same and I don't reclassify it)
Column A | Column B | Column C |
Cash | Fixed Assets | $10,000.00 |
Cash | Cash in the Bank | $500.00 |
Cash | Cash in Securities | $150.00 |
<tbody>
</tbody>
What I am trying to avoid is every month having to spend 5 hours reclassifying the accounts. Instead, just using a formula that references the account name (Column B) and sums it automatically.
Much help with be appreciated.