Multiple Sheet Consolidation - Help

[peter.abing]

Hi everyone,

I would like to ask for your help in Excel.

I have several sheets in a workbook. All sheet contains similar data (same number of columns and same column description). Each sheet represents different group of data (example, sheet1 may be for one month and sheet2 is for another month, or sheet1 is for one project manager and sheet2 is for another PM).

What I want is to consolidate the data into one sheet. Every time I enter a line in one sheet, that line will automatically be updated in the consolidation sheet on the next empty row. It would be similar to a database.

Is this even possible? How would you go about it?

Regards,
Peter

[Hercules1946]

Hello
I don't know of any way that you can have formulae relating to several other sheets which (at the same time) might be needed to consolidate the data.

However, lets assume that you really had one summary sheet to compile from several other sheets that a new data record might be entered in.
If you had both the summary sheet and say a managers sheet open, plus a "work area" to enter the data, then both sheets could be coded to
pick up what you enter in the "work area" (a third spreadsheet).

When completed, the formulae in the summary and the manager sheets would be valued out, and the work area template cleared down.

There would be a number of issues (eg):

1. Identify the next available line in each sheet
2. Deal with entering multiple records?
3. When to cast the data in stone (by valuing out the formulae)
4 Designing the template to cope with data variations. (If any)

This might take some time to get working but it would be possible I think especially if the benefits
are good enough.

[Hercules1946]

Hello Peter
I thought of a simplify that would be just as effective. Assuming that all entries go into the consolidation, this should be the sheet where the
operator first enters data (doing away with "work area"). Each record caries a key that identifies which additional sheet its entered in,
plus may be a sequence number as a key.

New records are "collected" in the additional sheet(s) by being opened at the same time, either when the data is entered or later.
The additional sheets have a lookup formula that is valued away before the sheet is closed.
There would still be some other issues to deal with, but let me know what you think.

Oh ...... and do tell us if your going to cross post on other web sites.