Dragging formulas on same sheet (automatic calculations turned on)

Chris_86

New Member
Joined
Dec 9, 2012
Messages
18
Office Version
  1. 2013
Platform
  1. Windows
Really need some help as I have a huge spreadsheet requiring me to manually enter alot of fields with formulas. I am using the formula

=COUNTIFS('A1'!C10:F11,Statistics!K5)+(COUNTIFS('A2'!C10:F11,Statistics!K5))+(COUNTIFS('A3'!C10:F11,Statistics!K5))

The full formula is quite long as this pick up the data off over 300 work sheets. (there's probably an easier way to do this aswell)
When I copy or drag this to another cell with on the same worksheet it gives me the correct formular with the correct cells changes as I want. However this formular does not work. I have auto calculate on and I manually press enter on the cells and still nothing. If I change the following for the entire formula (about 8min to do each cell) it works

=COUNTIFS('A1'!C10:F11,Statistics!K5)+(COUNTIFS('A2'!C10:F11,Statistics!K5))+(COUNTIFS('A3'!C10:F11,Statistics!K5))

Is there an easier alternative then manually changing each number?
 
Last edited:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
At the moment when I copy it into another cell it changes automatically (which is what I need)
 
Upvote 0
In your first post you said you had to change the formula to the one below...but they're identical. You say you want them to change though, so I'm a bit confused.

"=COUNTIFS('A1'!C10:F11,Statistics!K5)+(COUNTIFS('A2'!C10:F11,Statistics!K5))+(COUNTIFS('A3'!C10:F11,Statistics!K5))" #1
"=COUNTIFS('A1'!C10:F11,Statistics!K5)+(COUNTIFS('A2'!C10:F11,Statistics!K5))+(COUNTIFS('A3'!C10:F11,Statistics!K5))" #2

Which cell is #1 and #2 in?
 
Upvote 0
In your first post you said you had to change the formula to the one below...but they're identical. You say you want them to change though, so I'm a bit confused.

"=COUNTIFS('A1'!C10:F11,Statistics!K5)+(COUNTIFS('A2'!C10:F11,Statistics!K5))+(COUNTIFS('A3'!C10:F11,Statistics!K5))" #1
"=COUNTIFS('A1'!C10:F11,Statistics!K5)+(COUNTIFS('A2'!C10:F11,Statistics!K5))+(COUNTIFS('A3'!C10:F11,Statistics!K5))" #2

Which cell is #1 and #2 in?

Maybe I'm not explaing it correctly sorry. Ok so I have this which works only because I had to manually enter it into the cell; (manually entered)

=COUNTIFS('A1'!G7:J8,Statistics!O2)+(COUNTIFS('A2'!G7:J8,Statistics!O2))+(COUNTIFS('A3'!G7:J8,Statistics!O2))

Now i drag or copy this into a new cell which changes the corresponding numbers for me which is great so it looks like this; (automatically entered)

=COUNTIFS('A1'!I7:L8,Statistics!Q2)+(COUNTIFS('A2'!I7:L8,Statistics!Q2))+(COUNTIFS('A3'!I7:L8,Statistics!Q2))

However this does not calculate therefore It does not give the correct information. I hope this makes more sense. If i manually enter the exact same thing above it works just not when i copy or drag it over.
 
Last edited:
Upvote 0
OK. So when you drag your formula, you're changing the tables, from G7:J8, to I7:L8 are they meant to change?
If you don't want your "Table" to change then place a $ around the values you don't want to adjust.
IE $G$7:$J$8. When you drag that formula, it wont change at all.
If you use $G7:J$8, and you drag right, the J will adjust, if you drag down only the 7 will adjust.
If you want the statistics!O2 to move down the line so you're checking each value, then don't use the $ around the cell.

This site might help explain it a bit better if I have not managed to convey it accurately.
What are Relative Reference and Absolute Reference in Excel?
or
Formulas | Formula Referencing | Absolute and Relative Formulas

I hope that helps explain.

If this is not the cause of the issue, it might do to post a sample of the worksheet, so we can assess it some more.
 
Upvote 0
Thank you I think this is what I needed. Thanks Dermie_72 very much appreciated.

One last question. Is there a way I can SUM this all up rather then having to do this formula for 300+ pages? At the moment this is the formula length and I cannot add anymore due to limitations on excel. For sample is there rather then doing each page I can say go from page# to page#?

=COUNTIFS('A1'!$G$7:$J$8,Statistics!I2)+(COUNTIFS('A2'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('A3'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('A4'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('A5'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('A6'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('A7'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('A8'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('A9'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('A10'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('A11'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('B1'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('B2'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('B3'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('B4'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('B5'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('B6'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('B7'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('B8'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('B9'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('B10'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('B11'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('B12'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('B13'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('B14'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('B15'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('B16'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('B17'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('B18'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('B19'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('B20'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('B21'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('C1'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('C2'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('C3'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('C4'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('C5'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('C6'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('C7'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('C8'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('C9'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('C10'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('C11'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('C12'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('C13'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('C14'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('D1'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('D2'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('D3'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('D4'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('D5'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('D6'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('D7'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('D8'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('D9'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('D10'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('E1'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('E2'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('F1'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('F2'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('F3'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('G1'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('G2'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('G3'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('G4'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('G5'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('G6'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('G7'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('G8'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('G9'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('G10'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('G11'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('G12'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('G13'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('G14'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('G15'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('G16'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('H1'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('H2'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('H3'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('H4'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('H5'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('H6'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('H7'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('H8'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('H9'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('H10'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('H11'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('H12'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('H13'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('H14'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('H15'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('H16'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('H17'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('H18'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('H19'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('H20'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('I1'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('I2'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('I3'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('J1'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('J2'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('J3'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('J4'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('J5'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('J6'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('J7'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('J8'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('J9'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('J10'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('J11'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('K1'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('K2'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('K3'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('K4'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('K5'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('K6'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('L1'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('L2'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('L3'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('L4'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('L5'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('L6'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M1'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M2'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M3'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M4'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M5'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M6'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M7'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M8'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M9'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M10'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M11'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M12'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M13'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M14'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M15'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M16'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M17'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M18'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M19'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M20'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M21'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M22'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M23'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M24'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M25'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M26'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M27'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M28'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M29'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M30'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M31'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('M32'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('N1'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('N2'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('O1'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('O2'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('O3'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('O4'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('O5'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('P1'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('P2'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('P3'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('P4'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('P5'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('P6'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('P7'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('P8'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('P9'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('P10'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('Q1'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('Q2'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('R1'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('R2'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('R3'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('R4'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('R5'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('R6'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('R7'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('R8'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('R9'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('R10'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('R11'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('R12'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('R13'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('R14'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('R15'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('S1'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('S2'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('S3'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('S4'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('S5'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('S6'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('S7'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('S8'!$G$7:$J$8,Statistics!I2))+(COUNTIFS('S9'!$G$7:$J$8,Statistics!I2))

This is the current formula and I cannot add anymore
 
Upvote 0
How do I add an example excel sheet to this thread? Sorry new to forums.

I've tried to work it however unsuccesful. I've made a example document to try and reflect this information to you for ease. The reason for an example is due to confidentiality.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top