How can I vary the row number or column alphabet depending on my n
 

Example- Varying row number.
If I need to look up cell A(x) where,
A is the column alphabet.
x is the variable row number.
How can I input x as a variable?

OR

Example- Varying column alphabet.
If I need to look up cell (x)1 where,
x is the variable column alphabet.
1 is the row number.
How can I input x as a variable?

Thank you

How can I vary the row number or column alphabet depending on my n
 

Generally speaking...

=INDEX(range,R,C)

Whe
R = row number
C = column number

Note that both R and C are *relative* to the range. For example:

=INDEX(F5:J10,2,5)

Returns the value at cell address J6. J6 is on the 2nd row in the 5th column
relative to the range F5:J10.

Both R and C can be calculated in many ways limited only by your imagination
and/or skill level.

You can also use this technique on a one dimensional array (single row or
single column).

=INDEX(A:A,10)

Returns the value of cell A10

=INDEX(1:1,10)

Returns the value of cell J1

You can use a range as an entire column (A:A) or row (1:1) as I did above or
you can use a specific range. Just remember that R and C are *relative* to
the range.


--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Example- Varying row number.
If I need to look up cell A(x) where,
A is the column alphabet.
x is the variable row number.
How can I input x as a variable?

OR

Example- Varying column alphabet.
If I need to look up cell (x)1 where,
x is the variable column alphabet.
1 is the row number.
How can I input x as a variable?

Thank you

How can I vary the row number or column alphabet depending on
 

Thanks for the response Valko.

Actually I was looking to sum up specific number of cells by varying the row
number or column alphabet.
Example- Sum rows A2:A4 based on a condition or sum rows A2:A6 based on
another condition or sum rows A2:A7 based on a third condition. Hence I
wanted to be able to vary the row number.
Please advise if you think this is possible.
Thanks!

"T. Valko" wrote:

Generally speaking...

=INDEX(range,R,C)

Whe
R = row number
C = column number

Note that both R and C are *relative* to the range. For example:

=INDEX(F5:J10,2,5)

Returns the value at cell address J6. J6 is on the 2nd row in the 5th column
relative to the range F5:J10.

Both R and C can be calculated in many ways limited only by your imagination
and/or skill level.

You can also use this technique on a one dimensional array (single row or
single column).

=INDEX(A:A,10)

Returns the value of cell A10

=INDEX(1:1,10)

Returns the value of cell J1

You can use a range as an entire column (A:A) or row (1:1) as I did above or
you can use a specific range. Just remember that R and C are *relative* to
the range.


--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Example- Varying row number.
If I need to look up cell A(x) where,
A is the column alphabet.
x is the variable row number.
How can I input x as a variable?

OR

Example- Varying column alphabet.
If I need to look up cell (x)1 where,
x is the variable column alphabet.
1 is the row number.
How can I input x as a variable?

Thank you

How can I vary the row number or column alphabet depending on
 

Ok, you keep saying "based on a condition" but you don't say what that
condition might be.

So, to sum the range A2:An where n is the variable:

Sum of A2:A4

=SUM(A2:INDEX(A:A,4))

Or:

C1 = 4

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

Note that if cell C1 is empty the *entire* range will be calculated.


--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Thanks for the response Valko.

Actually I was looking to sum up specific number of cells by varying the
row
number or column alphabet.
Example- Sum rows A2:A4 based on a condition or sum rows A2:A6 based on
another condition or sum rows A2:A7 based on a third condition. Hence I
wanted to be able to vary the row number.
Please advise if you think this is possible.
Thanks!

"T. Valko" wrote:

Generally speaking...

=INDEX(range,R,C)

Whe
R = row number
C = column number

Note that both R and C are *relative* to the range. For example:

=INDEX(F5:J10,2,5)

Returns the value at cell address J6. J6 is on the 2nd row in the 5th
column
relative to the range F5:J10.

Both R and C can be calculated in many ways limited only by your
imagination
and/or skill level.

You can also use this technique on a one dimensional array (single row or
single column).

=INDEX(A:A,10)

Returns the value of cell A10

=INDEX(1:1,10)

Returns the value of cell J1

You can use a range as an entire column (A:A) or row (1:1) as I did above
or
you can use a specific range. Just remember that R and C are *relative*
to
the range.


--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Example- Varying row number.
If I need to look up cell A(x) where,
A is the column alphabet.
x is the variable row number.
How can I input x as a variable?

OR

Example- Varying column alphabet.
If I need to look up cell (x)1 where,
x is the variable column alphabet.
1 is the row number.
How can I input x as a variable?

Thank you

How can I vary the row number or column alphabet depending on
 

Hi Valko,

