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.
alucarelliAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Patrick MatthewsConnect With a Mentor 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
 
bruintjeCommented:
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
 
Patrick MatthewsCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
alucarelliAuthor 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
 
Patrick MatthewsCommented:
Tony,

I am not sure what you mean by control range.  Can you post an example workbook?  You can upload a sample to
your own site, or use:

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

Regards,

Patrick
0
 
alucarelliAuthor Commented:
Patrick-

I uploaded the file.

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
 
alucarelliAuthor Commented:


PS: I used EE-Stuff to upload the file.
0
 
alucarelliAuthor Commented:
Awesome...worked like a charm!

Thanks very much for the help and the quick response.

Tony
0
 
Patrick MatthewsCommented:
Tony,

Glad to help.

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.

All Courses

From novice to tech pro — start learning today.