+ Reply to Thread
Results 1 to 6 of 6

Ignoring Blank Cells in Formulas??

  1. 08-04-2017, 02:23 PM #1
    R. Turner
    R. Turner is offline
    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?
    Reply With QuoteRegister To Reply
  2. 08-04-2017, 03:40 PM #2
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    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 General Rules if you want my help. Not aimed at any person in particular:

    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.
    Reply With QuoteRegister To Reply
  3. 08-04-2017, 04:10 PM #3
    R. Turner
    R. Turner is offline
    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
    Reply With QuoteRegister To Reply
  4. 08-04-2017, 06:11 PM #4
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,548

    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.
    Reply With QuoteRegister To Reply
  5. 08-08-2017, 08:11 AM #5
    R. Turner
    R. Turner is offline
    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.
    Reply With QuoteRegister To Reply
  6. 08-08-2017, 08:43 AM #6
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,548

    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 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. Ignoring Blank Cells But Keeping Index of Non-Blank Cells w/ Data Validation and ScrollRow
    By rogerd1 in forum Excel Programming / VBA / Macros
    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. compact column, ignoring blank (but contain formulas) rows, creating named range w result
    By ex99125b in forum Excel General
    Replies: 0
    Last Post: 06-25-2012, 03:28 PM
  7. 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1