Copying Cell Reference Patterns in Formulas Moving Sideways in A Spreadsheet

[abhimanyusodhi]

Hi,



I am faced with a problem, where I have a data set in a sheet where for each date there are three columns – Manager, Time Scheduled & Time Worked. Now, in another tab, a summary is required for each manager for each date. I used the sumifs function to get the details. However, while dragging it sideways to copy the formula under each date, the pattern of cell references is getting lost, here is the formula being used and the example of the sheet from which the data is being referenced:

=SUMIFS(Sheet2!B:B,Sheet2!A:A,Sheet4!$A14)/SUMIFS(Sheet2!C:C,Sheet2!A:A,Sheet4!$A14)

Where,

Sheet4!A14 = Manager Name (Summary Sheet)

Sheet2!B:B = Time worked

Sheet2!A:A = Manager Name (raw data sheet)

Sheet2!C:C = Time Scheduled (raw data sheet)

Will be great if some1 could help in resolving this.



Thanks

Abhimanyu Sodhi