Need help merging worksheets

[carmeljo]

I have trial balances (cloumns of account numbers and account balances)
for multiple years on different worksheets and need to merge them into
one. The accounts are in numerical order, but not all years contain the
same accounts.

Please advise...

 

[carmeljo]

bump bump bump

 

[Ron Coderre]

Here's something to try:

Open the 2004 workbook
Change the column heading for the amounts to the year
(eg. Account balance will be changed to: 2004)
Select the Acct/Amount range
Name that range by doing this:
Click in the Name Box to the left of the formula bar.
Enter rngAccountData
Press [Enter]
Save the workbook.

Repeat for the 2005 workbook (you can use the same range name)

Open a new workbook and select cell A1 on any sheet.
Data>Consolidate
Click the [Browse] button
Select the 2004 workbook
(You'll something like: C:\2004AccountInfo.xls!)

Append rngAccountData to the value in the Reference box
(So it looks like: C:\2004AccountInfo.xls!rngAccountData
Click the [Add] button

Repeat for the 2005 workbook.

Use labels in
Check: Top Row
Check: Left Column
Click the [OK] button

Sample of returned data:
Account_2004__2005
1005____10____1
1010__________2
1015____20____3
1020__________4
1025____30____5
1030__________6
1035____40____7
1040__________8
1045____50____9
1050__________10
1055____60____11
1065____70
1075____80

Does that give you something to work with?

Regards,
Ron

 

[carmeljo]

My data is actually on seperate sheets of the same workbook, but I went
ahead and copied them to differnet workbooks so I could follow your
instructions, but I don't quite get it. When you say "Append
rngAccountData to the value in the Reference box
(So it looks like: C:\2004AccountInfo.xls!rngAccountData," do you mean
click on the file and when it shows up in the reference box just left
of the browse button add "!rngAccountData" to the end of it?

If so, that's where I'm having trouble. I can only see the beginning of
the filename and when I try to move my cursor to the end to add
"!rng..." the cursor is attached to the spreadsheet and inserts a cell
reference rather that the text I'm typing, and then when I type the
"!rng...) it puts it in the cell rather than in the reference box.

Also, what should the function be?

 

[Ron Coderre]

OK...a couple points to help you out.

You can have all of the sheets in the same workbook. If you do that,
just use different range names: rng2004Data, rng2005Data, etc
box just left of the browse button add "!rngAccountData" to the end of
it?
<<
Yes.
Regarding the reference, you're right...when you click in the Reference
box after browsing to a file, it will try to "help" you create a
reference. As soon as you have selected a file, click in the Reference
box and press the [F2] key. That will switch it from select mode to
edit mode. Then just type in the range name.
The function will be SUM, but that won't matter for you because you
won't have any duplicate accounts in the same year, will you? If you
do, they'll be summarized by year.

If you have any other questions, let us know.

Best regards,
Ron