+ Reply to Thread
Results 1 to 12 of 12

how do i get data to automatically transfer to diff column each time new data entered

  1. #1
    Registered User
    Join Date
    07-06-2011
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    6

    how do i get data to automatically transfer to diff column each time new data entered

    hi-i have a worksheet that i need to format so that data can be entered/tallied on a regular basis. Ex: data is entered in 'column D' and later need data is entered in 'column D' but the previous data that was entered needs to be tallied over to 'column C' without having to retype, etc. and this will happen several times thoroughout the worksheets use. I also need to LOCK certain columns/rows while allowing others to be changed.

    I will be the person formating and using the worksheet but different people will be the ones entering the data in 'column D' and that is all they will do each time they open the worksheet is update the column with thier new numbers.

    I HAVE ATTACHED THE WORKSHEET FOR REFERENCE
    Attached Files Attached Files
    Last edited by Phx2010; 07-13-2011 at 03:32 PM. Reason: title too vague

  2. #2
    Valued Forum Contributor
    Join Date
    06-17-2009
    Location
    Chennai,India
    MS-Off Ver
    Excel 2003,excel 2007
    Posts
    678

    Re: how do i get data to automatically transfer to diff column each time new data ent

    "previous"
    previous in time or in space.

    give two or three examples

  3. #3
    Registered User
    Join Date
    07-06-2011
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: how do i get data to automatically transfer to diff column each time new data ent

    ex: Mon someone would enter '12' on column D

    Tue someone would enter '4' on column D (same exact cell/space) so i need the '12' from Mon's entry to move over to column C

    Wed someone would enter '8' on column D (same exact cell/space) so i need the '4' from Tue's entry to move over to column C and ADD to the '12' that moved over from Mon's entry

    this would happen several times in the course of the worksheet being used on the project.

  4. #4
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2003
    Posts
    4,168

    Re: how do i get data to automatically transfer to diff column each time new data ent

    hi, Phx2010, please check attachment, try to insert values in D11:D24 range. I hope I understood you right.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-06-2011
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: how do i get data to automatically transfer to diff column each time new data ent

    Hi watersev, your attachment is doing the same as mine; i can put values in D11:D24; however, when i put 'new' values in D11:D24 it does not transfer the previos values to C11:C24...............

    maybe what i am trying to do cannot be done.......?

    EX: (D11) enter value of 12 on Mon;
    (D11) enter value of 4 on Tue-Value of 12 should now show on (C11) as 12
    (D11) enter value of 8 on Wed-Value of 8 should not show on (C11) as 16; which would be the 12 plus the 4, and this would continue each time a 'new' value was entered in (D11)

    Appreciate your Help!!

  6. #6
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2003
    Posts
    4,168

    Re: how do i get data to automatically transfer to diff column each time new data ent

    hi, Phx2010, please check attachment, insert 12, 4, 8 in D11 and check result.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-06-2011
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: how do i get data to automatically transfer to diff column each time new data ent

    watersev,
    THANKS! it works exactly as i needed.
    if you can let me know how u did it so i can use for the rest of my projects i will be so grateful
    i do not know if i can use in my Master worksheet for each project so each worksheet that is created for future projects will always have this formating.
    Last edited by Phx2010; 07-12-2011 at 11:24 AM. Reason: did not complete my reply

  8. #8
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2003
    Posts
    4,168

    Re: how do i get data to automatically transfer to diff column each time new data ent

    hi, Phx2010, there are 3 codes used:

    1. Workbook_open event: openning workbook triggers running code "data"
    2. Standard module level code: takes D11:D24 range into array
    3. Worksheet_Change event: updates array with new value entered and calculates prior values sum in C range

    If you are happy with the offered solution, please mark the thread as Solved, see forum rules for details: http://www.excelforum.com/forum-rule...rum-rules.html
    Last edited by watersev; 07-13-2011 at 09:50 AM.

  9. #9
    Registered User
    Join Date
    07-06-2011
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: how do i get data to automatically transfer to diff column each time new data ent

    hi watersev, i have another ? now; when i use ur attached sheet from 7/11/11 it no longer 'works' so now i have nothing to 'reference' to as i am trying to fomat my additional sheets AND how do i fix errors? EX: if someone mistakenly puts the wrong value in colum D, how do i correct it?
    Thanks!!

  10. #10
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2003
    Posts
    4,168

    Re: how do i get data to automatically transfer to diff column each time new data ent

    that was not in question, you asked how to bring to life your idea and provided example, I've written code for you.
    What should happen if value is wrong? The easiest way is to add confirmation window and proceed if answer is yes. Though it will not resolve the issue. All the other ways can be explored only if example is provided.

  11. #11
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2003
    Posts
    4,168

    Re: how do i get data to automatically transfer to diff column each time new data ent

    Quote Originally Posted by Phx2010 View Post
    when i use ur attached sheet from 7/11/11 it no longer 'works' so now i have nothing to 'reference' to as i am trying to fomat my additional sheets
    I suppose you need to run the following code beforehand:

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    07-06-2011
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: how do i get data to automatically transfer to diff column each time new data ent

    watersev, Thank you, i do appreciate your help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1