dynamic range query

  • Thread starter Thread starter kevinherring
  • Start date Start date
K

kevinherring

I'm sorry about this but i have looked in all the other threads and i
cant find an answer.

I want a dynamic range named 'Supplier'

The column is constant (A) but the start row and end row are dynamic
and user changable. I have two cells on a different sheet that contain
the start and end row. I have called these supp_start_row and
supp_end_row.


now i want my range 'supplier' to be:

A supp_start_row : A supp_end_row.

i have tried using offset and all that but it doesnt seem to work.

any ideas?
thanks
kevin
 
Activate Insert|Name|Define.
Enter Supplier as name in the Names in Workbook box.
Enter the following in the Refers to box:

=INDEX(x!$A:$A,supp_start_row):INDEX(x!$A:$A,supp_end_row)

Click OK.

Replace x with the actual sheet name.

Caveat. This definition does not check the validity of supp_start_row and
supp_end_row, e.g., the former <= the latter and they are > 0.
 
kevinherring said:
I'm sorry about this but i have looked in all the other threads and i
cant find an answer.

I want a dynamic range named 'Supplier'

The column is constant (A) but the start row and end row are dynamic
and user changable. I have two cells on a different sheet that contain
the start and end row. I have called these supp_start_row and
supp_end_row.


now i want my range 'supplier' to be:

A supp_start_row : A supp_end_row.

i have tried using offset and all that but it doesnt seem to work.

any ideas?
thanks
kevin
Click to expand...

Define the name 'Supplier' with this formula:
=OFFSET($A$1,supp_start_row-1,0,supp_end_row-supp_start_row+1,1)
 
try this. Change columns from C2 and C6 to suit.ie C1 & C1 for column A

Sub UserRange() 'Bx:Fy
x = InputBox("Enter Start row")
y = InputBox("enter Stop row")
ActiveWorkbook.Names.Add Name:="supplier", _
RefersToR1C1:="=Sheet4!R" & x & "C2:R" & y & "C6"
End Sub
 
Back
Top