+ Reply to Thread
Results 1 to 8 of 8

How do I: getting Offset to ignore blank cells

  1. 03-27-2020, 05:22 PM #1
    Learning ExL
    Learning ExL is offline
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    How do I: getting Offset to ignore blank cells

    good evening all,

    how do i get an =offset function to ignore blank cells, i have col "D6:D999" but because of the way i have data inputted there are blank cells so in D6 i may have "215487" and in D12 i have "548754" i need the =offset function to ignore the blank cells in between D7 and D12. this is my formula as it stands

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  2. 03-27-2020, 05:25 PM #2
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: How do I: getting Offset to ignore blank cells

    How about, what are you trying to accomplish?
    HTH
    Regards, Jeff
    Reply With QuoteRegister To Reply
  3. 03-27-2020, 05:28 PM #3
    Learning ExL
    Learning ExL is offline
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: How do I: getting Offset to ignore blank cells

    it will be used in Datavalidation
    Reply With QuoteRegister To Reply
  4. 03-27-2020, 05:30 PM #4
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: How do I: getting Offset to ignore blank cells

    I would do this

    Named Range
    MyList ='MASTER MASTER'!$D$6:INDEX('MASTER MASTER'!$D:$D,MATCH(99^99,'MASTER MASTER'!$D:$D))

    Note: On second thought, are you saying you want the named range to only return the data and no blanks?
    Reply With QuoteRegister To Reply
  5. 03-27-2020, 05:40 PM #5
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: How do I: getting Offset to ignore blank cells

    Is this what you mean? Check out the Data Validation in L2 and then add a number to D9
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 03-27-2020, 06:02 PM #6
    Learning ExL
    Learning ExL is offline
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: How do I: getting Offset to ignore blank cells

    Thank you, i did not realise i had to create two lists, ill give a go and get back to you.

    thank you again
    Reply With QuoteRegister To Reply
  7. 03-27-2020, 06:27 PM #7
    Learning ExL
    Learning ExL is offline
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: How do I: getting Offset to ignore blank cells

    worked like a dream but what does the 99^99 do?
    Reply With QuoteRegister To Reply
  8. 03-27-2020, 10:39 PM #8
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: How do I: getting Offset to ignore blank cells

    You are very welcome and thanks for the feedback.

    99^99 -- It's a way to find the last value in a range. There are other numbers that can be used, but I took the lazy way out on this one versus defining a BigNumber named range with this link will describe.

    http://www.xldynamic.com/source/xld.LastValue.html

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    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. Return value for first consective blank cell, ignore proceeding blank cells
    By hollywoodj20 in forum Excel General
    Replies: 2
    Last Post: 08-13-2018, 02:30 AM
  2. [SOLVED] Ignore blank cells with formulas or ignore NA() in a formula
    By guiismiti in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 03-24-2016, 02:13 PM
  3. [SOLVED] Return non-blank cells from range AND ignore cells with only formula w/o results
    By grafitti in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-06-2014, 08:15 AM
  4. [SOLVED] Macro merge cells separated by comma, ignore blank cells
    By lagiosman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-22-2014, 04:49 PM
  5. Ignore blank cells and truly blank cells in named range?
    By hschillig in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-13-2014, 02:56 PM
  6. Conditional formatting. Highlight cells that meet a criteria but ignore blank cells.
    By CEM in forum Excel General
    Replies: 5
    Last Post: 05-18-2013, 07:49 PM
  7. How can I make a macro ignore blank cells? - Blank Cells have formulas
    By mz1161 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-30-2013, 08:54 AM

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