+ Reply to Thread
Results 1 to 14 of 14

Updating Cell

  1. #1
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    MS 365
    Posts
    532

    Updating Cell

    Hi, I have a spreadsheet which is for Sales Invoice Template. Its link to another tab call Data. I wish to be able to have a "update" function.

    Please see sample excel for details. Thanks!!!!
    Attached Files Attached Files

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Updating Cell

    Possible with VBA. See attached for one quickly thrown together method of doing this.

    Essentially what it does is update the details on the second sheet to reflect changes made on the first sheet, then adds the VLOOKUPs again.

    BSB
    Attached Files Attached Files

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Updating Cell

    I am a little confused by your request. Do you wish to amend existing records in the Data tab or create new records in the data tab. These are two different functionalities which would need to be addressed.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    MS 365
    Posts
    532

    Re: Updating Cell

    Hi, Badlyspelledbouy, your vba work but only when the customer appear only 1 time in the Data Tab. I tried to "update" other invoice, if the customer name appear more than 1 time, it will automatically update the most bottom data.

    Hi, Alansidman, the "Sales" tab is actually where viewer can see the details. I have created another easily sample. The previous one is Column to Row which i think should be more troublesome. Actually, all i wan it to do is Copy and Paste the Row to its respective Row in Tab Data.
    Attached Files Attached Files

  5. #5
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Updating Cell

    Quote Originally Posted by jp16 View Post
    Hi, Badlyspelledbouy, your vba work but only when the customer appear only 1 time in the Data Tab. I tried to "update" other invoice, if the customer name appear more than 1 time, it will automatically update the most bottom data.
    I am unable to replicate that. If I update SA1606004 then changes are recorded on row 5 and not on Peter's other order on row 7.
    So all appears to be working for me.

    BSB

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Updating Cell

    You have not responded to my question. Are you updating the existing records or creating new ones. Your explanation of your situation does not elaborate on this.

  7. #7
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    MS 365
    Posts
    532

    Re: Updating Cell

    Hi, BadlySpelledBuoy, your VBA work, sorry.... However, the problem is, my excel actually has a lot of fills. Maybe 50. I will take a long time to do like how u do, especially when i am not so good at VBA. Hence i am thinking of Copy and Paste (Please see my Sample question 2). I think it should be easilier right??

  8. #8
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    MS 365
    Posts
    532

    Re: Updating Cell

    Hi, Alansidman, its actually Viewing and Updating, not creating new entry.

  9. #9
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Updating Cell

    I don't see how that approach would be easier. It's adding an extra element that is unnecessary.

    If you mean you have lots more fields that would need pasting then you could use TRANSPOSE and negate the need for the hidden row 1.

    BSB

  10. #10
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    MS 365
    Posts
    532

    Re: Updating Cell

    Hi. Anyone can help? Need it urgently.

  11. #11
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Updating Cell

    Perhaps if you could explain what is wrong with the solution provided already I would be easier for someone to help.

    BSB

  12. #12
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    MS 365
    Posts
    532

    Re: Updating Cell

    Hi. Because my "sales" tab is actually in horizontal. Not vertical. In sales tab, row 1 is actually the header, hence, sales invoice is at A2, date at B2, customer name C3, qty at D2 while amount is at E2. Hence actually all i need is to find the sales invoice in A2 and select the entire row and just copy to the respective Data tab row. I only realise its horizontal format after you send me the first solution. Hence i do up another sample. I'm afraid you will get **** off if i confess my first sample is wrong.... Sorry... But please help...

  13. #13
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    MS 365
    Posts
    532

    Re: Updating Cell

    Hi, BadlyspellBouy, please refer to the Excel. I want user to be able to Manually change the value at the Sales Tab. Then upon clicking on a button, the whole thing is copy and paste in the recpective row in the Data Tab. However, I wish to Retain the Vlookup formula in Sales Tab.
    Attached Files Attached Files

  14. #14
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Updating Cell

    Hopefully this will suit your needs.

    BSB
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 09-05-2015, 08:45 PM
  2. Replies: 1
    Last Post: 05-14-2014, 08:24 AM
  3. [SOLVED] Testing colour of cell and result cell not updating automatically
    By BishBosh in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-11-2013, 08:55 AM
  4. [SOLVED] Editing cell value in txtbox + updating that cell value in database..going MAD!!
    By MB_vba in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-17-2013, 10:29 PM
  5. Help with automatic chart updating/auto updating today function
    By Tux2424 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-26-2012, 04:45 PM
  6. macros for watching a cell and updating other cell
    By aravindkb7 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-12-2010, 02:54 PM
  7. Restricting cell entry in a Shared Workbook when a user updating a cell
    By Starbucks Junkie in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-02-2009, 09:22 AM

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