Prepare Cash Consolidation Wkst

[Guest]

Hi All,

I have 7 different bank accounts that I manage everyday, which I have 7
different workbooks that I enter data for. Once I fill out all of the detail
for each bank account for that day, I then have to go to a "Consolidated"
Cash sheet, and re-enter the data for the day so that each of my "detailed"
bank account files equals to my summary cash file.

I would like to be able to enter the detailed data in each of the individual
workbooks, and have the information automatically updated when I open the
consolidated workbook. This would save hours!!!!!!

I have given this some thought, and so far, here is what I have come up with.

For each detailed workbook, I have two columns that would be pertinant to
"coding" for the consolidated workbook. For example, column B would have
sub account validation box with 3 choices, F-01-BS,M-01-BS,and U-01-BS.
Column C would have multiple choices such as A/P, Billing Receipt, LOC
Advance, Other.

For the consolidated workbook, I would want to this to be executed "look
into 1100 Cash.xls, and for every F-01-BS that is a "billing receipt", then
return the data in column G here". (Column G would be the numeric amount for
this transaction) This, unfortunately is where I get hung up...I am not sure
how the formula should read for this to happen.

Any help would be greatly appreciated...thanks in advance....sorry for the
long post

 

[Guest]

You might be able to use MS Query to consolidate Excel ranges from your
multiple wkbks/wkshts. This also works for consolidating data from the
active workbook (Just save it first so Excel can find it):

This example uses 5 named ranges in 5 different workbooks, but it works for
any number.
(Each range contain 4 columns: Acct, SubAcct, Type, Amount)

Once all of the data is consolidated, you can move, filter, and pivot table
any way you'd like.

Assumptions:
The data in each wkbk is structured like a table:
--->Col headings (Acct, SubAcct, Type, Amount)
--->Columns are in the same order.

The data in each wkbk must be in named ranges.
--->I used rngBank1111Data for Bank 1111's data, rngBank2222Data for Bank
2222, etc
--->You may use the same range name in different wkbks.

(Note: MS Query may display warnings about it's ability to show the query
....ignore them and proceed.)

Starting with an empty worksheet:
1)Select the cell where you want the consolidated data to start

2)Data>Import External Data>New Database Query

Databases: Excel Files

Browse to one of The files, pick The data range to import.
--->Accept defaults until the next step.

At The last screen select The View data/Edit The Query option.

Click the [SQL] button

Replace the displayed SQL code with an adapted version of this:

SELECT * FROM `C:\Bank1111`.rngBank1111Data
UNION ALL
SELECT * FROM `C:\Bank2222`.rngBank2222Data
UNION ALL
SELECT * FROM `C:\Bank3333`.rngBank3333Data
UNION ALL
SELECT * FROM `C:\Bank5555`.rngBank4444Data
UNION ALL
SELECT * FROM `C:\Bank5555`.rngBank5555Data

(Note: the apostrophes in the SQL code ( ` )are located on the same key as
the tilde (~) )
Return the data to Excel.

Once that is done....to get the latest data just click in the data range
then Data>Refresh Data.
(You can edit the query at any time to add/remove data sources and/or fields.)

If you want to try this approach, post back with any questions.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

Hi Ron,

This approach sounds very interesting, and, in the future, I will look into
this, but for now, I would just like to use formulas. I will be passing this
job off to someone who is not very versed with excel, and I don't want to
make it difficult to explain to someone later.

This is what I have so far for my formula:
=IF('[Cash 1100 Template - edited SS 2.xls]Sheet1'!$C$9="Internal
Transfer",'[Cash 1100 Template - edited SS 2.xls]Sheet1'!$F$9,0)

I would like to add to this formula 2 more restraints - this is how I would
say it, but I can't get the syntax correct. If cell C9 equals Internal
Transfer, and Cell B9 equals M-01-BS, and Cell J9 is equal to the current
month, then return the dollar amount in column F.


I realized that I needed to add a constraint for the current month because
we use the detail worksheet like a ledger, and it goes back to the beginning
of the bank activity for each fiscal year. For the consolidation workbook,
we have a worksheet for each month. I am not sure how to make the IF
statement look at the current month without going in and editing each month's
formulas. Any suggestions??

Also, I need this to remain constant for each column, but look at all of the
rows, since there may be multiple "Internal Transfers" for each month.

Thanks ---

You might be able to use MS Query to consolidate Excel ranges from your
multiple wkbks/wkshts. This also works for consolidating data from the
active workbook (Just save it first so Excel can find it):

This example uses 5 named ranges in 5 different workbooks, but it works for
any number.
(Each range contain 4 columns: Acct, SubAcct, Type, Amount)

Once all of the data is consolidated, you can move, filter, and pivot table
any way you'd like.

Assumptions:
The data in each wkbk is structured like a table:
--->Col headings (Acct, SubAcct, Type, Amount)
--->Columns are in the same order.

The data in each wkbk must be in named ranges.
--->I used rngBank1111Data for Bank 1111's data, rngBank2222Data for Bank
2222, etc
--->You may use the same range name in different wkbks.

(Note: MS Query may display warnings about it's ability to show the query
...ignore them and proceed.)

Starting with an empty worksheet:
1)Select the cell where you want the consolidated data to start

2)Data>Import External Data>New Database Query

Databases: Excel Files

Browse to one of The files, pick The data range to import.
--->Accept defaults until the next step.

At The last screen select The View data/Edit The Query option.

Click the [SQL] button

Replace the displayed SQL code with an adapted version of this:

SELECT * FROM `C:\Bank1111`.rngBank1111Data
UNION ALL
SELECT * FROM `C:\Bank2222`.rngBank2222Data
UNION ALL
SELECT * FROM `C:\Bank3333`.rngBank3333Data
UNION ALL
SELECT * FROM `C:\Bank5555`.rngBank4444Data
UNION ALL
SELECT * FROM `C:\Bank5555`.rngBank5555Data

(Note: the apostrophes in the SQL code ( ` )are located on the same key as
the tilde (~) )
Return the data to Excel.

Once that is done....to get the latest data just click in the data range
then Data>Refresh Data.
(You can edit the query at any time to add/remove data sources and/or fields.)

If you want to try this approach, post back with any questions.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

Hi All,

I have 7 different bank accounts that I manage everyday, which I have 7
different workbooks that I enter data for. Once I fill out all of the detail
for each bank account for that day, I then have to go to a "Consolidated"
Cash sheet, and re-enter the data for the day so that each of my "detailed"
bank account files equals to my summary cash file.

I would like to be able to enter the detailed data in each of the individual
workbooks, and have the information automatically updated when I open the
consolidated workbook. This would save hours!!!!!!

I have given this some thought, and so far, here is what I have come up with.

For each detailed workbook, I have two columns that would be pertinant to
"coding" for the consolidated workbook. For example, column B would have
sub account validation box with 3 choices, F-01-BS,M-01-BS,and U-01-BS.
Column C would have multiple choices such as A/P, Billing Receipt, LOC
Advance, Other.

For the consolidated workbook, I would want to this to be executed "look
into 1100 Cash.xls, and for every F-01-BS that is a "billing receipt", then
return the data in column G here". (Column G would be the numeric amount for
this transaction) This, unfortunately is where I get hung up...I am not sure
how the formula should read for this to happen.

Any help would be greatly appreciated...thanks in advance....sorry for the
long post