Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting Formulas to Hard Values and Vice Versa?
I have a large spreadsheet which is updated with order statuses on a weekly
basis. Each row shows a different order. In Column E is the current status of each order: "Firm" or "Tentative." In Columns O, P, and Q are formulas that calculate various milestone dates for the order. These dates recalculate throughout the life cycle of the order, but once the order status in Column F turns to FIRM, these dates are frozen. Is there a way to use a Worksheet_Change event to read the statuses in Column F and, when one changes to FIRM, to replace the values in Columns O, P, and Q on that row with whatever their current values are? And is the reverse possible in case a use makes an error? You know, if a status changes back to TENTATIVE, for the formulas in O, P, and Q to restore to formulas? (I've given the formulas names "Column_O_Calc", "Column_P_Calc", and "Column_Q_Calc") Users can do all of this manually, but the problem is that the sheet is a couple of thousand lines long and the updates numerous. To complicate things, the status info in Column F can be imported into the sheet from Oracle OR manually changed by users, so any macro would have to respond to cell changes in either case. I'd really appreciate any help anyone can offer. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting Formulas to Hard Values and Vice Versa?
You may have to use this in a worksheet calculate if it doesn't work with the oracle update but this should get you what you need! Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column < 5 Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If Target.Value = "Firm" Then With Target ..Offset(0, 11) = .Offset(0, 11).Value ..Offset(0, 12) = .Offset(0, 12).Value ..Offset(0, 13) = .Offset(0, 13).Value End With ElseIf Target.Value = "Tentive" Then With Target ..Offset(0, 11).Formula = "=Column_O_Calc" ..Offset(0, 12).Formula = "=Column_P_Calc" ..Offset(0, 13).Formula = "=Column_Q_Calc" End With End If End Sub Harold Shea;323688 Wrote: I have a large spreadsheet which is updated with order statuses on a weekly basis. Each row shows a different order. In Column E is the current status of each order: "Firm" or "Tentative." In Columns O, P, and Q are formulas that calculate various milestone dates for the order. These dates recalculate throughout the life cycle of the order, but once the order status in Column F turns to FIRM, these dates are frozen. Is there a way to use a Worksheet_Change event to read the statuses in Column F and, when one changes to FIRM, to replace the values in Columns O, P, and Q on that row with whatever their current values are? And is the reverse possible in case a use makes an error? You know, if a status changes back to TENTATIVE, for the formulas in O, P, and Q to restore to formulas? (I've given the formulas names "Column_O_Calc", "Column_P_Calc", and "Column_Q_Calc") Users can do all of this manually, but the problem is that the sheet is a couple of thousand lines long and the updates numerous. To complicate things, the status info in Column F can be imported into the sheet from Oracle OR manually changed by users, so any macro would have to respond to cell changes in either case. I'd really appreciate any help anyone can offer. -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=90445 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting Formulas to Hard Values and Vice Versa?
Lightly tested:
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range Dim myCell As Range Dim RngToInspect As Range Set RngToInspect = Me.Range("F1").EntireColumn Set myRng = Nothing On Error Resume Next Set myRng = Intersect(Target, RngToInspect) On Error GoTo 0 If myRng Is Nothing Then Exit Sub 'nothing changed in column F End If On Error GoTo ErrHandler: Application.EnableEvents = False For Each myCell In myRng.Cells With myCell Select Case LCase(.Value) Case Is = LCase("Firm") Me.Cells(.Row, "O").Value = Me.Cells(.Row, "O").Value Me.Cells(.Row, "p").Value = Me.Cells(.Row, "p").Value Me.Cells(.Row, "q").Value = Me.Cells(.Row, "q").Value Case Is = LCase("Tentative") Me.Cells(.Row, "O").Formula = "=Column_O_Calc" Me.Cells(.Row, "p").Formula = "=Column_P_Calc" Me.Cells(.Row, "q").Formula = "=Column_Q_Calc" End Select End With Next myCell ErrHandler: Application.EnableEvents = True End Sub Harold Shea wrote: I have a large spreadsheet which is updated with order statuses on a weekly basis. Each row shows a different order. In Column E is the current status of each order: "Firm" or "Tentative." In Columns O, P, and Q are formulas that calculate various milestone dates for the order. These dates recalculate throughout the life cycle of the order, but once the order status in Column F turns to FIRM, these dates are frozen. Is there a way to use a Worksheet_Change event to read the statuses in Column F and, when one changes to FIRM, to replace the values in Columns O, P, and Q on that row with whatever their current values are? And is the reverse possible in case a use makes an error? You know, if a status changes back to TENTATIVE, for the formulas in O, P, and Q to restore to formulas? (I've given the formulas names "Column_O_Calc", "Column_P_Calc", and "Column_Q_Calc") Users can do all of this manually, but the problem is that the sheet is a couple of thousand lines long and the updates numerous. To complicate things, the status info in Column F can be imported into the sheet from Oracle OR manually changed by users, so any macro would have to respond to cell changes in either case. I'd really appreciate any help anyone can offer. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting Formulas to Hard Values and Vice Versa?
Simon and Dave:
Thank you so much for both solutions! They both work wonderfully. After playing around with both versions for a while--you know, trying to break the workbook with each of them--I think I may go with Dave's version. For whatever reason, it seems sightly more responsive to more conditions, like using AutoFill to input "Firm" or "Tentative" into groups of cells. I'm definitely keeping both on hand, though--both of them are excellent, and I suspect I'll be able to modify both for similar sheets in the future. Sorry about the delay in reply, by the way. Kind of an early Summer has come to Columbus, Ohio, and I took a really long lunch. :-) "Simon Lloyd" wrote: You may have to use this in a worksheet calculate if it doesn't work with the oracle update but this should get you what you need! Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column < 5 Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If Target.Value = "Firm" Then With Target .Offset(0, 11) = .Offset(0, 11).Value .Offset(0, 12) = .Offset(0, 12).Value .Offset(0, 13) = .Offset(0, 13).Value End With ElseIf Target.Value = "Tentive" Then With Target .Offset(0, 11).Formula = "=Column_O_Calc" .Offset(0, 12).Formula = "=Column_P_Calc" .Offset(0, 13).Formula = "=Column_Q_Calc" End With End If End Sub Harold Shea;323688 Wrote: I have a large spreadsheet which is updated with order statuses on a weekly basis. Each row shows a different order. In Column E is the current status of each order: "Firm" or "Tentative." In Columns O, P, and Q are formulas that calculate various milestone dates for the order. These dates recalculate throughout the life cycle of the order, but once the order status in Column F turns to FIRM, these dates are frozen. Is there a way to use a Worksheet_Change event to read the statuses in Column F and, when one changes to FIRM, to replace the values in Columns O, P, and Q on that row with whatever their current values are? And is the reverse possible in case a use makes an error? You know, if a status changes back to TENTATIVE, for the formulas in O, P, and Q to restore to formulas? (I've given the formulas names "Column_O_Calc", "Column_P_Calc", and "Column_Q_Calc") Users can do all of this manually, but the problem is that the sheet is a couple of thousand lines long and the updates numerous. To complicate things, the status info in Column F can be imported into the sheet from Oracle OR manually changed by users, so any macro would have to respond to cell changes in either case. I'd really appreciate any help anyone can offer. -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=90445 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting Formulas to Hard Values and Vice Versa?
Dave: See my reply to Simon, just above. Thanks!
"Dave Peterson" wrote: Lightly tested: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range Dim myCell As Range Dim RngToInspect As Range Set RngToInspect = Me.Range("F1").EntireColumn Set myRng = Nothing On Error Resume Next Set myRng = Intersect(Target, RngToInspect) On Error GoTo 0 If myRng Is Nothing Then Exit Sub 'nothing changed in column F End If On Error GoTo ErrHandler: Application.EnableEvents = False For Each myCell In myRng.Cells With myCell Select Case LCase(.Value) Case Is = LCase("Firm") Me.Cells(.Row, "O").Value = Me.Cells(.Row, "O").Value Me.Cells(.Row, "p").Value = Me.Cells(.Row, "p").Value Me.Cells(.Row, "q").Value = Me.Cells(.Row, "q").Value Case Is = LCase("Tentative") Me.Cells(.Row, "O").Formula = "=Column_O_Calc" Me.Cells(.Row, "p").Formula = "=Column_P_Calc" Me.Cells(.Row, "q").Formula = "=Column_Q_Calc" End Select End With Next myCell ErrHandler: Application.EnableEvents = True End Sub Harold Shea wrote: I have a large spreadsheet which is updated with order statuses on a weekly basis. Each row shows a different order. In Column E is the current status of each order: "Firm" or "Tentative." In Columns O, P, and Q are formulas that calculate various milestone dates for the order. These dates recalculate throughout the life cycle of the order, but once the order status in Column F turns to FIRM, these dates are frozen. Is there a way to use a Worksheet_Change event to read the statuses in Column F and, when one changes to FIRM, to replace the values in Columns O, P, and Q on that row with whatever their current values are? And is the reverse possible in case a use makes an error? You know, if a status changes back to TENTATIVE, for the formulas in O, P, and Q to restore to formulas? (I've given the formulas names "Column_O_Calc", "Column_P_Calc", and "Column_Q_Calc") Users can do all of this manually, but the problem is that the sheet is a couple of thousand lines long and the updates numerous. To complicate things, the status info in Column F can be imported into the sheet from Oracle OR manually changed by users, so any macro would have to respond to cell changes in either case. I'd really appreciate any help anyone can offer. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
switching the axes, categories to values and vice versa | Charts and Charting in Excel | |||
If Hours 0, If Volume <= 0,(and vice-versa) then.... | Excel Worksheet Functions | |||
Excel columns: Converting numbers to letters and vice versa | Excel Programming | |||
Turn +ve to -ve & vice versa. | Excel Programming | |||
From pricelist to productslist and vice versa... | Excel Discussion (Misc queries) |