# Need help with dynamic range selection in formula

#### randyharris

##### Board Regular
This one is stumping me, would appreciate some ideas.

I have three columns:
• Column A contains monthly return % figures
• Column B tracks the drawdown%
• Column C indicates when the drawdown hits 15% or more (negative)
I'm hoping for a single formula in column B which adjusts the starting point based on the "Yes" indicators in column C.

The drawdown formula starting in cell B2 goes from the range \$A\$1:A1, and as the formula goes down it goes from \$A\$1:current row.

What I'm trying to do is whenever column C shows "Yes", then then in column B, the row after the "Yes" would instead of starting from \$A\$1, would start from the row after the "Yes" Row.

In the attached picture, cell B34, I would want it to be looking at the range \$A\$34:A34, instead of \$A\$1:A34. And if cell C50 had a "Yes", then B50 would be looking at the range \$A\$51:A51

Was thinking about maybe incorporating a check for the YES and if there, using the Offset formula, but couldn't figure it out.

#### Attachments

• Screen Shot 2020-10-29 at 2.23.25 PM.png
208.1 KB · Views: 5

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### steve the fish

##### Well-known Member
Your example doesnt make complete sense to me. You use row 1 with the formula in row 2 then later on you use the same row for where you want to look and the formula. However you can use some sort of index setup. Ive used it with a SUM formula here as an example:

=SUM(INDEX(\$A\$1:A1,IF(ISNUMBER(MATCH("yes",\$C\$1:C1,0)),LOOKUP("yes",\$C\$1:C1,ROW(\$C\$1:C1)),1)):INDEX(\$A\$1:A1,ROW(A1)))

This will move the sum range based on seeing yes in column C. Use the evaluate formula tool to see what its doing as it moves down the column.

#### randyharris

##### Board Regular
Steve the Fish (haha)

Sorry I didn't explain it as clearly as it should have been. Your formula is really close, but I'm having trouble adapting it.

If you look at the image below, the formula in Column B is a VBA function, drawdown.
• B2 is looking at the range of \$A\$2:A2
• B3 looks at the drawdown range of \$A\$2:A3, and so on down the column

The Trigger column C is what I hope to use the name the drawdown range dynamic.
• Row 33 is a Trigger "Yes", this means that
• cell B34, seeing the Trigger in C33, it would change the range it looks at from \$A\$2:A34 to \$A\$34:B34
So the drawdown formula in Column B, I'm hoping to start at the current row instead of A2 when there is a Yes in column C, (one row above the current formula in column B's row")

#### steve the fish

##### Well-known Member
Try this:

=DRAWDOWN(INDEX(\$A\$2:A2,IFERROR(LOOKUP(2,1/(\$C\$2:C2="yes"),ROW(\$C\$2:C2)-1),1)):INDEX(\$A\$2:A2,ROW(A2)-1))

Replies
34
Views
735
Replies
3
Views
99
Replies
27
Views
421
Replies
2
Views
395
Replies
3
Views
42

1,127,876
Messages
5,627,421
Members
416,245
Latest member
Xterminat

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