How to change a formula reference?
 

I need to create a formula that references a range of cells. The range is
supposed to be determined by what the user enters into another cell. Can a
formula have a variable in it that will grab a number from another cell?
For instance:
=COUNTIF(N[B18]:N[B18+B18],"Y")?
Where you see B18 the user has entered in a number, we will say it was 5. I
need the function to reference the cells in the N column from N5 (cell B18
has a 5 in it) to N10 (B18+B18 or 5+5).

I appreciate any help you all can give me. Thanks so much in advance.

Brockettb

Hi!

Assume you enter in cells:

A1 = 5
B1 = 10

=COUNTIF(INDIRECT("N"&A1&":N"&B1),"Y")

Which translates to:

=COUNTIF(N5:N10,"Y")

Biff

---Original Message-----
I need to create a formula that references a range of
cells. The range is
supposed to be determined by what the user enters into
another cell. Can a
formula have a variable in it that will grab a number
from another cell?
For instance:
=COUNTIF(N[B18]:N[B18+B18],"Y")?
Where you see B18 the user has entered in a number, we
will say it was 5. I
need the function to reference the cells in the N column
from N5 (cell B18
has a 5 in it) to N10 (B18+B18 or 5+5).

I appreciate any help you all can give me. Thanks so
much in advance.

Brockettb
.

Hi Buck,
Use the INDIRECT Worksheet Function

=COUNTIF(INDIRECT("N"&B18 &":N" &B18+B18),"Y")

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Brockettb" wrote in message ...
I need to create a formula that references a range of cells. The range is
supposed to be determined by what the user enters into another cell. Can a
formula have a variable in it that will grab a number from another cell?
For instance:
=COUNTIF(N[B18]:N[B18+B18],"Y")?
Where you see B18 the user has entered in a number, we will say it was 5. I
need the function to reference the cells in the N column from N5 (cell B18
has a 5 in it) to N10 (B18+B18 or 5+5).

I appreciate any help you all can give me. Thanks so much in advance.

Brockettb

Another way:

=OFFSET($N$1,B18-1,0,B18*2,1)


On Sun, 6 Mar 2005 21:05:04 -0800, Brockettb
wrote:

I need to create a formula that references a range of cells. The range is
supposed to be determined by what the user enters into another cell. Can a
formula have a variable in it that will grab a number from another cell?
For instance:
=COUNTIF(N[B18]:N[B18+B18],"Y")?
Where you see B18 the user has entered in a number, we will say it was 5. I
need the function to reference the cells in the N column from N5 (cell B18
has a 5 in it) to N10 (B18+B18 or 5+5).

I appreciate any help you all can give me. Thanks so much in advance.

Brockettb

Thank you so much every one. I am going to try these out and see which one
works the best for me. Thanks again for the help.

Brockettb

"Myrna Larson" wrote:

Another way:

=OFFSET($N$1,B18-1,0,B18*2,1)


On Sun, 6 Mar 2005 21:05:04 -0800, Brockettb
wrote:

I need to create a formula that references a range of cells. The range is
supposed to be determined by what the user enters into another cell. Can a
formula have a variable in it that will grab a number from another cell?
For instance:
=COUNTIF(N[B18]:N[B18+B18],"Y")?
Where you see B18 the user has entered in a number, we will say it was 5. I
need the function to reference the cells in the N column from N5 (cell B18
has a 5 in it) to N10 (B18+B18 or 5+5).

I appreciate any help you all can give me. Thanks so much in advance.

Brockettb