Dragging a Formula

[Excel_77]

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

 

[Joe4]

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]

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

 

[63falcondude]

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

needs to reference BA4 - to BA33726

Which one is it? BA2:BA33726 or BA4:BA33726 ?

 

[Joe4]

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.

 

[Excel_77]

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?

 

[Joe4]

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:

=OFFSET(tab1!$BA$33727,0,ROW()-1)

 

[Excel_77]

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:

=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]

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.