+ 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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    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!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    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 2000/3/7/10/13/16
    Posts
    50,899

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    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 2000/3/7/10/13/16
    Posts
    50,899

    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 2000/3/7/10/13/16
    Posts
    50,899

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    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