Chart - Dynamic Named Range Problem

[polarisking]

I'm trying to do something I've done many, many times before - use dynamic named ranges to "automate" a chart. Both the two series, New and Total, as well as the X-Axis labels shift depending on two user inputs - a date value and a row position derived from a MATCH using the Date Value.

This is the error I receive:

The formula you typed contains an error. Try one of the following:

• Make sure you've included all parentheses and required arguments.
• To get assistance with using a function, click Insert Function on the Formulas tab (in the Function Library group).
• If you include a reference to another sheet or workbook, verify that the reference is correct.
• If you are not trying to enter a formula. avoid using an equal sign (=) or minus sign(-), or precede it with a single quotation mark (').
• For more information about common formula problems. click Help.

I've attached a file showing the problem. Thank you in advance.

 

[dan_l]

I don't think you attached it.

 

[bobhc]

Attachment has unattached ??

 

[polarisking]

Sorry about that. Here you go.

 

[dan_l]

Weird: I'm not getting an error. It's just not working. So ha. Error fixed, right?


Anyway:
indirect() returns the value of the cell you put together. So, where A6 is equal to 40, and your rowlocation is 6, doing indirect("a"&rowlocation) will return 40.

I guess you could mess around with address(rowlocation,column(a1)) as the first argument in the offset. I'm pretty sure you would need to do something else because that address result comes back as a string - not actually a usable reference.

Here's an alternative:
dates =OFFSET(Metrics!$A$2,RowLocation,0,10,1)
total =OFFSET(dnr_dates,0,1,10,1)
new =OFFSET(dnr_dates,0,2,10,1)