Dragging a formula problem?

nharbel

New Member
Joined
Nov 10, 2019
Messages
2
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?
 

nharbel

New Member
Joined
Nov 10, 2019
Messages
2
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
Joined
Sep 21, 2016
Messages
2,609
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)
 

Forum statistics

Threads
1,077,849
Messages
5,336,734
Members
399,100
Latest member
darcob

Some videos you may like

This Week's Hot Topics

Top