+ Reply to Thread
Results 1 to 6 of 6

Ignoring Blank Cells in Formulas??

  1. #1
    Registered User
    Join Date
    08-04-2017
    Location
    Newberry, FL
    MS-Off Ver
    Excel 2016
    Posts
    3

    Ignoring Blank Cells in Formulas??

    Hi all! We're having issues with a spreadsheet at work that I just cannot figure out. Sheet 1 holds information of various invoices. Sheet 2 is meant to pull information from Sheet 1 columns A and B only when a column Z of the same row is marked as 'Recall'. At present our formula reads: =IF(ISNUMBER(SEARCH("Recall",'Sheet 1'!$Z$3:$Z$33)), 'Sheet 1'!$A$3:$A$33, "")). The issue I'm running into is that it's pulling a bunch of blank rows for cells which don't meet my criteria. I realize I specified the blank in my formula but I simply cannot figure out how to specify skipping every row that does not contain "Recall" in column Z. We've tried compound if/then statements as well as query formulas for this. I know it has to be possible but I can't for the life of me figure out a solution. I'm not great with macros, but if that solves it I will try that. I was hoping there would be a formula fix. Any suggestions?

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,131

    Re: Ignoring Blank Cells in Formulas??

    Try entering this in row one of your spreadsheet using Ctrl Shift and Enter

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then fill down until you get zeros.
    My Rules if you want my help:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    08-04-2017
    Location
    Newberry, FL
    MS-Off Ver
    Excel 2016
    Posts
    3

    Re: Ignoring Blank Cells in Formulas??

    Thanks so much for the quick response! I put that into my spreadsheet and it's not calculating the results I need. I was able to get the zeros to appear, but it's not pulling the data from sheet 1. All the cells in sheet 2 are displaying as zeros. The data I'm trying to pull is a last name from column A and its corresponding invoice number in column B. Does it effect your formula if the data I'm pulling is not a number? I'm so sorry. I should have said that in my original post. It's a bit hard to write out what issues I'm having in Excel. To me it's very hard to describe. I also can't quite figure out how to attach my demo spreadsheet so people can look at it. Sorry, this is my first time posting.

    Edit: The columns A and B from Sheet 1 are being pulled into columns A and B in Sheet 2. I'm not trying to get them in a single cell, I'm just altering the formula to reflect the desired result between columns. In theory it should be the same formula just a different range...
    Last edited by R. Turner; 08-04-2017 at 04:21 PM. Reason: Clarification

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,082

    Re: Ignoring Blank Cells in Formulas??

    Hello R. Turner and Welcome to Excel Forum.
    I believe the following array entered formula will do what you want:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    *Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    One of the nice features of this site is that you can upload samples of your workbooks so that we can work on them, which cuts down of misunderstandings. I have uploaded my interpretation of your layout and the way you would like sheet 2 to display the information.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    08-04-2017
    Location
    Newberry, FL
    MS-Off Ver
    Excel 2016
    Posts
    3

    Re: Ignoring Blank Cells in Formulas??

    Thank you so much @JeteMc! This is exactly what we were looking for. I sincerely appreciate it.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,082

    Re: Ignoring Blank Cells in Formulas??

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

+ 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. Replies: 3
    Last Post: 07-24-2017, 01:41 PM
  2. [SOLVED] Formulas within IFs. In a col, ignoring blank cells above until you reach a number then +1
    By MushroomFace in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-24-2014, 06:45 PM
  3. how to average large formulas ignoring 0 and blank
    By nsmjc in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 06-23-2014, 03:10 PM
  4. Dynamic Ranges - Ignoring Blank Cells with Underlying Formulas
    By jmm722 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-25-2014, 04:14 PM
  5. [SOLVED] Data validation using dynamic range ignoring blank formulas
    By gerainta in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-22-2013, 09:24 AM
  6. Replies: 0
    Last Post: 06-25-2012, 03:28 PM
  7. [SOLVED] Ignoring Blank/Empty Cells that contain formulas
    By pabown in forum Excel General
    Replies: 4
    Last Post: 01-25-2005, 06:06 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