Excel 2007 : help with #VALUE error

[DaveG46]

Hi I’m looking for some help on Excel 2007 which has been doing my head in for a while now.

I’m using excel to add up the results from a number of different time sheets at work, Each person has a workbook with 5 worksheets Week 1 to 4 and then total sheet which adds up the results (we have different codes for different activities which are added up with a countif formula). I now need to add up all the totals for everyone onto one sheet.

I know only a little bit about excel so what I’ve done is setup a master work book and pasted in a copy of each persons total for there sheet, then the final sheet adds up all the information which seems to work fine until I go out and come back into the sheet. I then get a message saying this workbook contains links to other data sources. If I click don’t update then everything seems to be ok however I guess this won't bring in the updated figures if anyone has changed their time sheet? If I click update the then excels throws a bit of a wobbler and fills the spreadsheet with #VALUE! error, this is really driving me mad as the error doesn’t appear on everyone’s time sheet, and if I go in and open each persons original time sheet then this then gets rid of the error on the master time sheet.

Sorry it not the greatest explanation but I wondered if anyone could offer any advice on how I can stop excel from showing this error, any help would be great. Thanks Dave

[scottylad2]

are all the other workbooks open at the same time you're updating? that could be your problem, trying to update from closed worrkbooks

[DaveG46]

are all the other workbooks open at the same time you're updating? that could be your problem, trying to update from closed worrkbooks

Ah never really thought about that, I would guess most people would have their open as we have to fill them in thoughout the day, I dont remember seeing too many "this time sheet is open" warnings though. Would this be why someone peoples dont have the #VALUE error as they were not in the timesheet at the time?

[DaveG46]

Had a go at opening the spreadsheets this morning when no one was in but still getting the error so don't think it can be that. Any other ideas?

[DaveG46]

Done a bit of research and found something that suggests that I should change the formula I'm using to include Sum and take out the + signs.

The Formula I was using is

='Clare Thompson'!C4+'Dave Goodhand'!C4+'Guy Hobson'!C4+'John Cousins'!C4+'Kevin Cadman'!C4+'Louise Gouldthorpe'!C4+'Martin Salmon'!C4+'Phillip George'!C4+'Rachael Ireson'!C4+'Sian Everett'!C4+'Steve DeBoer'!C4+'Tony Riley'!C4+'Tracey Duffort'!C4+'Trevor Honess'!C4+'Dave Allen'!C4

after seeing the comments about sum and + I changed it to but its still giving the Value error

=SUM('Clare Thompson'!C4,'Dave Goodhand'!C4,'Guy Hobson'!C4,'John Cousins'!C4,'Kevin Cadman'!C4,'Louise Gouldthorpe'!C4,'Martin Salmon'!C4,'Phillip George'!C4,'Rachael Ireson'!C4,'Sian Everett'!C4,'Steve DeBoer'!C4,'Tony Riley'!C4,'Tracey Duffort'!C4,'Trevor Honess'!C4,'Dave Allen'!C4)

I've also had a go at just having one sheet and using this as the totals master I've not copied the other sheets like I did before and only used three people . The formula for this one seems to work and updates fine with now errors, but excel now tells me the formulas too long so I can't get everyone on there. I used this formula

=SUM('G:\Time Sheets\Clare Thompson\[Timesheet Period Ending 040211 CLT .xls]Sheet2'!$C$4,'G:\Time Sheets\Dave Goodhand\[Timesheet Period Ending 040211.xls]Sheet2'!$C$4,'G:\Time Sheets\John Cousins\[Timesheet Period Ending 06-02-11 JC.xls]Sheet2'!$C$4)

could someone have a look at the above and see what I maybe doing wrong or suggest a way around the formula which is too long for excel. Thanks

[DonkeyOte]

Do you have underlying #VALUE! errors in any of the cells you're referencing ?

[DaveG46]

Don't think so they all appear ok in each persons totals sheet, guess if there was a problem I would be able to see the #value! error in one of the cells?

[DaveG46]

Would a Countif formula in the original data which I'm using cause the Value error?

[DonkeyOte]

Only if it returns a #VALUE! error - which it would if linked to a closed file.

[DaveG46]

Only if it returns a #VALUE! error - which it would if linked to a closed file.

What do you mean by a closed file?

[DonkeyOte]

If you have a COUNTIF formula that points to another file and that file is closed then the COUNTIF will return #VALUE! when calculated.

[DaveG46]

Cheers DonkeyOte will give that a look, every little helps, I will get to the bottom this error eventually. For now I'm opening everyones timesheet before I open the master sheet and then clicking update which seems to sort my problem.

[DonkeyOte]

If the COUNTIF is pointed at a timesheet file that file would need to be open.

If that is indeed the case and you prefer to keep the files closed you can replace the COUNTIF with SUMPRODUCT - the latter is however a less efficient function.

[DaveG46]

Thanks but Sumproduct gives a value error in the original file. The countif formula is used to add up the different codes which people enter, these are made up of text and numbers such as AD01 or GA01

[DonkeyOte]

@DaveG46, as I am sure you can appreciate - with nothing to see / work on it's hard for anyone to provide meaningful comment I'm afraid.