Dropdown with variable starting point

[Travman1986]

Hi
I want to be able to vary the starting point of a dropdown based on a change in cell B7 (see sheet attached). I am trying to create a formula in cell C7, which will form the base of a named Range. This formula keeps evaluating to an error.

[jeffreybrown]

Look here for some ideas...

http://www.contextures.com/xlDataVal02.html

What your post says and what you say inside your Excel file seem to give to ideas.

What are you doing? Trying to change the B10 drop down or trying to get a formula in C7?

If you change B7, what would you change it to and then what do you expect in C7 or B10?

[Travman1986]

Hi Jeffrey,
I'm familiar with contextures and have used it before for dynamic validation - it's an excellent resource. One limitation of it, however, is that the starting point is not dynamic. The end point is.

The formula in C7 would form the base of a named range. Then in cell B10 under the Data Validation, I would type List under Allow and my Source would be the name of the range.

[JBeaucaire]

Use this dynamic starting formula as the source of your list instead of the fixed range:

=INDEX(OFFSET($A$3, , MATCH(StartForecastPeriod,$3:$3, 0)-1, 1, COUNTA(OFFSET($A$3, , MATCH(StartForecastPeriod,$3:$3, 0)-1, 1, 1000))),1)

[southward]

Hi,

Is this what you are looking for?

Please Login or Register  to view this content.

If you enter it in a cell you have to use CTRL/SHIFT/ENTER as an array.

As a formula for a dynamic named range it should work for you.

Hope this is helpful.

Cheers