Excel 2003 help?

Question

Is there a way to easily (or not so easily) merge the contents of multiple speradsheets into one? The way our system works, it exracts info for different dates, all with the same fields, into a separate spreadsheet for each day. I need to consolodate these, while staying in date order. Is there a way to do this? Thanks.

I should add that I have a large number of sheets to work with at a time, and am looking for an alternative to cut and paste. I know Excel, I just wondered if there was an easier method. Thanks.

Answer 1

Yes, Microsoft provides a way!
Our company has a similar application, but it is Text files, and an Excel file. My system allows users to place their cursor on the line they want to move data into. (One line per day) then they click a button and the data is consolidated on that line from two different text files and an Excel sheet.

Here's what I suggest for you:
Open the file you consolidate into now. Start a macro recording. Open a file, do your custom pattern of copy/paste that arranges the data the way you want it. Close the file. End the recording.

Now edit the macro.
You will find a Workbooks.Open Filename:= "" command.
In the quotes will be the name of the file you opened with the path included. You can control what file to open in your sheet. Take the name of the file out and put closed quotes at the \. then put & and put a reference to a cell in your workbook like [A1] for cell A1.

In future runnings you put the name of the file to open in cell A1. then run the macro.

Also, before each of your paste commands, there is a select

Range("B18").select is an example.
You can replace that row 18 with the current row. The current row reference is: ActiveCell.Row. You need to preserve that row before the file is opened, so you put a variable assignment at the top of your code. First declare the variable. Here's an example:
Dim EntryRow as Long
EntryRow = ActiveCell.Row
(Then open the file.)

You would change the select to:
Range("B" & EntryRow).Select

Because you took the row number and put it in the variable EntryRow

So, just remember to place your cursor on the row you want before you run the macro.

It is tedious work modifying a recorded macro to do this, but in the long run it may save you time because you will marvel how fast the macro does the copy/paste pattern and it will be infinitely more accurate!

Answer 2

You seem to want to put all your eggs in one basket so you can search it. This has benefits, so, let's give it a simple try. It is too important to explain a complicated set of formulas here.
Create a new sheet as a monthly database.
The three spreadsheets become side by side active vertical databases. It may not be possible to have them aligned by date in rows. It probably doesn't matter. Your search criteria will scan down to find matching data from each.
Suppose in three spreadsheet you have:
abcde
11111
22222
33333

ABCDE
444444
555555
666666

WXYZ
77777
88888
99999

Then in your new spreadsheet you have formulae that collects from the sheets and puts the data on the new sheet:
At the start of the first sheet in the upper the corner and extending the width of data, a formula like this.
=[September06SchedA.xls]Sheet1!A1
=[September06SchedA.xls]Sheet1!A2
=[September06SchedA.xls]Sheet1!A3
=[September06SchedA.xls]Sheet1!A4

If you just enter a formula such as = and click on the first cell of data in one of the spreadsheets, you will get a formula like this: =[September06Sched.xls]Sheet1!$A$1 This can not be drag/copied until you remove the $ from it.

So design the collector with enough rows and columns to contain the data in 3 other sheets. In the upper left cell of each type = and click on the corresponding cell in the original data. Remove the $ from the formula. Drag from the bottom right corner of the cell to the right to match the data in the first sheet. Skip 3 cells. Type = and click on the corresponding data in the second sheet. Remove the $. Drag the cell from the bottom right corner of the cell to the right to match the data in the third sheet.

Select all these cells - the full width of all the formulae for all three data sets. Drag the bottom right corner of the LAST selected cell DOWN to fill the sheet with enough cells to contain one month of data from the three original spreadsheets.

Cells that have data will have it. Cells that don't have data will have a zero. Cells that don't exist will have errors. When the cells exist, the data will appear.

This is simple. If the data you want is spread out in many locations rather than in neat rows and columns, it won't work.

;-D Maybe you can make it work using the daily report that is generated. You will have to click on the cells you want for each data you want. It could be confusing.

Answer 3

Try the Import data function. Got to toolbar click on Data, Import external data, import data. Then browse to the workbook you want to import.

Answer 4

If you know some programming it is easy to do, Wrox sells a book called VBA for Excel that might help you do this, check it out.

Answer 5

copy and paste then sort by date and it should work....good luck