Series Name Excel Chart - Is cell reference necessary?

[patkim]

In excel chart a range name that gets its value from a formula but does not refer to a cell reference is not accepted as Series Name.

e.g. sheet1!var1 =IF(Sheet1!$A$2="V1","Vendor1","Vendor2") is not accepted as series name, output is just text value not a cell ref.
sheet1!var1 =IF(Sheet1!$A$2="V1",Sheet1!$B$2,Sheet1!$C$2) is accepted as it refers to B2 or C2 cells

Is this the way it works?

I thought since text value is accepted as series name, range name based on formula returning string values should be acceptable as well.

 

[PCRIDE]

Are you talking about an actual Range you created? or just mean that in other terms?

Any formula that creates text should work for a Series, sometimes charts get out of wack because the date axis doesn't match the series values, I mean the length or number of columns/rows.

One cool trick I learned recently is using data labels on Charts but pointing those to cells where it will update automatically as they are typed in.

 

[patkim]

Thanks PCRIDE for your input. Yes, I am putting range name in place of series name as say sheet1!var1 and formula I am using is given in my first post above. Both formulas are correct however first instance is not accepted where output is plain text, not a cell reference. The range name when creating does not give error as formula is correct however when substituted as series name it errors out in first case.

 

[patkim]

Just to clarify I am not taking about the data range that reflects on the chart. It's just the name of the series that I am putting in.

 

[patkim]

See the screenshot here http://i.imgur.com/43SH2zM.jpg

 

[PCRIDE]

That formula should be in a cell not in the chart, creating your series, then the chart should just point to the cell so I would just expect to see B4, if in fact B4 had your Sheetvar formula in it.

make sense?

You can't put formulas into a chart series like that.

 

[patkim]

It's actually a defined Range Name set as var1 that internally holds formula and is referred in Series Name.
I get the point that Series Name should refer to a Cell rather than formula. I thought since absolute text say "Name1" is acceptable as Series Name, a defined Name returning text should also be possible, but does not seem so. Thanks.