Chart using dynamic range name that includes if formula and is on another workbook

[markmellow]

I am trying to make a chart that changes dynamically according to data on another workbook. The workbook with data, named "mPacking" has a sheet named Machine. I made a named range called "testing" in the name manager with this formula,

Please Login or Register  to view this content.

It seems to get the range that I want correctly, even when using the IF formula. When i go to the workbook with the chart, select and add the series with the named range, =mPacking.xlsm!testing , I get an error saying invalid path. I am not sure which step am I doing wrong since a lot of stuff is going on.

[nickmangan]

hi, can you please post a sample spreadsheet?

[markmellow]

here is the workbook with fake data...

Edit:
ooops, make M10's value equal to "2013Week14" , then the named range may show up semi working.


I might post better examples later, I'm really off work by now... Happy Weekend everybody!

[markmellow]

I have found my problem, this part, (INDIRECT("D"&COUNTA(Machine!$D:$D))), does not work on dynamic named ranges. Online posts say that indirect function does not work with dynamic ranges, as it will leave a #ref... I don't know the workaround of it yet, but at least i was able to isolate the problem.

[dlow]

I don't know if it will work. This is an old post too, but if I want to drop stuff from my formulas I add IFERROR to the beginning of the formula & fallow the prompts to suit my needs.

Here is a sample from something I am working on, so it wont fit your need, but gives you an example.

Please Login or Register  to view this content.

I think IFERROR is 07 & up.

All the best,

dirty