MrExcel Publishing
Your One Stop for Excel Tips & Solutions

deadline without answer please help...

Posted by andre on November 10, 2000 7:33 AM

Hi All,

i have some graph in a worksheet. the X Axis is based on the Dates contained in the A Column. (A9:A256)

When i make a new query to populate my Worksheet, My A Column might have more or less entries than what it had before.

How do i specify the range of my references in my graph so it will include all my new dates but not empty cells

Posted by D on November 17, 2000 2:16 PM

Try using a named range for your column of dates, and use the Offset function to make it dynamic.

NamedRange = Sheet1!$A$1:OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A$1:$A$2500),1)

In the above example A1 would be the title ie. Date

COUNTA will count all non-blank cells (COUNT will only count numeric values)

If you have never named a range you can find this under the Insert menu (Insert>Name>Define)