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

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)DataImport External DataNew 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 DataRefresh 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


"Frantic Excel-er" wrote:

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

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 ---



"Ron Coderre" wrote:

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)DataImport External DataNew 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 DataRefresh 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


"Frantic Excel-er" wrote:

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