Formula is skipping worksheets in workbook

[russelh1971]

I cant seem to find a similar question so Im going to go ahead and post a new thread. I have multiple expense reports (2008 to 2011) and I am trying to add all the totals from different items on these to one worksheet in a separate workbook (stay with me here Im winging this). Anyway the formula I have is

=SUM([RWH2011.xls]RWH11001:RWH11005!E29)

RWH2011.xls = the workbook name
RWH11001:RWH11005 = the sheets in that workbook
E29 = the cell I want to collect data from

(not trying to insult anyones intelligence)

This all seems to work ok except that for some reason it has decided to skip some work sheet? In this case worksheet RWH11002 from that workbook. Does any one know what I did wrong or how I might change this so that it will work? Thank you in advance for your time.

Russel

 

[Jon von der Heyden]

Welcome to the board!

Is RWH11002 physically positioned between RWH11001 and RWH11005?

And what value is in RWH11002!E29? Can you confirm that it is a number?

=ISNUMBER(RWH11002!E29)

 

[russelh1971]

Hey Thanks!

The sheet is positioned in order, it is not a true number (1,2,3,etc) i is another formula displaying the sum of three other cells on that sheet (=E29+D29+C29). The other sheets are the same but this one seems to be a problem child (same problem with other workbooks but since they all seem to have the same problem one solution will fix them all).

 

[Jon von der Heyden]

What is the resulting value in that cell?

 

[russelh1971]

It is displaying 4950.00, the exact amount that is missing from the total on the master sheet Im trying to build.

 

[russelh1971]

still dont know why it did this but I rebuilt the worksheet it was skipping from scratch (recreated all the formulas etc) and then deleted the old on. Now it sees it fine pain to do but worked. Thank you for the advice sorry if I wasted your time here.

Russel