formulae

Copper Contributor

I wish to add the value in all the cells in one worksheet to the value in all the cells of another exactly similar worksheet and put the result in a third exactly similar worksheet. I have found out how to go to "consolidate", etc, but I always get stopped by messages such as "consolidation address not recognised" or "consolidation reference is not valid"

Please can someone help me to find and insert the correct ? name? of the two source worksheets. Is it their file names or what? thanks for any help

hugh gilbert

2 Replies

@hughgilbert 

Can't remember when I used consolidation last, so I had to refresh my knowledge a bit. At first I received errors because I was doing things in the wrong order or in the wrong place.

 

1. Go to a cell in your destination sheet where you want to display your consolidated data

2. Press DATA / Consolidate..., choose the function you want to use e.g. SUM and select the "Reference" box

3. Go to your first source sheet

4. Select all cells (EDIT / Select All) or the range you want to include in your consolidation and press the +sign under the "All references" box

5. Repeat steps 3 and 4 for every source sheet you want to include

6. Indicate if your source data has a column and row headers or not!

6. Press OK

 

The SUM of all cells in the same positions in the source sheets are now displayed where the top left corner will be in the cell you selected in step 1. If you checked the column and row headers it's important that the headers are exactly the same in all sheets. If the aren't you may get som unexpeted results.

 

Experiment a little and see what happens. Good luck!

 

riny, thank you so much for answering my problem. It worked like a dream! all the best. Mike