# Making Cell Range Dynamic within a Formula

#### jacksparrow22

##### New Member
Hi Guys,

So I have the following formula:

=COUNTIF('Company Actual (NO BEAM)'!\$D\$7:\$D\$85,">"&E58)+1

As you can see this formula is referencing another workbook, the issue that I have is that I want to make the \$D\$7:\$D\$85 dynamic. I managed to create the following formula:

=INDEX('Company Actual (NO BEAM)'!\$M\$1451:\$BX\$1451,MATCH(Summary!E\$55&Summary!C71,'Company Actual (NO BEAM)'!\$M\$5:\$BX\$5,FALSE))&":"&INDEX('Company Actual (NO BEAM)'!\$M\$1450:\$BX\$1450,MATCH(Summary!E\$55&Summary!C71,'Company Actual (NO BEAM)'!\$M\$5:\$BX\$5,FALSE))

Which throws the range that I am looking for. My question to you is how do I integrate this formula to the one above? I believe you have to create an indirect formula?

Appreciate your help! would save me a late night in the office!

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Replies
5
Views
145
Replies
3
Views
63
Replies
9
Views
165
Replies
9
Views
124
Replies
9
Views
269

1,203,501
Messages
6,055,764
Members
444,821
Latest member
Shameer

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