Using indifferent function and name manager to create a chart

%3CLINGO-SUB%20id%3D%22lingo-sub-204604%22%20slang%3D%22en-US%22%3EUsing%20indifferent%20function%20and%20name%20manager%20to%20create%20a%20chart%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-204604%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20a%20novice%20excel%20user%20who%20ran%20into%20a%20trouble%20about%20creating%20a%20chart%20using%20indifferent%20function%20and%20name%20manager.%26nbsp%3B%3CSPAN%3EIt%20stuck%20with%20this%20problem%20for%20a%20whole%20day%2C%20however%2C%20I%26nbsp%3Bcould%20not%20get%20my%20chart%20working.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CSPAN%3EI%20created%20two%20named%20ranges%2C%20called%20for%20example%2C%20'A'%20and%20'B'.%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E'A'%20points%20to%20the%20cell%20references%20found%20at%20C23%3AC35%20with%20the%20'INDIRECT'%20function%2C%20selecting%20the%20range%20I%20wish%20to%20chart.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E'B'%20simply%20is%20a%20same%20formula%20as%20A%2C%20but%20point%20to%20the%20cell%20references%20of%20D23%3AD35%3CBR%20%2F%3E%3C%2FSPAN%3E%3CEM%3ENote%3A%20The%20whole%20formula%20is%20%22%3DINDIRECT(ADDRESS(23%2CCOLUMN('Data'!%24B%242)%2C1%2C1%2C%22Data%22))%3AINDIRECT(ADDRESS(MATCH(1E%2B99%2C'Data'!%24B%3A%24B%2C1)%2CCOLUMN('Data'!%24B%242)%2C1%2C1%2C%22Data%22))%22%3C%2FEM%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EUnder%20the%20name%20manager%2C%20both%20of%20the%20named%20ranges%20correctly%20highlighted%20the%20range%20I%20wished%20to%20plot%2C%20but%20the%20problems%20arose%26nbsp%3Bwhen%20I%20tried%20to%20use%20these%20named%20ranges%20in%20order%20to%20create%20the%20chart.%20I%20typed%20%3D'%5BWorkbookname%5DData'!A%20and%20%3D'%5BWorkbookname%5DData'!B%20respectively%20and%20then%20I%20got%20the%20following%20error%20message.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E%22We%20have%20found%20a%20problem%20with%20one%20or%20more%20formula%20references%20in%20this%20worksheet.%20Check%20that%20the%20cell%20references%2C%20range%20names%2C%20defined%20names%20and%20links%20to%20other%20workbooks%20are%20all%20correct.%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ESo%2C%20here%20is%20my%20question.%3C%2FSTRONG%3E%20I%20doubt%20whether%20it%20is%20possible%20for%20the%20excel%20to%20create%20the%20chart%20using%20indirect%20function%20and%20name%20manager.%20Should%20I%20try%20to%20overcome%20this%20problem%2C%20if%20so%2C%20how%3F%20or%20I%20should%20turn%20to%20use%20offset%20function%20instead.%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EAny%20advice%20would%20be%20greatly%20appreciated%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EMany%20thanks%2C%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EKob%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-204604%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharts%20%26amp%3B%20Visualizing%20Data%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

Hi all,

I am a novice excel user who ran into a trouble about creating a chart using indifferent function and name manager. It stuck with this problem for a whole day, however, I could not get my chart working.


I created two named ranges, called for example, 'A' and 'B'. 
'A' points to the cell references found at C23:C35 with the 'INDIRECT' function, selecting the range I wish to chart.
'B' simply is a same formula as A, but point to the cell references of D23:D35
Note: The whole formula is "=INDIRECT(ADDRESS(23,COLUMN('Data'!$B$2),1,1,"Data")):INDIRECT(ADDRESS(MATCH(1E+99,'Data'!$B:$B,1),COLUMN('Data'!$B$2),1,1,"Data"))"

Under the name manager, both of the named ranges correctly highlighted the range I wished to plot, but the problems arose when I tried to use these named ranges in order to create the chart. I typed ='[Workbookname]Data'!A and ='[Workbookname]Data'!B respectively and then I got the following error message.

"We have found a problem with one or more formula references in this worksheet. Check that the cell references, range names, defined names and links to other workbooks are all correct."

 

So, here is my question. I doubt whether it is possible for the excel to create the chart using indirect function and name manager. Should I try to overcome this problem, if so, how? or I should turn to use offset function instead.  

Any advice would be greatly appreciated,

Many thanks,

Kob

0 Replies