Insert a value into formula
 

Hello, Is it possible to have a formula that has a Range for example: $A$1:
A100, but that I can type the number 100 in a special cell and the formula
takes the value from that cell?

,thank you

Insert a value into formula
 

Hi!

The short answer is yes, but how to do it depends on what the formula is
doing.

Post your formula for a more specific suggestion.

Biff

"Ed" wrote in message
...
Hello, Is it possible to have a formula that has a Range for example:
$A$1:
A100, but that I can type the number 100 in a special cell and the formula
takes the value from that cell?

,thank you

Insert a value into formula
 

Okay, let's suppose it is a VLOOKUP

=VLOOKUP($A2,'Sheet1'!$B$2:U[VALUE HERE],16,"FALSE")

In the case that I would need to change the table array for some reason. I
haven't really thought exactly for the moment how I will need it, because I
know I can just use "B:U" on the formula above, but I thought I might need
sometimes to be able to modify the array tables for some formulas. A case I
can think of really quick is on a sum

=SUM(A1:A[VALUE HERE])

I hope I was able to explain myself!
thanks

Insert a value into formula
 

Ok....

For the lookup formula you'd need to use Indirect:

=VLOOKUP($A2,INDIRECT("Sheet1!B2:U"&A1),16,0)

Where A1 holds your number value.

For the sum formula:

=SUM(A1:INDEX(A:A,C1))

Where C1 holds you number value.

Note that if C1 is empty ALL the cells in A:A will be summed. So, maybe with
a little "checker":

=IF(C1="","",SUM(A1:INDEX(A:A,C1)))

Biff

"Ed" wrote in message
...
Okay, let's suppose it is a VLOOKUP

=VLOOKUP($A2,'Sheet1'!$B$2:U[VALUE HERE],16,"FALSE")

In the case that I would need to change the table array for some reason. I
haven't really thought exactly for the moment how I will need it, because
I
know I can just use "B:U" on the formula above, but I thought I might need
sometimes to be able to modify the array tables for some formulas. A case
I
can think of really quick is on a sum

=SUM(A1:A[VALUE HERE])

I hope I was able to explain myself!
thanks

Insert a value into formula
 

Thanks Biff!

Insert a value into formula
 

You're welcome!

Biff

"Ed" wrote in message
...
Thanks Biff!