|
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? |
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? |
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? |
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? |
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? |
| All times are GMT +1. The time now is 07:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com