Dragging formulas on same sheet (automatic calculations turned on)

C

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:
Sort by date Sort by votes
Excel 2010
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
1
2
3
4

<TBODY>
[TD="bgcolor: #fabf8f"]With Job?
[/TD]
[TD="bgcolor: #fabf8f"][/TD]
[TD="bgcolor: #fabf8f"]Region
[/TD]
[TD="bgcolor: #fabf8f"][/TD]
[TD="bgcolor: #fabf8f"][/TD]
[TD="bgcolor: #fabf8f"][/TD]
[TD="bgcolor: #fabf8f"][/TD]
[TD="bgcolor: #fabf8f"][/TD]
[TD="bgcolor: #fabf8f"]Location of Exposure
[/TD]
[TD="bgcolor: #fabf8f"][/TD]
[TD="bgcolor: #fabf8f"][/TD]
[TD="bgcolor: #fabf8f"][/TD]
[TD="bgcolor: #fabf8f"][/TD]
[TD="bgcolor: #fabf8f"][/TD]
[TD="bgcolor: #fabf8f"][/TD]
[TD="bgcolor: #fabf8f"][/TD]
[TD="bgcolor: #fabf8f"][/TD]
[TD="bgcolor: #fabf8f"][/TD]

[TD="bgcolor: #d8e4bc"]Yes
[/TD]
[TD="bgcolor: #d8e4bc"]No
[/TD]
[TD="bgcolor: #d8e4bc"]Northern
[/TD]
[TD="bgcolor: #d8e4bc"][/TD]
[TD="bgcolor: #d8e4bc"]Central
[/TD]
[TD="bgcolor: #d8e4bc"][/TD]
[TD="bgcolor: #d8e4bc"]Southern
[/TD]
[TD="bgcolor: #d8e4bc"][/TD]
[TD="bgcolor: #d8e4bc"]Cairns
[/TD]
[TD="bgcolor: #d8e4bc"][/TD]
[TD="bgcolor: #d8e4bc"]Brisbane
[/TD]
[TD="bgcolor: #d8e4bc"][/TD]
[TD="bgcolor: #d8e4bc"]Townsville
[/TD]
[TD="bgcolor: #d8e4bc"][/TD]
[TD="bgcolor: #d8e4bc"]Mackay
[/TD]
[TD="bgcolor: #d8e4bc"][/TD]
[TD="bgcolor: #d8e4bc"]Rockhampton
[/TD]
[TD="bgcolor: #d8e4bc"][/TD]

[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"]#VALUE!
[/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"]1
[/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"]1
[/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]

[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]
[TD="bgcolor: #ffffff"][/TD]

</TBODY>
Statistics
​

Formulas used in I3 - =COUNTIFS('A1'!$G$4:$J$5,Statistics!I2)+COUNTIFS('A2'!$G$4:$J$5,Statistics!I2)
This works and counts every sheet providing I add the sheets manually e.g. A1, A2, A3 etc

Formulas used in G3 - =COUNTIFS('A1:A3'!$C$4:$D$5,Statistics!G2)
This doesn't work

G3 Formula does not work, however this sort of short cut rather then adding every sheet in like with I3 would be helpful. Is there a way I can use A1:A3 or another formula to capture the information.

This is Page A1
Excel 2010
ABCDEFGHIJKLMNOPQR

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]
[TD="bgcolor: #808080, align: center"]CONTACT DETAILS[/TD]
[TD="bgcolor: #808080, align: center"][/TD]
[TD="bgcolor: #808080, align: center"][/TD]
[TD="bgcolor: #808080, align: center"][/TD]
[TD="bgcolor: #808080, align: center"][/TD]
[TD="bgcolor: #808080, align: center"][/TD]
[TD="bgcolor: #808080, align: center"][/TD]
[TD="bgcolor: #808080, align: center"][/TD]
[TD="bgcolor: #808080, align: center"][/TD]
[TD="bgcolor: #808080, align: center"][/TD]
[TD="bgcolor: #808080, align: center"][/TD]
[TD="bgcolor: #808080, align: center"][/TD]
[TD="bgcolor: #808080, align: center"][/TD]
[TD="bgcolor: #808080, align: center"][/TD]
[TD="bgcolor: #808080, align: center"][/TD]
[TD="bgcolor: #808080, align: center"][/TD]
[TD="bgcolor: #808080, align: center"][/TD]
[TD="bgcolor: #808080, align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #ffff00, align: center"]STILL WITH JOB[/TD]
[TD="bgcolor: #ffff00, align: center"][/TD]
[TD="bgcolor: #d9d9d9, align: center"]YES[/TD]
[TD="bgcolor: #d9d9d9, align: center"][/TD]
[TD="bgcolor: #ffff00, align: center"]D.O.B.[/TD]
[TD="bgcolor: #ffff00, align: center"][/TD]
[TD="align: center"]2/04/1986[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffff00, align: center"]PREFERED CONTACT NUMBER[/TD]
[TD="bgcolor: #ffff00, align: center"][/TD]
[TD="align: center"]44444444[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffff00, align: center"]E-MAIL[/TD]
[TD="bgcolor: #ffff00, align: center"][/TD]
[TD="align: center"]joe.blogs@hotmail.com[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #ffff00, align: center"][/TD]
[TD="bgcolor: #ffff00, align: center"][/TD]
[TD="bgcolor: #d9d9d9, align: center"][/TD]
[TD="bgcolor: #d9d9d9, align: center"][/TD]
[TD="bgcolor: #ffff00, align: center"][/TD]
[TD="bgcolor: #ffff00, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffff00, align: center"][/TD]
[TD="bgcolor: #ffff00, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffff00, align: center"][/TD]
[TD="bgcolor: #ffff00, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #ffff00, align: center"]REGION[/TD]
[TD="bgcolor: #ffff00, align: center"][/TD]
[TD="align: center"]Northern[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffff00, align: center"]LOCATION[/TD]
[TD="bgcolor: #ffff00, align: center"][/TD]
[TD="align: center"]Cairns[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffff00, align: center"]CURRENT POSITION[/TD]
[TD="bgcolor: #ffff00, align: center"][/TD]
[TD="align: center"]Manager[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #ffff00, align: center"][/TD]
[TD="bgcolor: #ffff00, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffff00, align: center"][/TD]
[TD="bgcolor: #ffff00, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #ffff00, align: center"][/TD]
[TD="bgcolor: #ffff00, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</TBODY>
A1​

Im sorry for posting so much its just that I have spent the last 4 months working on this really great register (note the above is not the register) and my boss has told me that If i cannot capture the data I cannot use it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
What is the best way to drag and drop a formula?
Replies
1
Views
173
MARK858
MARK858
G
  • Question Question
Weird drag-and-drop behavior
Replies
1
Views
519
Fluff
Fluff
M
  • Question Question
Formula Calculation is Inadvertently Switching from Automatic to Manual...
Replies
0
Views
388
MNexcelguy19
M
GMC The Macro Man
  • Solved
SUM COUNTIFS with AND + OR
Replies
3
Views
238
Fluff
Fluff
S
  • Question Question
Have formula bar reflect the link to a cell
Replies
6
Views
291
Peter_SSs
Peter_SSs

Share this page

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