+ Reply to Thread
Results 1 to 17 of 17

Formula to look up info on other worksheet and if nothing in that cell, return a blank.

  1. #1
    Registered User
    Join Date
    10-19-2017
    Location
    Fort Saskatchewan, Alberta, Canada
    MS-Off Ver
    2010
    Posts
    10

    Formula to look up info on other worksheet and if nothing in that cell, return a blank.

    This will be simple - but my brain is on overtime lol. 5 worksheets pulling info from header in "Summary". If there is info in specific cells on Summary then populate with that info. If no info in that cell on Summary, then just leave blank.

    Example:
    Sheet 2 is "Commercial". I want it to pull the Client, Project Description, Project Number, Equipment, Date, Enquiry Number and Prepared by from the first sheet - "Summary". If I just do an = formula - I get a 0 returned in the field on the Commercial worksheet. I just want it to be blank if there's nothing on the Summary sheet in that cell. I'm making a template at the moment.

    Thanks

  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: Formula to look up info on other worksheet and if nothing in that cell, return a blank

    Very simply this:

    =IF(Summary!A1="","",Summary!A1)
    Last edited by AliGW; 08-21-2018 at 01:14 PM.
    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
    10-19-2017
    Location
    Fort Saskatchewan, Alberta, Canada
    MS-Off Ver
    2010
    Posts
    10

    Re: Formula to look up info on other worksheet and if nothing in that cell, return a blank

    Sorry, didn't work.

  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: Formula to look up info on other worksheet and if nothing in that cell, return a blank

    Sorry, what didn't work? What EXACTLY did you try? Error messages? What???

  5. #5
    Registered User
    Join Date
    10-19-2017
    Location
    Fort Saskatchewan, Alberta, Canada
    MS-Off Ver
    2010
    Posts
    10

    Re: Formula to look up info on other worksheet and if nothing in that cell, return a blank

    it appears it has an issue with me putting an = in front of the coding. This cell must be a text? or ?

  6. #6
    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: Formula to look up info on other worksheet and if nothing in that cell, return a blank

    What 'coding'? What EXACTLY did you type into the cell?

  7. #7
    Registered User
    Join Date
    10-19-2017
    Location
    Fort Saskatchewan, Alberta, Canada
    MS-Off Ver
    2010
    Posts
    10

    Re: Formula to look up info on other worksheet and if nothing in that cell, return a blank

    when the cells in Summary are blank, and I just do a =cell, a 0 is returned.

  8. #8
    Registered User
    Join Date
    10-19-2017
    Location
    Fort Saskatchewan, Alberta, Canada
    MS-Off Ver
    2010
    Posts
    10

    Re: Formula to look up info on other worksheet and if nothing in that cell, return a blank

    =if(SUMMARY!C4"",""SUMMARY!C4)

  9. #9
    Registered User
    Join Date
    10-19-2017
    Location
    Fort Saskatchewan, Alberta, Canada
    MS-Off Ver
    2010
    Posts
    10

    Re: Formula to look up info on other worksheet and if nothing in that cell, return a blank

    It's adding a ' in front of my coding automatically. Cant seem to get rid of it.

  10. #10
    Registered User
    Join Date
    10-19-2017
    Location
    Fort Saskatchewan, Alberta, Canada
    MS-Off Ver
    2010
    Posts
    10

    Re: Formula to look up info on other worksheet and if nothing in that cell, return a blank

    got rid of the ', but still getting a "0" on the Commercial page when nothing is in the Summary page cell referenced.

  11. #11
    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: Formula to look up info on other worksheet and if nothing in that cell, return a blank

    There's a comma missing:

    =IF(SUMMARY!C4="","",SUMMARY!C4)

    There is no coding. This is a formula, not code.

    EDIT: Missing = added, too!
    Last edited by AliGW; 08-21-2018 at 01:53 PM.

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula to look up info on other worksheet and if nothing in that cell, return a blank

    I think there is an = also missing there?
    =IF(SUMMARY!C4="","",SUMMARY!C4)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  13. #13
    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: Formula to look up info on other worksheet and if nothing in that cell, return a blank

    Yes, there is, Ford - I missed that in post #8!!! No wonder it's not working.

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula to look up info on other worksheet and if nothing in that cell, return a blank

    The devil is in the detail lol

  15. #15
    Registered User
    Join Date
    10-19-2017
    Location
    Fort Saskatchewan, Alberta, Canada
    MS-Off Ver
    2010
    Posts
    10

    Re: Formula to look up info on other worksheet and if nothing in that cell, return a blank

    works great!! thanks

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula to look up info on other worksheet and if nothing in that cell, return a blank

    Happy to help

  17. #17
    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: Formula to look up info on other worksheet and if nothing in that cell, return a blank

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Index/ Match Formula, How to return blank cell as a blank not 0
    By MDResearcher in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-24-2016, 08:40 PM
  2. [SOLVED] When my formula encounters a blank cell it returns a 0, I need it to return a blank
    By stretch99 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-27-2014, 07:52 PM
  3. [SOLVED] Cell referenced in formula has no information displayed (shows blank), return blank cell
    By nunayobinezz in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-07-2013, 04:51 PM
  4. Replies: 1
    Last Post: 04-15-2013, 08:06 AM
  5. If formula to return blank if reference cell blank
    By dhooper in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-10-2013, 08:56 PM
  6. Replies: 2
    Last Post: 02-09-2013, 12:54 PM
  7. [SOLVED] Need formula to return blank instead of #N/A when source cell is blank, using VLOOKUP
    By TMB1234 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-03-2013, 07:16 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