+ Reply to Thread
Results 1 to 5 of 5

Formula to automatically update cell entry based on whether other cells are empty or not

  1. 04-22-2022, 11:52 AM #1
    kbrown123456
    kbrown123456 is offline
    Registered User
    Join Date
    10-09-2019
    Location
    Birmingham, England
    MS-Off Ver
    Microsoft Office 365
    Posts
    5

    Formula to automatically update cell entry based on whether other cells are empty or not

    Hi All,

    I work on a lot of projects and at the moment I'm updating the status on each project by hand depending on which stages are completed or not. In an ideal world I'd love for this to automatically update itself.

    I've tried looking at nested IF formulas with and/or but I'm not getting very far with it.

    The attached example workbook lists three projects and their status based on whether there is an entry under each stage. If anyone can help me - even just to point me in the right direction for how to put something like this together I'd really appreciate it.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-22-2022, 11:58 AM #2
    AliGW
    AliGW is offline
    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
    80,830

    Re: Formula to automatically update cell entry based on whether other cells are empty or n

    Try this (you need to add Complete to column H, but this could be hidden):

    AliGW on MS365 Beta Channel (Windows 11) 64 bit

    A
    B
    C
    D
    E
    F
    G
    H
    2
    Project
    Status
    Stage 1
    Stage 2
    Stage 3
    Stage 4
    Stage 5
    Complete
    3
    		 Collection 1 Stage 4
    Yes
    Yes
    Yes
    4
    		 Collection 2 Complete
    Yes
    Yes
    Yes
    Yes
    Yes
    5
    		 Collection 3 Stage 3
    Yes
    Yes
    Sheet: Sheet1

    =LOOKUP(2,1/($C3:$G3="Yes"),$D$2:$H$2)
    Attached Files Attached Files
    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.
    Reply With QuoteRegister To Reply
  3. 04-22-2022, 12:08 PM #3
    kbrown123456
    kbrown123456 is offline
    Registered User
    Join Date
    10-09-2019
    Location
    Birmingham, England
    MS-Off Ver
    Microsoft Office 365
    Posts
    5

    Re: Formula to automatically update cell entry based on whether other cells are empty or n

    Thank you so much for responding so quickly.

    Will this also work if there is something other than "yes" entered under each stage? or can this be altered so its conditional on whether each column is empty or not?
    Reply With QuoteRegister To Reply
  4. 04-22-2022, 12:09 PM #4
    AliGW
    AliGW is offline
    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
    80,830

    Re: Formula to automatically update cell entry based on whether other cells are empty or n

    Before I answer, is there anything else about your sample data that is not realistic?

    Try this:

    =LOOKUP(2,1/(ISTEXT($C3:$G3)),$D$2:$H$2)
    Last edited by AliGW; 04-22-2022 at 12:11 PM.
    Reply With QuoteRegister To Reply
  5. 04-22-2022, 12:11 PM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Formula to automatically update cell entry based on whether other cells are empty or n

    =LOOKUP(2,1/($C3:$G3<>""),$D$2:$H$2)

    will do that.
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Update Sheet Reference Based on Cell Entry When Dragging/Copying
    By bob34253454 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-24-2015, 09:04 AM
  2. Formula to automatically populate cells based on cell value
    By WE5T in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-15-2014, 01:51 PM
  3. Filling Empty cell value based on if else condition and delete row more than 2 cells empty
    By maheshkanda in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-26-2014, 01:31 PM
  4. MACRO to empty a range of cells based on whether specified cell is empty
    By TBJV in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-01-2013, 10:35 PM
  5. Chart Display to automatically update based on cells with values?
    By MacroPolo in forum Excel General
    Replies: 1
    Last Post: 08-21-2012, 04:44 PM
  6. Increase formula by 1 row when original cell contents are not constant
    By Tulip1968 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-30-2012, 11:23 AM
  7. Update several cells based on entry
    By JStud in forum Excel General
    Replies: 10
    Last Post: 01-10-2011, 04:42 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1