Thanks...this helps!
The formula you wrote-
=SUM(A2:INDEX(A:A,C1))
....will certainly help me in the case of variable row number.

To simplify my request could you advise on a similar formula which would
represent a variable colum aplhabet?

Thanks again!


"T. Valko" wrote:

Ok, you keep saying "based on a condition" but you don't say what that
condition might be.

So, to sum the range A2:An where n is the variable:

Sum of A2:A4

=SUM(A2:INDEX(A:A,4))

Or:

C1 = 4

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

Note that if cell C1 is empty the *entire* range will be calculated.


--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Thanks for the response Valko.

Actually I was looking to sum up specific number of cells by varying the
row
number or column alphabet.
Example- Sum rows A2:A4 based on a condition or sum rows A2:A6 based on
another condition or sum rows A2:A7 based on a third condition. Hence I
wanted to be able to vary the row number.
Please advise if you think this is possible.
Thanks!

"T. Valko" wrote:

Generally speaking...

=INDEX(range,R,C)

Whe
R = row number
C = column number

Note that both R and C are *relative* to the range. For example:

=INDEX(F5:J10,2,5)

Returns the value at cell address J6. J6 is on the 2nd row in the 5th
column
relative to the range F5:J10.

Both R and C can be calculated in many ways limited only by your
imagination
and/or skill level.

You can also use this technique on a one dimensional array (single row or
single column).

=INDEX(A:A,10)

Returns the value of cell A10

=INDEX(1:1,10)

Returns the value of cell J1

You can use a range as an entire column (A:A) or row (1:1) as I did above
or
you can use a specific range. Just remember that R and C are *relative*
to
the range.


--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Example- Varying row number.
If I need to look up cell A(x) where,
A is the column alphabet.
x is the variable row number.
How can I input x as a variable?

OR

Example- Varying column alphabet.
If I need to look up cell (x)1 where,
x is the variable column alphabet.
1 is the row number.
How can I input x as a variable?

Thank you

How can I vary the row number or column alphabet depending on
 

It would follow the same basic syntax:

To sum B1:D1

A3 = 4

=SUM(B1:INDEX(1:1,A3))

Excel doesn't evaluate the columns based on their letter heading. They get
evaluated based on the column number. On the surface we see column A as "A"
(in A1 reference style) but under the covers inside Excel see's it as column
1. We see column Z as column "Z". Excel see's it as column 26.

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Hi Valko,

Thanks...this helps!
The formula you wrote-
=SUM(A2:INDEX(A:A,C1))
...will certainly help me in the case of variable row number.

To simplify my request could you advise on a similar formula which would
represent a variable colum aplhabet?

Thanks again!


"T. Valko" wrote:

Ok, you keep saying "based on a condition" but you don't say what that
condition might be.

So, to sum the range A2:An where n is the variable:

Sum of A2:A4

=SUM(A2:INDEX(A:A,4))

Or:

C1 = 4

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

Note that if cell C1 is empty the *entire* range will be calculated.


--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Thanks for the response Valko.

Actually I was looking to sum up specific number of cells by varying
the
row
number or column alphabet.
Example- Sum rows A2:A4 based on a condition or sum rows A2:A6 based on
another condition or sum rows A2:A7 based on a third condition. Hence I
wanted to be able to vary the row number.
Please advise if you think this is possible.
Thanks!

"T. Valko" wrote:

Generally speaking...

=INDEX(range,R,C)

Whe
R = row number
C = column number

Note that both R and C are *relative* to the range. For example:

=INDEX(F5:J10,2,5)

Returns the value at cell address J6. J6 is on the 2nd row in the 5th
column
relative to the range F5:J10.

Both R and C can be calculated in many ways limited only by your
imagination
and/or skill level.

You can also use this technique on a one dimensional array (single row
or
single column).

=INDEX(A:A,10)

Returns the value of cell A10

=INDEX(1:1,10)

Returns the value of cell J1

You can use a range as an entire column (A:A) or row (1:1) as I did
above
or
you can use a specific range. Just remember that R and C are
*relative*
to
the range.


--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Example- Varying row number.
If I need to look up cell A(x) where,
A is the column alphabet.
x is the variable row number.
How can I input x as a variable?

OR

Example- Varying column alphabet.
If I need to look up cell (x)1 where,
x is the variable column alphabet.
1 is the row number.
How can I input x as a variable?

Thank you

How can I vary the row number or column alphabet depending on
 

Thanks a lot for your help Valko!

"T. Valko" wrote:

It would follow the same basic syntax:

To sum B1:D1

A3 = 4

=SUM(B1:INDEX(1:1,A3))

