Help with incrementing cell reference

**mjtrash** · 12-11-2012, 02:17 AM

So I have a little over 1,000 rows filled with 1's and 2's in column L. I would like to make a nested countif and if statement implementing incremented rows. I am trying to make a formula so that if there is one "2" in L1:L23, then I will get the result "Yes". If there are only "1's" in L1:L23 I want to get the result "No". However, I need help making a formula that will search for a 2 in L1:L23, then L24:L46, then L47:L69, then L70:L92 etc...

I'm looking for a way to find one "2" in each range, then if there is a "2" have the output be "Yes", without having to manually select the ranges dozens of time.

Thanks in advance for your help!

**TheNewUnion** · 12-11-2012, 11:53 AM

Assuming that there are always 23 rows each time you calculate, I think that this is do'able. Will have to have a look at it but I think you'll probably be able to use Offset (perhaps). Using ROW() will allow us to assign a number to each row, can work with that I assume.

Will have a look at it later if I can.

**TheNewUnion** · 12-12-2012, 04:50 AM

Had a rethink on this, might be easier to just use a table to define the ranges then stick a formula in to validate the content of the cells. I'll do it now then work out how to post it. Might not be the best of most elegant solution, but it should do the job for you.

**dogberry** · 12-12-2012, 05:19 AM

Originally Posted by TheNewUnion

Had a rethink on this, might be easier to just use a table to define the ranges then stick a formula in to validate the content of the cells. I'll do it now then work out how to post it. Might not be the best of most elegant solution, but it should do the job for you.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

Chris