+ Reply to Thread
Results 1 to 14 of 14

Change the value in a cell depending on the content of another cell

  1. #1
    Registered User
    Join Date
    03-10-2022
    Location
    Lansing, Michigan
    MS-Off Ver
    Office 365 Version 18.1903.1152.0
    Posts
    6

    Change the value in a cell depending on the content of another cell

    Hi everyone,

    I'm sort of an excel novice, so I'm hoping someone here will be able to help.

    In my excel workbook, columns A and B are used for text (Last and First names, respectively, manually entered by various users at any time of day)

    Column C contains a data validation drop-down list with two options ("Student" and "Staff")

    For confidentiality purposes, any time a staff name appears in A and B, I have to change it to something arbitrary ("Staff member")

    For staff entries like this, column C will read "Staff"

    I am currently checking entries multiple times each day so that I can catch staff entries and change them to maintain confidentiality.

    Is there ANY way that I could automate this process? I would like to maybe run something that will catch the "Staff" data validation selection in column C, then automatically change the text in columns A and B. For example, if A = "Jones" and B = "George", as soon as "Staff" is selected from the dropdown in C, I'd like A = "Staff member" and B = "Staff member"


    A couple of notes:
    1. I need this to run automatically for each new entry
    2. I need columns A, B, and C to all maintain text, NOT formulas

  2. #2
    Registered User
    Join Date
    03-10-2022
    Location
    North Carolina
    MS-Off Ver
    Professional Plus 2019
    Posts
    3

    re: Change the value in a cell depending on the content of another cell

    Can you just use conditional formatting to change the font/cell shading if a certain value is used in column C? Or do you need the name gone?

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,696

    re: Change the value in a cell depending on the content of another cell

    You would need a VBA Worksheet Change Event handler monitoring column C to do this.

    Not complex but the workbook would need to be macro enabled.

    Is that an option for you?

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,696

    re: Change the value in a cell depending on the content of another cell

    Changing the font and fill colour wouldn't stop the names showing in the formula bar.

  5. #5
    Registered User
    Join Date
    03-10-2022
    Location
    Lansing, Michigan
    MS-Off Ver
    Office 365 Version 18.1903.1152.0
    Posts
    6

    re: Change the value in a cell depending on the content of another cell

    Quote Originally Posted by Niso1922 View Post
    Can you just use conditional formatting to change the font/cell shading if a certain value is used in column C? Or do you need the name gone?
    I don't think this would suffice -- the names would still show in the formula bar

  6. #6
    Registered User
    Join Date
    03-10-2022
    Location
    Lansing, Michigan
    MS-Off Ver
    Office 365 Version 18.1903.1152.0
    Posts
    6

    re: Change the value in a cell depending on the content of another cell

    Quote Originally Posted by TMS View Post
    You would need a VBA Worksheet Change Event handler monitoring column C to do this.

    Not complex but the workbook would need to be macro enabled.

    Is that an option for you?
    Yes, the workbook is macro enabled. I just don't have any experience writing them

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,696

    re: Change the value in a cell depending on the content of another cell

    In the worksheet module, try:

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    03-10-2022
    Location
    Lansing, Michigan
    MS-Off Ver
    Office 365 Version 18.1903.1152.0
    Posts
    6

    re: Change the value in a cell depending on the content of another cell

    Quote Originally Posted by TMS View Post
    In the worksheet module, try:

    Please Login or Register  to view this content.
    Ok, that did exactly what I needed it to. Thank you!

    I do have a follow-up request -- this code permanently changes the text in A and B when "Staff" is selected from the dropdown. Is there a way the code can be changes so that it can be reverted back to the original name if the "Staff" selection is changed to "Student"?

    I'm now considering human error during entry and auditing.

  9. #9
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,007

    re: Change the value in a cell depending on the content of another cell

    I think you are wanting you cake and eating it! if you keep the names so they can be restored, you can find out the staff member

    An option is for you to have a master sheet with the data in that you store elsewhere or password protect and then you publish the anonymised sheet for other people. If it has been edited you could restore the values and republish

  10. #10
    Registered User
    Join Date
    03-10-2022
    Location
    Lansing, Michigan
    MS-Off Ver
    Office 365 Version 18.1903.1152.0
    Posts
    6

    re: Change the value in a cell depending on the content of another cell

    Quote Originally Posted by davsth View Post
    I think you are wanting you cake and eating it! if you keep the names so they can be restored, you can find out the staff member

    An option is for you to have a master sheet with the data in that you store elsewhere or password protect and then you publish the anonymised sheet for other people. If it has been edited you could restore the values and republish
    As I said in my original post, I have zero experience with these functions. I am only looking for guidance and helpful solutions. I would argue that I am trying to solve a problem, not get the best of both worlds. I don't know what's possible and what isn't -- that is why I joined this forum. I appreciate the advice in your reply, but please next time refrain from posting anything subjective (e.g. your opening sentence).
    Last edited by wthacker; 03-10-2022 at 12:36 PM.

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,696

    re: Change the value in a cell depending on the content of another cell

    Short answer is no. You could, in theory, save the cell addresses and the original contents (maybe with a date and time stamp) in a very hidden change log sheet. However, if any rows are added or deleted, that would render the logged changes useless. You could, perhaps, add a unique reference and output that instead of the static "staff". BUT, if the data is retained, it could be recovered by others.

  12. #12
    Registered User
    Join Date
    03-10-2022
    Location
    Lansing, Michigan
    MS-Off Ver
    Office 365 Version 18.1903.1152.0
    Posts
    6

    re: Change the value in a cell depending on the content of another cell

    Quote Originally Posted by TMS View Post
    Short answer is no. You could, in theory, save the cell addresses and the original contents (maybe with a date and time stamp) in a very hidden change log sheet. However, if any rows are added or deleted, that would render the logged changes useless. You could, perhaps, add a unique reference and output that instead of the static "staff". BUT, if the data is retained, it could be recovered by others.
    I may be able to hide it well enough to keep it safe. If so (and please forgive me if this is confusing -- not even really sure how to ask this), is there a bit of code that can be added to your original solution to include the "save somewhere else with date stamp" step in the function before it is replaced with "Staff"? Something to tell the function, "save what's there in location 'x', THEN replace it with "Staff"?

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    re: Change the value in a cell depending on the content of another cell

    Hi wthacker ... just a quick comment. Your thread title doesn't meet the requirements of Forum rule 1.


    A GOOD thread title is the sort of thing you would use as a search term on Google*. Your chosen title would produce a squillion useless hits. In future, please take a bit more time to think of a descriptive title that would be "Google-friendly". Many people search the forum answers looking for help and your very general title wouldn't help them at all.

    Since you are a new or very infrequent user of this site, on this occasion, I have changed it for you.

    However, if you continue to use weak titles, you can expect to have your thread BLOCKED until you change it yourself. If that happens, to edit the thread title, open the original post, click "Edit Post" (bottom right) to access the area where you can edit your title.

    *Other search engines are available!!!

    Regards,

    Glenn.
    Glenn



  14. #14
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,696

    Re: Change the value in a cell depending on the content of another cell

    Please see the sample workbook attached.

    Note that the Change Log should be set to xlSheetVeryHidden in the VB Editor. It is currently visible so that you can see the code working. You can also uncomment the line of code .Visible = xlSheetVeryHidden to ensure that it stays hidden.

    The VBA Project should be protected with a password or the code, and the properties of the worksheets will be visible.
    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. Change cell content depending on selection from drop down list
    By mack4n in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-16-2015, 04:17 AM
  2. Replies: 1
    Last Post: 04-03-2015, 06:00 PM
  3. Replies: 0
    Last Post: 09-05-2012, 02:04 PM
  4. Change ws3 Cell Content Depending On ws1 Content
    By heliskier89 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-24-2011, 04:52 PM
  5. Excel VBA Change text colour depending on another cell's content
    By jell in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-15-2010, 11:05 AM
  6. Replies: 3
    Last Post: 01-08-2008, 05:13 PM
  7. Change colour of cell depending on content
    By blain in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-17-2006, 08:47 AM

Tags for this Thread

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