# Make a formula that has specific-cell-references change every n'th row in Excel

#### StijnW1998

##### New Member
Hi all,

For a university project, I am currently working on a fictional dataset containing information on a charity and their different campaigns (see picture for a small part of the document).

What I would like to do is the following: for every "group" of 10 rows (10 most recent donations and/or soliciations for 1 donor), I want to check if the 10 dates showcased in column I (dates on which that specific donor contributed an amount to the charity) overlap with the dates in column F (dates on which the donors received a sollication of the charity asking them for a donation). In that way, it would become possible to compare the different types of donor (e.g. male/female, ZIP code, etc.) in terms of how sensitive they are to the donation solicitations made by the charity.

I found a way to check this overlap for the 10 donation dates of the first donor, using the following formula (see column N): =NOT(ISERROR(MATCH(I11,\$F\$2:\$F\$11,0)))

However, now I would like to change the values in the formula after every 10 rows, so that that the formula focuses on the next donor. For example: it should become \$F\$12:\$F\$21 for donor #7270, \$F\$22:\$F\$31 for donor #10620, and so on and so forth.

Is there a way to do this? Of should I use a different type of formula?

Best,
Stan

#### Attachments

• Capture.PNG
104.8 KB · Views: 4

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### Eric W

##### MrExcel MVP
Welcome to the MrExcel forum!

Here's an idea that uses the donor ID in C to figure out the ranges. It's much shorter.

Book2
ABCDEFGHIJKLMN
1donorSolicitContributeMatch?
2006033/1/20155/1/2015FALSE
3006031/1/20151/1/2015TRUE
4006031/1/201512/1/2014FALSE
50060310/1/20145/1/2014FALSE
6006037/1/20141/1/2014TRUE
7006033/1/201411/1/2013FALSE
8006031/1/20148/1/2013FALSE
9006034/1/2013FALSE
10006032/1/2013FALSE
Sheet10
Cell Formulas
RangeFormula
N2:N10N2=COUNTIFS(C:C,C2,F:F,I2)>0

#### StijnW1998

##### New Member
I managed to make it work with a slightly altered version of the IF-formula, thank you very much for thinking with me!

#### Eric W

##### MrExcel MVP
Glad we could help! Thanks for the feedback.

Replies
11
Views
126
Replies
0
Views
72
Replies
1
Views
243
Replies
13
Views
407
Replies
2
Views
897

1,127,199
Messages
5,623,317
Members
415,966
Latest member
ctorohuamanchumo

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