Excel doesn't evaluate the columns based on their letter heading. They get
evaluated based on the column number. On the surface we see column A as "A"
(in A1 reference style) but under the covers inside Excel see's it as column
1. We see column Z as column "Z". Excel see's it as column 26.

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Hi Valko,

Thanks...this helps!
The formula you wrote-
=SUM(A2:INDEX(A:A,C1))
...will certainly help me in the case of variable row number.

To simplify my request could you advise on a similar formula which would
represent a variable colum aplhabet?

Thanks again!


"T. Valko" wrote:

Ok, you keep saying "based on a condition" but you don't say what that
condition might be.

So, to sum the range A2:An where n is the variable:

Sum of A2:A4

=SUM(A2:INDEX(A:A,4))

Or:

C1 = 4

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

Note that if cell C1 is empty the *entire* range will be calculated.


--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Thanks for the response Valko.

Actually I was looking to sum up specific number of cells by varying
the
row
number or column alphabet.
Example- Sum rows A2:A4 based on a condition or sum rows A2:A6 based on
another condition or sum rows A2:A7 based on a third condition. Hence I
wanted to be able to vary the row number.
Please advise if you think this is possible.
Thanks!

"T. Valko" wrote:

Generally speaking...

=INDEX(range,R,C)

Whe
R = row number
C = column number

Note that both R and C are *relative* to the range. For example:

=INDEX(F5:J10,2,5)

Returns the value at cell address J6. J6 is on the 2nd row in the 5th
column
relative to the range F5:J10.

Both R and C can be calculated in many ways limited only by your
imagination
and/or skill level.

You can also use this technique on a one dimensional array (single row
or
single column).

=INDEX(A:A,10)

Returns the value of cell A10

=INDEX(1:1,10)

Returns the value of cell J1

You can use a range as an entire column (A:A) or row (1:1) as I did
above
or
you can use a specific range. Just remember that R and C are
*relative*
to
the range.


--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Example- Varying row number.
If I need to look up cell A(x) where,
A is the column alphabet.
x is the variable row number.
How can I input x as a variable?

OR

Example- Varying column alphabet.
If I need to look up cell (x)1 where,
x is the variable column alphabet.
1 is the row number.
How can I input x as a variable?

Thank you

How can I vary the row number or column alphabet depending on
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Thanks a lot for your help Valko!

"T. Valko" wrote:

It would follow the same basic syntax:

To sum B1:D1

A3 = 4

=SUM(B1:INDEX(1:1,A3))

Excel doesn't evaluate the columns based on their letter heading. They
get
evaluated based on the column number. On the surface we see column A as
"A"
(in A1 reference style) but under the covers inside Excel see's it as
column
1. We see column Z as column "Z". Excel see's it as column 26.

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Hi Valko,

Thanks...this helps!
The formula you wrote-
=SUM(A2:INDEX(A:A,C1))
...will certainly help me in the case of variable row number.

To simplify my request could you advise on a similar formula which
would
represent a variable colum aplhabet?

Thanks again!


"T. Valko" wrote:

Ok, you keep saying "based on a condition" but you don't say what that
condition might be.

So, to sum the range A2:An where n is the variable:

Sum of A2:A4

=SUM(A2:INDEX(A:A,4))

Or:

C1 = 4

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

Note that if cell C1 is empty the *entire* range will be calculated.


--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Thanks for the response Valko.

Actually I was looking to sum up specific number of cells by varying
the
row
number or column alphabet.
Example- Sum rows A2:A4 based on a condition or sum rows A2:A6 based
on
another condition or sum rows A2:A7 based on a third condition.
Hence I
wanted to be able to vary the row number.
Please advise if you think this is possible.
Thanks!

"T. Valko" wrote:

Generally speaking...

=INDEX(range,R,C)

Whe
R = row number
C = column number

Note that both R and C are *relative* to the range. For example:

=INDEX(F5:J10,2,5)

Returns the value at cell address J6. J6 is on the 2nd row in the
5th
column
relative to the range F5:J10.

Both R and C can be calculated in many ways limited only by your
imagination
and/or skill level.

You can also use this technique on a one dimensional array (single
row
or
single column).

=INDEX(A:A,10)

Returns the value of cell A10

=INDEX(1:1,10)

Returns the value of cell J1

You can use a range as an entire column (A:A) or row (1:1) as I did
above
or
you can use a specific range. Just remember that R and C are
*relative*
to
the range.


--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Example- Varying row number.
If I need to look up cell A(x) where,
A is the column alphabet.
x is the variable row number.
How can I input x as a variable?

OR

Example- Varying column alphabet.
If I need to look up cell (x)1 where,
x is the variable column alphabet.
1 is the row number.
How can I input x as a variable?

Thank you