I have 18 or so Excel 2010 files (showing research results) that are identical in layout and I need to 'sum' the data into another file to provide a grand total. It seems that this should be very simple using the sum function across the files, indicating the file name, the sheet name and the cell name but when I paste the formula from the first cell into others (on the same sheet), Excell wants to 'update value' for each of the 18 files. This makes it painfully tedious to copy and paste the formula in order to sum all the data.
I know the formula works - it works in the first cell I put it into - it's justing pasting it in that causes this annoying request.
Is there any way to stop it requesting 'update value' when pasting?
The formula is:
=SUM([StaffSurveyAnalysisBasildon]Q1!E4,StaffSurveyAnalysisBrooklands]Q1!E4,StaffSurveyAnalysisCallCentre]Q1!E4,StaffSurveyAnalysisChelmsford]Q1!E4,[StaffSurveyAnalysisCroydon]Q1!E4,[StaffSurveyAnalysisDartford]Q1!E4,[StaffSurveyAnalysisGrays]Q1!E4,[StaffSurveyAnalysisHarlow]Q1!E4, etc etc
Bookmarks