# Copying Indirect horizontally

#### chellebell

##### New Member
Hello,

I'm working on an excel document that tracks who checked in and after a specific date we'll move them to a different area. Below is the current code:

=COUNTIF(BN6:INDIRECT("BN"&ROW()-2),"x")

I want to fill-across, but when I do I have to go in and change "BN" to the next row. I've tried all sorts of ways, referencing the current cell to get the column, indirect to reference the column, offset, and quite a few others that I can't remember. I did find one that was close (I can't remember what it was now), but it didn't get it right. It was using

COLUMN()+1

But it would only reference the original (above) plus 1, which would direct it to the next row. I can't for the life of me remember what the exact formula was. (I've been working on this and fixing an Access Database, so my mind is kinda fried right now.

Thanks in advance for any and all help.

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Welcome to the Board!

So, you want your range to go across multiple columns in a single row as opposed to multiple rows in a single column?
How should it be determined how far it needs to go out?
Can you just have it go all the way out to the last possible column?, i.e.
Code:
``[COLOR=#333333]=COUNTIF(BN6:IV6,"x")[/COLOR]``

If not, it might be helpful if you can post some examples of your data and expected formulas.
There are tools you can use to post screen images. They are listed in Section B of this link here: Guidelines for Forum Use.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

Welcome to the forum.

I'm making a lot of assumptions here, but I think this is what you want. Function OFFSET chokes when you try to put an array in the Height or Width arguments; so you have to trick it by using function MAX.

Enter this in a row two greater than the reference row (6 in this case).

Code:
``=COUNTIF(OFFSET(BN\$6,,,MAX(1,ROW()-2)),"x")``

Last edited:
Hey guys, thanks so much for the replies. I'm sorry I should have been more clear I just want it to count the x in that column only. I want to be able to copy the fumula into the next column (grab the little plus in the bottom right corner and drag over for as many columns as I currently need) while it updates to the correct column name.

Replies
3
Views
653
Replies
3
Views
682
Replies
5
Views
763
Replies
14
Views
1K
Replies
3
Views
539

1,196,073
Messages
6,013,265
Members
441,758
Latest member
Abren

### 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.

### Which adblocker are you using?

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

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