Solved

# Excel Formula Creation

Posted on 2006-07-14
526 Views
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.
0
Question by:alucarelli
• 4
• 4

LVL 44

Expert Comment

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

LVL 92

Expert Comment

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 Comment

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

LVL 92

Expert Comment

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 Comment

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 Comment

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

LVL 92

Accepted Solution

Patrick Matthews earned 250 total points
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

Author Comment

Awesome...worked like a charm!

Thanks very much for the help and the quick response.

Tony
0

LVL 92

Expert Comment

Tony,

Regards,

Patrick
0

## Featured Post

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …