Merging Worksheets with Inconsistent Entries
 

Hi Everyone-

I've got a workbook with some 40 worksheets containing macroeconomic
data for a number of countries. The problem is that some worksheets
have data on a certain number of countries, while others have more or
less.

What is the easist way to populate one worksheet with all of the data
(where missing info for certain fields would be left blank)?


Example:

Worksheet 1:
Italy 30,000
Germany 25,000
France 28,093

Worksheet 2:
Italy 4.5
Germany 6.8

Worksheet 3:
Italy 344
France 223
Spain 112

I want to simply create Worksheet 4:
Italy 30,000 4.5 344
Germany 25,000 6.8 -
France 28,093 - 223
Spain - - 112

This is obviously a simplification. The workbook contains a couple
hundred countries..

Thanks for your help!

Alex

Merging Worksheets with Inconsistent Entries
 

This is a design issue. Your one summary worksheet should be envisioned
first. Then, all the other worksheets should conform EXACTLY with the first.
For example, data for Germany should always appear on the same row number and
column, Italy the same row number and column - on every worksheet that comes
after the first one. Unfortunately this is a manual editing process.

Writing the formula, that can be copied, to pull the country data forward
into the summary sheet requires writing a formula that uses both Absolute
Cell References (eg. $b$4) and Relative Cell References (eg. b4) that would
look like one of the following ($b4 or b$4) where the $ "fixes" either the
row number or column (you can look this up in Help).

Finally you need to format the cell to display a "-" instead of a zero when
there is no data there requires creating a custom cell format. Look in help
on how to do that. If you can't figure it out, then write back
-
big-bob


" wrote:

Hi Everyone-

I've got a workbook with some 40 worksheets containing macroeconomic
data for a number of countries. The problem is that some worksheets
have data on a certain number of countries, while others have more or
less.

What is the easist way to populate one worksheet with all of the data
(where missing info for certain fields would be left blank)?


Example:

Worksheet 1:
Italy 30,000
Germany 25,000
France 28,093

Worksheet 2:
Italy 4.5
Germany 6.8

Worksheet 3:
Italy 344
France 223
Spain 112

I want to simply create Worksheet 4:
Italy 30,000 4.5 344
Germany 25,000 6.8 -
France 28,093 - 223
Spain - - 112

This is obviously a simplification. The workbook contains a couple
hundred countries..

Thanks for your help!

Alex