| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Named Cell
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dynamic Named Cell
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dynamic Named Cell
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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dynamic Named Cell
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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dynamic Named Cell
Due to your range names and "define", I'm not sure I understand what you want. If desired, send your workbook along with a snippet of this email pasted into a newly inserted sheet along with a clear explanation and I'll take a look. -- Don Guillett Microsoft MVP Excel SalesAid Software "Kent McPherson" wrote in message ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I have a cell value define a dynamic named range? | Excel Worksheet Functions | |||
| Dynamic Named Range | Excel Discussion (Misc queries) | |||
| Change the starting and ending cell of a dynamic named range | Excel Discussion (Misc queries) | |||
| Dynamic named list includes blank cell at bottom | Excel Discussion (Misc queries) | |||
| dynamic named ranges | Excel Programming | |||
![Kent McPherson[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
