Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to retain last number in a cell
In R1C3, I fill manually 1 or delete this 1 (i.e. blank). Only 2 actions in
R1C3. In R1C2, I fill,say, number 30 then overwrite 30 with say 57 then overwrite 57 with 807 & so on. R1C2 is being filled regularly manually by me with different numbers. I want in R1C1 same number of R1C2 as long as R1C3 contains 1. The moment I delete 1 from R1C3 (make it blank), I want R1C1 should retain the last number (which it should have been copying thr' an excel formula) from R1C2 & STOP further copying same number from R1C2 unless R1C3=1. How to do it? Pls give the formula in R1C1???? Many thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to retain last number in a cell
I don't think you can do this with VBA code. Any formula that 'remembers'
R1C2 will not remember it once it changes. This code will do what you want. Select the sheet and right-click on its name tab and choose [View Code] from the list and then copy and paste this code into the code module presented to you. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B1:C1")) Is Nothing Then Exit Sub End If If Range("C1") = 1 Then Range("A1") = Range("B1") End If End Sub "Sandeep Himatsingka" wrote: In R1C3, I fill manually 1 or delete this 1 (i.e. blank). Only 2 actions in R1C3. In R1C2, I fill,say, number 30 then overwrite 30 with say 57 then overwrite 57 with 807 & so on. R1C2 is being filled regularly manually by me with different numbers. I want in R1C1 same number of R1C2 as long as R1C3 contains 1. The moment I delete 1 from R1C3 (make it blank), I want R1C1 should retain the last number (which it should have been copying thr' an excel formula) from R1C2 & STOP further copying same number from R1C2 unless R1C3=1. How to do it? Pls give the formula in R1C1???? Many thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to retain last number in a cell
Thanks LLatham for yr prompt help.
But I do not know How to do ' from the list and then copy and paste this code into the code module presented to you.' Pls help immdtly & also reply whether my problem can be solved thr' this?? Is there no formula in excel for R1C1?? WAITING!!!!! "JLatham" wrote: I don't think you can do this with VBA code. Any formula that 'remembers' R1C2 will not remember it once it changes. This code will do what you want. Select the sheet and right-click on its name tab and choose [View Code] from the list and then copy and paste this code into the code module presented to you. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B1:C1")) Is Nothing Then Exit Sub End If If Range("C1") = 1 Then Range("A1") = Range("B1") End If End Sub "Sandeep Himatsingka" wrote: In R1C3, I fill manually 1 or delete this 1 (i.e. blank). Only 2 actions in R1C3. In R1C2, I fill,say, number 30 then overwrite 30 with say 57 then overwrite 57 with 807 & so on. R1C2 is being filled regularly manually by me with different numbers. I want in R1C1 same number of R1C2 as long as R1C3 contains 1. The moment I delete 1 from R1C3 (make it blank), I want R1C1 should retain the last number (which it should have been copying thr' an excel formula) from R1C2 & STOP further copying same number from R1C2 unless R1C3=1. How to do it? Pls give the formula in R1C1???? Many thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to retain last number in a cell
There is no worksheet formula for R1C1 that I know of that will do this.
This page, on my site, will help you to get the code from the post above into your workbook: http://www.jlathamsite.com/Teach/WorksheetCode.htm If you are still unable to get it to work for you, send an email to me (remove spaces from this address) Help From @ jlathamsite.com with your workbook attached and I will put the code into it and return it to you. "Sandeep Himatsingka" wrote: Thanks LLatham for yr prompt help. But I do not know How to do ' from the list and then copy and paste this code into the code module presented to you.' Pls help immdtly & also reply whether my problem can be solved thr' this?? Is there no formula in excel for R1C1?? WAITING!!!!! "JLatham" wrote: I don't think you can do this with VBA code. Any formula that 'remembers' R1C2 will not remember it once it changes. This code will do what you want. Select the sheet and right-click on its name tab and choose [View Code] from the list and then copy and paste this code into the code module presented to you. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B1:C1")) Is Nothing Then Exit Sub End If If Range("C1") = 1 Then Range("A1") = Range("B1") End If End Sub "Sandeep Himatsingka" wrote: In R1C3, I fill manually 1 or delete this 1 (i.e. blank). Only 2 actions in R1C3. In R1C2, I fill,say, number 30 then overwrite 30 with say 57 then overwrite 57 with 807 & so on. R1C2 is being filled regularly manually by me with different numbers. I want in R1C1 same number of R1C2 as long as R1C3 contains 1. The moment I delete 1 from R1C3 (make it blank), I want R1C1 should retain the last number (which it should have been copying thr' an excel formula) from R1C2 & STOP further copying same number from R1C2 unless R1C3=1. How to do it? Pls give the formula in R1C1???? Many thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to retain last number in a cell
In R1C3, I fill manually 1 or delete this 1 (i.e. blank). Only 2
actions in R1C3. In R1C2, I fill,say, number 30 then overwrite 30 with say 57 then overwrite 57 with 807 & so on. R1C2 is being filled regularly manually by me with different numbers. I want in R1C1 same number of R1C2 as long as R1C3 contains 1. The moment I delete 1 from R1C3 (make it blank), I want R1C1 should retain the last number (which it should have been copying thr' an excel formula) from R1C2 & STOP further copying same number from R1C2 unless R1C3=1. How to do it? Pls give the formula in R1C1???? Many thanks This might work. First in Tools Options Calculation check the "Iteration" box to allow circular references. Then in R1C1 put =IF(R1C3=1,R1C2,R1C1) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to retain last number in a cell
The danger with allowing circular reference calculations is that they can
hide one that you set up later that you really didn't mean to. The OP did send me a single-sheet workbook and I put the code I provided earlier into it along with including that code in a .txt file and giving what I hope are sufficiently detailed instructions on how to get it into his real-world workbook so that it can be done 'cleaner' than resorting to an intentional circular reference solution. But I suppose that if he continues to struggle with inserting the code into his workbook, that this is one possible alternative. "MyVeryOwnSelf" wrote: In R1C3, I fill manually 1 or delete this 1 (i.e. blank). Only 2 actions in R1C3. In R1C2, I fill,say, number 30 then overwrite 30 with say 57 then overwrite 57 with 807 & so on. R1C2 is being filled regularly manually by me with different numbers. I want in R1C1 same number of R1C2 as long as R1C3 contains 1. The moment I delete 1 from R1C3 (make it blank), I want R1C1 should retain the last number (which it should have been copying thr' an excel formula) from R1C2 & STOP further copying same number from R1C2 unless R1C3=1. How to do it? Pls give the formula in R1C1???? Many thanks This might work. First in Tools Options Calculation check the "Iteration" box to allow circular references. Then in R1C1 put =IF(R1C3=1,R1C2,R1C1) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to retain last number in a cell
Sandeep,
Please send new email with attachments as recently sent - system with your email address and those files crashed and I do not have access to them now. These are the 3 messages you sent on 9/9/2008 Thank You JLatham (HelpFrom @ Jlathamsite.com) "Sandeep Himatsingka" wrote: In R1C3, I fill manually 1 or delete this 1 (i.e. blank). Only 2 actions in R1C3. In R1C2, I fill,say, number 30 then overwrite 30 with say 57 then overwrite 57 with 807 & so on. R1C2 is being filled regularly manually by me with different numbers. I want in R1C1 same number of R1C2 as long as R1C3 contains 1. The moment I delete 1 from R1C3 (make it blank), I want R1C1 should retain the last number (which it should have been copying thr' an excel formula) from R1C2 & STOP further copying same number from R1C2 unless R1C3=1. How to do it? Pls give the formula in R1C1???? Many thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change number of decimals but retain original style | Excel Discussion (Misc queries) | |||
How delete formula bar and retain the computed number in a cell? | Excel Worksheet Functions | |||
How do I retain a zero as the first number in a postal code? | Excel Discussion (Misc queries) | |||
Add two cell values, retain it, and add another value | Excel Worksheet Functions | |||
retain leading zeros in number format | Excel Discussion (Misc queries) |