+ Reply to Thread
Results 1 to 9 of 9

VBA for AutoUpdate Column Based on Table Array

  1. #1
    Registered User
    Join Date
    01-18-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    15

    Question VBA for AutoUpdate Column Based on Table Array

    Hi:
    I have a 2-column table on Sheet 2 defining status of a person as active or inactive.
    I need to update the status of the person on sheet 1 automatically based upon the name selected from the drop down list (defined in the name manager for sheet 1)
    Please advise.

  2. #2
    Registered User
    Join Date
    01-18-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: VBA for AutoUpdate Column Based on Table Array

    I know vlookup and how to use it in a sheet, however, i want to include a VBA script at sheet level to auto update the column. Reason i am not using vlookup function in cells is that the sheet needs to be appended with auto-import and then parsed for auto-export - and to this extent, the last-row calculations go haywire.

  3. #3
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: VBA for AutoUpdate Column Based on Table Array

    Add the following code to worksheet 1 and make the necessary changes to meet your situation:

    Please Login or Register  to view this content.
    If you like my contribution click the star icon!

  4. #4
    Registered User
    Join Date
    01-18-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: VBA for AutoUpdate Column Based on Table Array

    Hi Ollie!
    My apologies for sounding confused but i am having a bit of trouble executing your code. To clarify matters let me put things in perspective -
    1. I need to update info on worksheet named "LEAD MASTER" where depending on the name from pick-list in Column X (Coming from name manager on sheet DATA column T) i want to update column Y with the status.
    2. the NAme and Status are in a table format in a sheet "DATA" with Name residing in column T and Status in Column U.

    Kindly advise on code modifications.

  5. #5
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: VBA for AutoUpdate Column Based on Table Array

    Try

    Please Login or Register  to view this content.
    PS you need to insert this code in the code module of the LEAD MASTER worksheet!

  6. #6
    Registered User
    Join Date
    01-18-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: VBA for AutoUpdate Column Based on Table Array

    Hi Ollie!
    It works fine with the exception when data is appended to the table or existing data needs to be refreshed. Kindly advise if an auto-update loop can be created.
    Thanks Much!

  7. #7
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: VBA for AutoUpdate Column Based on Table Array

    Parijaat,

    - when data is appended to the table on the DATA worksheet the new entries will automatically be taking into consideration, no changes to code needed

    - refreshing the existing values is not possible without writing a specific VBA routine to do so (if you wanted online/realtime updates, you should have opted for a LOOKUP formula)

  8. #8
    Registered User
    Join Date
    01-18-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: VBA for AutoUpdate Column Based on Table Array

    Hi Ollie!

    Can you please suggest a code in form of a macro to lookup the array and replace values in the other sheet? I am having serious trouble when i copy and paste the name of person in column 24. May be we do away with sheet-level code and run a macro manually to update the corresponding values in column Y.

    I will really appreciate your kind help in this matter.

  9. #9
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: VBA for AutoUpdate Column Based on Table Array

    Can you post a copy of the workbook?

+ 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