Dynamic Named Range for Graph Not Working

[thatexcelguy]

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".

 

[Hui]

Try a Named Formula for the date range like:
=OFFSET('Trigger Log'!$A$1,MATCH('Trigger Log'!$N$1008,'Trigger Log'!$A:$A,0)-1,,COUNTA('Trigger Log'!$I$988:$I$991))

 

[thatexcelguy]

Thank you Hui.

I modified it a bit 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...

 

[NARAYANK991]

Hi ,

This may help :

http://dailydoseofexcel.com/archives/2005/03/01/indirect-and-dynamic-ranges/

Narayan

 

[thatexcelguy]

Actually it turns out that my original code didn't work because I didn't put the sheet name before "A". I guess the Named Ranges scope determines whether these are required or not!