Thanks Don. Your suggestion got me thinking. Let me give you the specific
example. I thought a generic example would make it easier but now I don't
think that is the case. Here is specific problem I'm trying to solve.

In this code below, I'm trying to define the Range name MF_Rate_wo_GR to be
one of cells C4 to G4 on the worksheet named MCT UR wo GR. This code
below doesn't work. Can you help me?

Select Case (MF_MIPS)
Case 0 To 100
Range("MF_Rate_wo_GR") = Worksheets("MCT UR wo
GR").Cells(4, "C")
Case 101 To 500
Range("MF_Rate_wo_GR") = Worksheets("MCT UR wo
GR").Cells(4, "D")
Case 501 To 1001
Range("MF_Rate_wo_GR") = Worksheets("MCT UR wo
GR").Cells(4, "E")
Case 1002 To 5000
Range("MF_Rate_wo_GR") = Worksheets("MCT UR wo
GR").Cells(4, "F")
Case Else
Range("MF_Rate_wo_GR") = Worksheets("MCT UR wo
GR").Cells(4, "G")
End Select


"Don Guillett" wrote in message
...
Right click sheet tabview code insert this. I suspect that you may want
to change your categories after testing

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("a1")) Is Nothing Then Exit Sub
Select Case Target
Case Is < 100: Range("xyz") = Cells(1, "b")
Case 100 To 200: Range("xyz") = Cells(2, "b")
Case 201 To 300: Range("xyz") = Cells(3, "b")
Case 301 To 400: Range("xyz") = Cells(4, "b")
Case Else
Range("xyz") = ""
End Select
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kent McPherson" wrote in message
...
My bad. Sorry.

I'm OK with doing it in a macro.

Input cell = A1 = 100

Possible Name Values:
B1 = 1
B2 = 2
B3 = 3
B4 = 4

If A1<=100, then define range name XYZ = B1
If A1 100 and <200, then define range XYZ = B2
If A1 200 and <300, then define range XYZ = B3
If A1 300, then define range XYZ = B4



"Don Guillett" wrote in message
...

A formula can only return a value. You would need a macro and more
explanation to us.
Your a1 a2 below don't seem to be consistent. Typo?
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kent McPherson" wrote in message
...
I have a formula which references a cell which has been defined as a
Name under Insert/Name/Define... The formula works just fine.
However I want the named definition to be variable based on the input of
another cell. For example:

Input cell = A1 = 100

Possible Name Values:
B1 = 1
B2 = 2
B3 = 3
B4 = 4

If A1<=100, then define range name XYZ = B1
If A2 100 and <200, then define range XYZ = B2
If A2 200 and <300, then define range XYZ = B3
If A2 300 and <400, then define range XYZ = B4

Ideas?