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?
 

Some videos you may like

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
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,874
Office Version
2016
Platform
Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,090,337
Messages
5,413,875
Members
403,504
Latest member
rosebo

This Week's Hot Topics

Top