Dynamic Named Range for Graph Not Working

Hi,

I am pulling my hair out on this one.

I have a graph in Excel and I want it to update based on two cells I have for the Start Date and End Date.

I am using a Named Range for the Axix label range, but get an error when trying to use it ("There's an error in the formula you entered.")

The formula I am using in the Named Range is:

=OFFSET(INDIRECT("A"&MATCH('Trigger Log'!$N$1008, 'Trigger Log'!$A$1:$A$9999,0)),0,0,COUNTA(INDIRECT("A"&MATCH('Trigger Log'!$N$1008,'Trigger Log'!$A$1:$A$9999,0)&":$A9999")))

...where N1008 is the cell of my Date Start. The formula seems to resolve properly into a range, but I can't seem to get it to work. I'd paste the steps here, but Excel doesn't allow me to copy from the Evaluate Formula dialog!

I was previously using: "=OFFSET('Trigger Log'!$A$2,0,0,COUNTA('Trigger Log'!$A:$A)-1)" which works, but I really want to be able to be able to quickly choose the start and end dates for my graph...

Any help would greatly be appreciated.

I've attached a modified version of my Excel file here (I removed personal details since this is for a personal medical log). You will get an error due to it not liking my Named Range: "GraphDateDynamic" -- the graph is at the bottom of the data and is currently using a deleted Named Range since it won't accept "GraphDateDynamic".

Thanks protonLeah - I will try your solution in a bit as it's always good to learn new things.

I modified my old formula a bit (with the help of somebody in another forum) to include the end date range too, and got it working:

=OFFSET('Trigger Log'!$A$1,MATCH('Trigger Log'!$N$1008,'Trigger Log'!$A:$A,0)-1,,MATCH('Trigger Log'!$O$1008,'Trigger Log'!$A:$A,0)-(MATCH('Trigger Log'!$N$1008,'Trigger Log'!$A:$A,0)-1))

I then created another Named Range for the series (the same formula as above, but just adding an 8 where the Cols parameter goes - simple!).

I guess that OFFSET won't take INDIRECT, or INDIRECT won't work with dynamic ranges or something? I'm not sure why my original formula didn't work... it evaluated fine...

Originally Posted by xlnitwit

Hi,

The problem was just that you didn't include a sheet name in the INDIRECT formula- you needed
=OFFSET(INDIRECT("'Trigger Log'!A"&MATCH('Trigger Log'!$N$1008, 'Trigger Log'!$A$1:$A$9999,0)),0,0,COUNTA(INDIRECT("'Trigger Log'!A"&MATCH('Trigger Log'!$N$1008,'Trigger Log'!$A$1:$A$9999,0)&":$A9999")))

Thanks! I guess it was a question of scope then, as my original Named Range had a scope of Workbook. When I tried setting the scope to the sheet, then it worked. Aha! Thanks again.

And protonleah, thanks for your solution. It works and is a nice alternative!