+ Reply to Thread
Results 1 to 8 of 8

Offset do not consider blank cell in name range

  1. #1
    Forum Contributor
    Join Date
    05-19-2015
    Location
    Karnataka, INdia
    MS-Off Ver
    MS Office 2010
    Posts
    277

    Offset do not consider blank cell in name range

    Hi Experts,

    I have created several Name range for easier calculation using Offset

    Please Login or Register  to view this content.
    Now what happen is if nay cell is blank in the name range calculation leaves Error. Any suggestion to consider blank cell in the name range. I am using CountIfs function for calculating the result. Such as

    Please Login or Register  to view this content.
    Thanks!
    Biplab

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Offset do not consider blank cell in name range

    does your named range produce the same size range in both ?
    ie you cannot have one with 50 and the other with 100 entries

    blank cells in any part of the range effectively cuts back the size of the range
    so it really comes back to the intent of your named ranged in the first place
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Offset do not consider blank cell in name range

    I too agree with humdingaling if you have different size ranges you will get error, criteria range should be for each criteria range but if you have list of criteria you can add sumproduct function. Sumproduct can handle array calculation and can give result.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Offset do not consider blank cell in name range

    I also agree with humdingaling. There are alternative ways to build dynamic named ranges that circumvent problems caused by blank cells.

    I also agree with shukla.ankur281190. We won't get far without a workbook.
    Dave

  5. #5
    Forum Contributor
    Join Date
    05-19-2015
    Location
    Karnataka, INdia
    MS-Off Ver
    MS Office 2010
    Posts
    277

    Re: Offset do not consider blank cell in name range

    Sample file attached. Result section is H4:H8 using COUNTIFS function. Currently it returned error because Cells C15 through C21 is blank.

    Just a thought, do you think if i use below, it will work without any issue. If not please advise

    Please Login or Register  to view this content.
    Thanks in advance!
    Attached Files Attached Files

  6. #6
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Offset do not consider blank cell in name range

    change named range references as below

    =OFFSET(Sheet1!$B$2,0,0,MATCH("zzzzz",Sheet1!$A:$A)-1)
    =OFFSET(Sheet1!$A$2,0,0,MATCH("zzzzz",Sheet1!$A:$A)-1)
    =OFFSET(Sheet1!$C$2,0,0,MATCH("zzzzz",Sheet1!$A:$A)-1)
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  7. #7
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Offset do not consider blank cell in name range

    Just want to highlight on nfl's solution

    use the same column reference "MATCH("zzzzz",Sheet1!$A:$A)-1" in each named range
    otherwise if your last row has blank in either column A,B or C then you will get error like before

  8. #8
    Forum Contributor
    Join Date
    05-19-2015
    Location
    Karnataka, INdia
    MS-Off Ver
    MS Office 2010
    Posts
    277

    Re: Offset do not consider blank cell in name range

    Thanks all for your help especially nflsales

+ 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. If offset range is not blank move 3 cells right
    By Sc0tt1e in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-11-2014, 06:15 AM
  2. [SOLVED] Offset to blank cell
    By Strup in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-05-2013, 06:24 PM
  3. [SOLVED] How to stop OFFSET including one blank row at the bottom of the range...?
    By strud in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2013, 08:53 AM
  4. [SOLVED] Find first non-blank cell after a group of blanks copy offset cells, find next blank, loop
    By gwsampso in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-21-2012, 06:11 AM
  5. insert offset blank cell
    By George in AZ in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-23-2010, 02:07 AM
  6. Re: Select last cell before blank, then offset
    By bryant_16 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-19-2009, 07:51 AM
  7. Select last cell before blank, then offset
    By jeff.p in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-04-2007, 06:43 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