Dynamic Named Ranges

[Qullion22]

I am using a named range for a data validation in a configurator.
i have a range defined as:
=OFFSET(Limitations!$AN$9,0,0,COUNT(Limitations!$AN$9:$AN$99),1) this defines the end point of the range dynamically.

This works very well, however, now i want to make the starting cell dynamic. that means using a formula that returns a reference rather than the direct reference Limitations$an$9
one example of those i have tried is
"'Lists'!"&ADDRESS(P5,P6) where p5 and p6 are the col and row numbers of the first cell in the range i want to use (the first cell changes with user input)
i have tried every combination of ' and " to set the text as i think excel wants it but the only one that works and only on a one off basis (ie highlight and hit F9) is
this evaluates to
"'Lists'!$J$11"
but the formula still does not work.
although editing it to lists!$j$11 does work.

any assistance would be apreciated....

[Glenn Kennedy]

Are you changing the starting row in a single column, or changing both column and row?

[Qullion22]

Just the starting row

[protonLeah]

Specify what makes any particular cell the starting cell:...
I.e., what do you see that lets you know.

[Qullion22]

ok a bit more explanation of the problem:
Its a dynamically specified range based on user input;
Definitions:
The range A1 to A10 is a set of numbers that are generally valid.
The user inputs a number which defines the start cell of that range and the end cell.
This specifies the specific validation number set (the sequence A1 to A10 is in order) and so we are specifying the start and the end points.
That range is then defined as a list and used for data validation in a subsequent data input process.

there might be better ways of doing this using index or similar.

Any suggestions would be appreciated.

[FlameRetired]

Not sure I completely understand. This is a guess.

=OFFSET(INDEX(Limitations!$AN$9:$AN$99,MATCH(A1,Limitations!$AN$9:$AN$99,0)),0,0,COUNT(Limitations!$AN$9:$AN$99),1)

and fill down for each value A1:A10.

The INDEX\MATCH part will set the first argument of OFFSET dynamically.

Does that do what you want?

[leelnich]

Hi all! @Qullion, perhaps you could upload a sample workbook to clarify where everything is and how it's used. In particular, you need to show where and how the user inputs a single value that defines both start and end points for the range. Be sure to include examples of both the underlying validation entries and a validated cell so we can see exactly how the range is used.

To attach a workbook:
Click Edit Post (or just start a new reply.)
Click Go Advanced
Scroll down to Manage Attachments and click.
Now click Browse, find your file, then click Upload to finish.

[kvsrinivasamurthy]

Try this

Your inputs are StartNumber and EndNumber

=OFFSET(INDIRECT("Limitations!$AN$"&StartNumber,0,0,EndNumber-StartNumber+1,1) this defines the end point of the range dynamically.