Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 . |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change workbook sheet reference using cell A1 to change a vairable | Excel Worksheet Functions | |||
Cannot change a formula | Excel Discussion (Misc queries) | |||
copying a formula, the reference adjusts, but the result does not | Excel Discussion (Misc queries) | |||
how do I make a formula NOT change when the data range is moved? | Excel Discussion (Misc queries) | |||
Formula to reference column heading | Excel Worksheet Functions |