I am try to sum some cells from across 7 workbooks. Have tried consolodation
but it does not work how I need it to. Each work book has 4 worksheets the
1st is where I enter my data, the 2nd is a pivot table, the 3rd is just a
plain chart and the 4th is a sum of all the data gathered from the pivot
table (the work book also contains a list)
Each work book is identical.
I have an 8th workbook to contain all the summed data, and because
consolodtion wont work I have a formula as follows...

=SUM('[REWORK 01-05-05.xls]%'!$B$3+'[REWORK 25-04-05.xls]%'!$B$3+'[REWORK
26-04-05.xls]%'!$B$3+'[REWORK 27-04-05.xls]BURST %'!$B$3+'[REWORK
28-04-05.xls]%'!$B$3+'[REWORK 29-04-05.xls]%'!$B$3+'[REWORK
30-04-05.xls]%'!$B$3)

(the name of the sheet being summed is called '%' if you was wondering)

This works to a point where some of the cells do not have a value, but
display '#n/a' , this is because it is linked to a further page within each
workbook which some of the cells do not have a value entered.

The result is in the 8th workbook I get '#n/a' where there should be a value.
How do I ignore the cells containing '#n/a'?

Hope this makes some sense to you all, I know there is probably a simple
solution but being a novice with excel I can't work it out!

Your use of the SUM function is redundant.

=IF(ISNUMBER('[REWORK 01-05-05.xls]%'!$B$3),'[REWORK 01-05-05.xls]%'!$B$3)
+IF(ISNUMBER('[REWORK 25-04-05.xls]%'!$B$3),'[REWORK 25-04-05.xls]%'!$B$3)
+...

Jerry

Mark Goodwin wrote:

I am try to sum some cells from across 7 workbooks. Have tried consolodation
but it does not work how I need it to. Each work book has 4 worksheets the
1st is where I enter my data, the 2nd is a pivot table, the 3rd is just a
plain chart and the 4th is a sum of all the data gathered from the pivot
table (the work book also contains a list)
Each work book is identical.
I have an 8th workbook to contain all the summed data, and because
consolodtion wont work I have a formula as follows...

=SUM('[REWORK 01-05-05.xls]%'!$B$3+'[REWORK 25-04-05.xls]%'!$B$3+'[REWORK
26-04-05.xls]%'!$B$3+'[REWORK 27-04-05.xls]BURST %'!$B$3+'[REWORK
28-04-05.xls]%'!$B$3+'[REWORK 29-04-05.xls]%'!$B$3+'[REWORK
30-04-05.xls]%'!$B$3)

(the name of the sheet being summed is called '%' if you was wondering)

This works to a point where some of the cells do not have a value, but
display '#n/a' , this is because it is linked to a further page within each
workbook which some of the cells do not have a value entered.

The result is in the 8th workbook I get '#n/a' where there should be a value.
How do I ignore the cells containing '#n/a'?

Hope this makes some sense to you all, I know there is probably a simple
solution but being a novice with excel I can't work it out!

Thanks Jerry, that formula works a treat.

Now I just have to apply it to the other 350 other workbooks!

Regards
Mark

You're welcome. Glad it helped.

Jerry

Mark Goodwin wrote:

Thanks Jerry, that formula works a treat.

Now I just have to apply it to the other 350 other workbooks!

Regards
Mark