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

StijnW1998

New Member
Joined
Dec 7, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
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
    Capture.PNG
    104.8 KB · Views: 4

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,138
Office Version
  1. 365
Platform
  1. Windows

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,803
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
 
Solution

StijnW1998

New Member
Joined
Dec 7, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
I managed to make it work with a slightly altered version of the IF-formula, thank you very much for thinking with me!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,387
Messages
5,636,018
Members
416,892
Latest member
Bensch

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
Top