Results 1 to 10 of 10

Thread: How to create formula that will switch out information

  1. #1
    Seeker Calgaryexcel's Avatar
    Join Date
    Jul 2020
    Posts
    5
    Articles
    0
    Excel Version
    365

    Unhappy How to create formula that will switch out information



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm trying to build a replace formula, but want the data to be pulled from a list that has two columns with 107 rows. The first column would have staff names, and the second would have staff community of residence. Essentially if a staff name is entered in a cell, I want the text to be replaced with the community associated with the staff.

  2. #2
    Admin Alumnus Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,755
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Hi
    please post a sample sheet with some data and expected results added manually. Thanks
    Thank you Ken for this secure forum.

  3. #3
    Seeker Calgaryexcel's Avatar
    Join Date
    Jul 2020
    Posts
    5
    Articles
    0
    Excel Version
    365
    So if a staff name were entered in Column A it would then switch it out to reflect the associated community of residence.
    Attached Files Attached Files

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,721
    Articles
    0
    Excel Version
    MS365 Sub (Insider) Win11
    Show us manually what you want. It's not really clear what you mean by "switch it out".
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,859
    Articles
    0
    Excel Version
    O365
    You can't do that in standard Excel, but you could do it with VBA, or a sleight-of-hand with the camera tool.

  6. #6
    Seeker Calgaryexcel's Avatar
    Join Date
    Jul 2020
    Posts
    5
    Articles
    0
    Excel Version
    365
    So a formula that does this is =Substitute,INDEX(FIND,1),INDEX(REPLACE,1). The problem is that you can only nest max of 64 and I need 107+

  7. #7
    Seeker Calgaryexcel's Avatar
    Join Date
    Jul 2020
    Posts
    5
    Articles
    0
    Excel Version
    365
    Assuming the file uploaded. If you see in cell A2 I’ve entered “staff 2” which in reality would be the staffs name. Once that is typed it then needs to convert to the community beside their name in the list.
    Attached Files Attached Files

  8. #8
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,721
    Articles
    0
    Excel Version
    MS365 Sub (Insider) Win11
    The workbook still doesn’t show what you want.
    Ali
    Enthusiastic self-taught user of MS Excel!

  9. #9
    Seeker Calgaryexcel's Avatar
    Join Date
    Jul 2020
    Posts
    5
    Articles
    0
    Excel Version
    365
    Sorry that was an error on my end. So in column A you can see that I have entered various "staff names". When I do this it should then change the text to be the community in which the staff lives in.
    Attached Files Attached Files

  10. #10
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,721
    Articles
    0
    Excel Version
    MS365 Sub (Insider) Win11
    I give up.

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    Staff Name Staff Name Staff Community
    2
    Staff 2 Canyon Meadows
    3
    Staff 3 Silverado
    4
    Staff 4 Cochrane
    5
    Staff 5 Mahogany
    6
    etc Bankview
    7
    Abbeydale
    8
    Airdrie
    9
    Midnapore
    10
    Woodbine
    11
    Mayland Heights
    12
    Mount Royal
    13
    Coach Hill
    14
    Cochrane
    15
    Airdrie
    16
    Discovery Ridge
    Sheet: Sheet1
    Ali
    Enthusiastic self-taught user of MS Excel!

Posting Permissions

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