Dragging formulas on same sheet (automatic calculations turned on)

[Chris_86]

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?

 

[dermie_72]

do you want the K5 to stay the same when you drag?

 

[Chris_86]

At the moment when I copy it into another cell it changes automatically (which is what I need)

 

[dermie_72]

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?

 

[Chris_86]

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.

 

[dermie_72]

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.

 

[Chris_86]

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

 

[dermie_72]

have a look at this post, it might make your life much easier
http://www.mrexcel.com/forum/excel-questions/36082-countif-across-multiple-worksheets.html
you may need to set up a range for all the sheet names, but once that's done, it should be a simple line of code instead of what you're using now.

 

[Chris_86]

Thanks again Dermie_72 your help is very much appreciated

 

[Chris_86]

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.