Copying Indirect horizontally

chellebell

New Member
Joined
Dec 16, 2016
Messages
2
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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

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