Insert a value into formula

E

Ed

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
 
B

Biff

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
 
E

Ed

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
 
B

Biff

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
 
Top