View Full Version : Dynamic Named Range
jakester
Apr 18, 2011, 04:05 PM
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.
JBeaucaire
Apr 19, 2011, 01:08 AM
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.
JBeaucaire
Apr 19, 2011, 01:22 AM
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.
jakester
Apr 19, 2011, 08:25 AM
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.
JBeaucaire
Apr 19, 2011, 02:15 PM
I'll take a look. If I'm not mistaken you can attach any file type by zipping it.
JBeaucaire
Apr 19, 2011, 02:19 PM
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))