Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Dynamic Named Range (https://www.askmehelpdesk.com/showthread.php?t=571242)

  • Apr 18, 2011, 04:05 PM
    jakester
    Dynamic Named Range
    I use a named dynamic named range in my workbook for a daily report. After I copy into a spreadsheet a bunch of data, I manipulate some of the data. Some of the rows get deleted that I don't need. The odd thing is after I delete all of the unnecessary rows and create the named dynamic range, one blank row keeps appearing in the range. I then try to delete the blank row but when I view the range in the edit mode, I still see that the blank row is included.

    Here is the formula that I am using:

    =OFFSET('Daily Summary'!$A$2,0,0,COUNTA('Daily Summary'!$A:$A),COUNTA('Daily Summary'!$1:$1))

    Can I add another layer of code to ignore a blank row?

    Thanks.
  • Apr 19, 2011, 01:08 AM
    JBeaucaire

    Perhaps your method using COUNTA is off. I'd need to see the data and compare that to the dynamic named range address results to be sure.

    Post up the workbook and let's take a look.
  • Apr 19, 2011, 01:22 AM
    JBeaucaire

    Perhaps your method using COUNTA is off. I'd need to see the data and compare that to the dynamic named range address results to be sure.

    Post up the workbook and let's take a look. I use many dynamic named range techniques, I'm not sure I've ever used the one you've presented.
  • Apr 19, 2011, 08:25 AM
    jakester
    1 Attachment(s)
    Quote:

    Originally Posted by JBeaucaire View Post
    Perhaps your method using COUNTA is off. I'd need to see the data and compare that to the dynamic named range address results to be sure.

    Post up the workbook and let's take a look. I use many dynamic named range techniques, I'm not sure I've ever used the one you've presented.

    Jerry - here's a modified copy of the workbook... just one sheet in this example.

    Incidentally, AMHD won't allow .xlsx docs to be attached so I had to save this as Excel 2003 with some loss of fidelity.

    Thanks.
  • Apr 19, 2011, 02:15 PM
    JBeaucaire

    I'll take a look. If I'm not mistaken you can attach any file type by zipping it.
  • Apr 19, 2011, 02:19 PM
    JBeaucaire

    Well, I hate to say is seems obvious, but you do know what COUNTA() does, don't you?

    COUNTA($A:$A) will give you a count of every cell in column A that has a value in it, including row 1 where your titles are. Since you obviously want the OFFSET() to skip row 1, you will need to subtract 1 from the count of column A.

    =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,COUNTA(Sheet1!$1:$1))

  • All times are GMT -7. The time now is 01:23 AM.