# Excel Formula Creation

I have an issue in Excel. Here is what i am trying to do, is this possible?

In a control I have a selection formula something like =\$A\$1:\$A\$500...so now I am limiting the "lookup" to 500 values. What happens if there is 501?

So here is my question...

Can I create a formula that makes that 500 = to a cell value, for instance: =\$A\$1:\$A\$(X) Where X would be equal to a cell value, so I can make this lookup in the control dynamic? I want X = the cell value of B1.

Is this possible??

Thanks.
###### Who is Participating?

x

Commented:
alucarelli,

OK, got it.

1) Define a new name, Orders, with Refers To:
=Data!\$B\$2:INDEX(Data!\$B:\$B,COUNTA(Data!\$B:\$B),1)

2) Make the source range for the dropdown control Orders.

Regards,

Patrick
0

Commented:
Hi alucarelli,
----------

not sure if this is a possibility for you but you can use dynamically sizing named ranges
http://www.beyondtechnology.com/geeks007.shtml

----------
bruintje
0

Commented:
Hi alucarelli,

Try:

You can use INDIRECT for that.  This example uses INDIRECT to set the lookup range for VLOOKUP:

=VLOOKUP(C1,INDIRECT("A1:A"&B1),1,FALSE)

Regards,

Patrick
0

Author Commented:
Patrick,
Thanks for you qucik response. But that still didn't work. Can I use the "INDIRECT" in a control range? We need the control, not a VLOOKUP....

Here is the formula we are using: Where data is page2...

INDIRECT("Data!B2:Data!B"&A15)

Where A15 stores an #value...

TIA.
Tony
0

Commented:
Tony,

I am not sure what you mean by control range.  Can you post an example workbook?  You can upload a sample to

http://www.ee-stuff.com/

Regards,

Patrick
0

Author Commented:
Patrick-

If you look at the file you will see two pages.

Ratings Questions & Data
The data page is loaded via VB and it will populate a value in cell AX1 (on the data page) with a numeric value (the number of rows of data).

On the Ratings Question page there is a control, which we do a lookup to the "Orders" on the Data Page. If you look at the Format Control properties the "Input Range" is set to Data!\$B\$2:\$B\$500 we would like the 500 to be dynamic.

Again Thanks.
Tony
0

Author Commented:

PS: I used EE-Stuff to upload the file.
0

Author Commented:
Awesome...worked like a charm!

Thanks very much for the help and the quick response.

Tony
0

Commented:
Tony,

Regards,

Patrick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.