Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,365
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,365
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 213
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,365
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,365
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Change number of decimals but retain original style Alice Graham Excel Discussion (Misc queries) 3 July 9th 07 03:27 PM
How delete formula bar and retain the computed number in a cell? Les P. Excel Worksheet Functions 3 May 13th 06 08:50 PM
How do I retain a zero as the first number in a postal code? Laureen Excel Discussion (Misc queries) 7 February 28th 06 06:34 PM
Add two cell values, retain it, and add another value Jay Excel Worksheet Functions 7 April 22nd 05 06:24 PM
retain leading zeros in number format Jeff Excel Discussion (Misc queries) 5 February 22nd 05 10:24 PM


All times are GMT +1. The time now is 07:43 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"