# Dragging a formula problem?

#### nharbel

##### New Member
I apologize if this has been covered somewhere else, but I'm not even sure how to accurately search for my problem. I have a spreadsheet in which I have calculated a lot of data on 3 separate sheets. I'm trying to consolidate the data into a "Totals" sheet. I have used the following formula in cell B5 on the Totals sheet to do this: =\$'5th'.B5+\$'6th'.B5+\$JH.B5+\$HS.B5. In cell B6 on the Totals sheet I used this formula: =\$5th.C5+\$6th.C5+\$JH.C5+\$HS.C5. I need to drag this formula across the page on the Totals sheet and have it fill in the formula for a cell found in every other column on the individual sheets. I have manually entered in the info for the first two cells, but when I try dragging it across to fill in or copy the formula, it doesn't fill in for every other column, it fills it in consecutively. I am desperate to find a formula to do this automatically to save me hours of time doing it manually. Does anyone have any suggestions?

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### nharbel

##### New Member
The above link refers to data on a single sheet. I have values on 3 separate sheets that I am combining on a 4th sheet. A offset formula may work, but I need something specific to my situation.

#### footoo

##### Well-known Member
The principle is the same whether it's one sheet or more than one.
For example, the construction for your Totals sheet cell B5 :
=OFFSET(Sheet1!\$A5,0,(COLUMN(A5)*2)-1)+OFFSET(Sheet2!\$A5,0,(COLUMN(A5)*2)-1)
and for C5 :
=OFFSET(Sheet1!\$B\$5,0,(COLUMN(A5)*2)-1)+OFFSET(Sheet2!\$B\$5,0,(COLUMN(A5)*2)-1)