Dragging a Formula

Excel_77

Active Member
Joined
Sep 15, 2016
Messages
291
Hi All,

I have the following formula in tab 2, it needs to be done for 100 rows using the same number but with BA updating to BB to BC as the formula is dragged down, is this possible?

=COUNTIF(tab1!BA2:BA33726, "No")

Thanks in advance
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,430
Office Version
  1. 365
Platform
  1. Windows
What row is the first formula being placed into?
How far down does it need to go (what is the last column it needs to reference)?
 

Excel_77

Active Member
Joined
Sep 15, 2016
Messages
291
What row is the first formula being placed into?
How far down does it need to go (what is the last column it needs to reference)?

Formula is being placed into cell C1 of the second tab and needs to run to cell C40.

From the original source it C1 needs to reference BA4 - to BA33726.
C2 needs to reference BB4 - BB33726 and so on
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,430
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Here is how I would do it:

Highlight the range BA4 - BA33726 on tab1 and name it (I used MyRange for this example).
See this if you do not know how to name ranges:
http://www.contextures.com/xlNames01.html

Then, on your other sheet, enter this formula in cell C1:
=COUNTIF(OFFSET(MyRange,0,ROW()-1), "No")
and copy down to cell C40.
 
Last edited:

Excel_77

Active Member
Joined
Sep 15, 2016
Messages
291
Thanks but that doesn't quite work.

What I have now done is update row BA33727, BB33727 onwards up to CN33727 with totals in tab1, however in tab2 C1 I want to return the value in BA, in C2 the BB value and so on, can this be done?
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,430
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks but that doesn't quite work.
It should work on the original conditions you have laid out. If you changed them, then the solution would obviously have to be changed too.

What I have now done is update row BA33727, BB33727 onwards up to CN33727 with totals in tab1, however in tab2 C1 I want to return the value in BA, in C2 the BB value and so on, can this be done?
If you just want to return the value from those cells, then enter the following formula in C1 and copy down:
Code:
=OFFSET(tab1!$BA$33727,0,ROW()-1)
 

Excel_77

Active Member
Joined
Sep 15, 2016
Messages
291
It should work on the original conditions you have laid out. If you changed them, then the solution would obviously have to be changed too.


If you just want to return the value from those cells, then enter the following formula in C1 and copy down:
Code:
=OFFSET(tab1!$BA$33727,0,ROW()-1)

Thanks so much, that works. Could you explain your code please? None of it makes any sense to me.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,430
Office Version
  1. 365
Platform
  1. Windows
The most important thing to understand is the OFFSET function. There are lots of great resources out there that have explanations and examples that can be found with Google searches (and check out Excel's help files on functions - they are pretty good). Here is one: https://www.techonthenet.com/excel/formulas/offset.php

The other important piece is the ROW function. When left blank, like ROW(), it simply returns the row number of the cell the formula is found in. We can use this to our advantage, as we populate our formula down a column, it will increment by 1 each time, i.e.
for the first formula, placed in cell C1, ROW() returns 1,
for the second formula, placed in cell C2, ROW() returns 2,
etc.

So, you put those two things together to get what you need.
 

Forum statistics

Threads
1,137,350
Messages
5,680,981
Members
419,947
Latest member
cchristian6

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
Top