+ Reply to Thread
Results 1 to 8 of 8

Dynamically alter table header and dropdown list

  1. #1
    Registered User
    Join Date
    05-25-2020
    Location
    trowbridge
    MS-Off Ver
    Office 365
    Posts
    10

    Dynamically alter table header and dropdown list

    Hi

    In the attached workbook when any name in the staff table in Setup is changed I would like to automatically change:-

    The header names (Name1 to Name10) in the Wages Table in the Wages worksheet to the new name entered.

    The names in the drop-down list to select the staff member to create the list for on the CIS Report worksheet.

    Is this possible?

    Pete
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Dynamically alter table header and dropdown list

    Yes, but it’s not practical. You can change the names, but not where the entires are. The wages sheet takes manual entries: if you change the names, the values will not move with the names, so you would and up with misaligned data.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    05-25-2020
    Location
    trowbridge
    MS-Off Ver
    Office 365
    Posts
    10

    Re: Dynamically alter table header and dropdown list

    Hi AliGW

    I do not think that would be a problem.
    If a name is changed it is because a person has left then the data in that column would be deleted.
    If a new name is added then there would be no data connected to that name.

    Pete

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Dynamically alter table header and dropdown list

    Yes, but if the name is added before the data has been added, you’ve got a problem. It isn’t a safe way of working.

  5. #5
    Registered User
    Join Date
    05-25-2020
    Location
    trowbridge
    MS-Off Ver
    Office 365
    Posts
    10

    Re: Dynamically alter table header and dropdown list

    Many thanks for your advice.

    I will have to live with manual changes to each item but by implication that will give a problem as well.
    It would seem that you are saying once a name is allocated to a column then even if all the data in that column is deleted then the name cannot be changed and the column reused for another new member of staff.
    I don't really understand why but if that is the case I will have to find an alternative method of achieving this task.

    Pete

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: Dynamically alter table header and dropdown list

    As to the data validation in cell C5 on the CIS Report sheet, it will change as the names in Settings A5:A22 change. If you would like to not have the blanks in the dropdown list however, change the Source to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Registered User
    Join Date
    05-25-2020
    Location
    trowbridge
    MS-Off Ver
    Office 365
    Posts
    10

    Re: Dynamically alter table header and dropdown list

    Many thanke
    Pete

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: Dynamically alter table header and dropdown list

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. Already selected values from DropDown list dynamically updating on multiple sheets
    By nyuffancs in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-26-2018, 04:38 PM
  2. Replies: 2
    Last Post: 12-25-2017, 09:57 PM
  3. Alter Generic text created by dropdown
    By Yidolee in forum Excel General
    Replies: 7
    Last Post: 01-19-2016, 01:57 PM
  4. [SOLVED] Dynamically Alter Macro Based on Tab Name or Cell Value
    By mandora in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-09-2014, 07:50 PM
  5. Replies: 21
    Last Post: 12-02-2009, 03:27 PM
  6. Dynamically view and alter records
    By tubbsy in forum Excel General
    Replies: 1
    Last Post: 02-12-2008, 03:14 PM

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