Data Label Values from Cells

%3CLINGO-SUB%20id%3D%22lingo-sub-1567410%22%20slang%3D%22en-US%22%3EData%20Label%20Values%20from%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1567410%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20have%20a%20line%20graph%20with%20data%20labels%20from%20a%20cell%20range.%26nbsp%3B%20It%20looks%20like%20this%3A%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%3D'Template'!%24CM%2412%3A%24CM%2435%3C%2FP%3E%3CP%3EHowever%2C%20I%20have%20code%20that%20duplicates%20this%20worksheet%20many%20times%3B%20and%20I%20need%20each%20sheet's%20labels%20to%20refer%20to%20it's%20own%20label%20data%20range%20instead%20of%20the%20original%20Template.%26nbsp%3B%20When%20I%20try%20removing%20the%20sheet%20name%20from%20the%20label%20range%20in%20the%20Template%2C%20I%20get%20the%20message%20%22The%20reference%20is%20not%20valid.%26nbsp%3B%20References%20for%20titles%2C%20values%2C%20sizes%2C%20or%20data%20labels%20must%20be%20a%20single%20cell%2C%20row%2C%20or%20column.%22%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%24CM%2412%3A%24CM%2435%20is%20a%20single%20column%2C%20so%20I'm%20not%20sure%20why%20this%20isn't%20working.%26nbsp%3B%20Any%20suggestions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1567410%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1582873%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Label%20Values%20from%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1582873%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F750249%22%20target%3D%22_blank%22%3E%40ToddA2001%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Todd%2C%3CBR%20%2F%3EThis%20can%20be%20done.%3CBR%20%2F%3EThe%20sheet%20name%20MUST%20always%20be%20in%20the%20chart%20reference.%3CBR%20%2F%3EPlace%20this%20formula%20somewhere%20on%20the%20main%20spreadsheet.%3CBR%20%2F%3E%3DMID(CELL(%22filename%22%2CA1)%2CFIND(%22%5D%22%2CCELL(%22filename%22%2CA1))%2B1%2C255)%3CBR%20%2F%3EThis%20will%20call%20out%20the%20name%20of%20that%20spreadsheet%20in%20the%20cell%20you%20place%20this%20in%2C%20but%20only%20if%20you%20have%20already%20saved%20the%20workbook.%20It%20will%20call%20out%20the%20new%20name%20when%20a%20new%20spreadsheet%20is%20created.%20Use%20that%20name%20in%20an%20indirect%20formula%20that%20is%20part%20of%20a%20named%20range%2C%20and%20use%20that%20named%20range%20in%20place%20of%20the%20reference%20in%20the%20chart.%3CBR%20%2F%3EIn%20my%20test%2C%20I%20put%20the%20above%20formula%20in%20cell%20G29.%20In%20cell%20d23%20through%20j23%20I%20placed%20random%20numbers%2C%201-7.%20I%20highlighted%20those%207%20numbers%20and%20selected%20a%20simple%20bar%20chart%2C%20placing%20it%20next%20to%20the%20row%20of%20numbers.%20I%20named%20my%20first%20sheet%20tester%20and%20named%20the%20spreadsheet%20TRIAL.XLSX%3CBR%20%2F%3EIn%20another%20random%20cell%2C%20I%20used%20D4%2C%20I%20put%20%3DINDIRECT(%22'%22%26amp%3B%24G%2429%26amp%3B%22'!%24d%2423%3A%24j%2423%22)%3CBR%20%2F%3EMind%20all%20%E2%80%9C%2C%20%E2%80%98%20and%20%24.%20Copy%20and%20paste%20this%20adjusting%20for%20the%20cells%20but%20do%20not%20change%20the%20syntax.%3CBR%20%2F%3EIt%20should%20display%20the%20number%20that%20is%20in%20the%20row%2029%20cell%20it%20is%20over%2C%20but%20that%20does%20not%20really%20matter.%20We%20are%20wanting%20to%20make%20sure%20the%20indirect%20formula%20is%20working.%20Click%20on%20the%20formula%20in%20D4%20and%20copy%20it.%3CBR%20%2F%3ENow%2C%20open%20the%20name%20manager%20under%20the%20Formulas%20tab.%3CBR%20%2F%3ESelect%20New%2C%20and%20give%20it%20a%20name%20with%20letters%20only.%20I%20chose%20the%20word%20TRIBE%20for%20no%20reason.%20In%20the%20Refers%20to%20box%2C%20erase%20what%20is%20there%20and%20paste%20your%20indirect%20formula%20in%20it%2C%20making%20sure%20the%20%3D%20is%20there.%20Say%20ok.%20This%20should%20take%20you%20to%20a%20bigger%20box%20that%20displays%20all%20the%20names%20in%20the%20manager.%20Just%20close%20it.%3CBR%20%2F%3EClick%20on%20one%20of%20the%20bars%20in%20the%20chart%20to%20display%20the%20formula%20for%20the%20chart.%20It%20will%20look%20like%20SERIES(%2C%2C'tester'!%24D%2423%3A%24J%2423%2C1).%3CBR%20%2F%3EChange%20it%20to%20%3D%20SERIES(%2C%2Ctrial.xlsx!tribe%2C1)%3CBR%20%2F%3EThat%20is%20using%20the%20name%20of%20the%20spreadsheet%2C%20not%20the%20worksheet%2C%20and%20the%20name%20of%20the%20range%20created%20in%20the%20name%20manager.%20As%20you%20copy%20this%20sheet%2C%20the%20tab%20name%20will%20change%2C%20and%20the%20indirect%20formula%20will%20pick%20that%20up.%20You%20can%20change%20the%20name%20of%20the%20tab%20and%20it%20will%20still%20work.%3CBR%20%2F%3EYou%20do%20not%20need%20to%20keep%20all%20of%20the%20indirect%20formulas%20in%20the%20d4-j4%20range.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1584943%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Label%20Values%20from%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1584943%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F628423%22%20target%3D%22_blank%22%3E%40SqueakySneakers%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%26nbsp%3B%20I'll%20give%20this%20a%20try%20soon.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi,

I have a line graph with data labels from a cell range.  It looks like this:  

='Template'!$CM$12:$CM$35

However, I have code that duplicates this worksheet many times; and I need each sheet's labels to refer to it's own label data range instead of the original Template.  When I try removing the sheet name from the label range in the Template, I get the message "The reference is not valid.  References for titles, values, sizes, or data labels must be a single cell, row, or column." 

=$CM$12:$CM$35 is a single column, so I'm not sure why this isn't working.  Any suggestions?

 

2 Replies
Highlighted

@ToddA2001 

Hi Todd,
This can be done.
The sheet name MUST always be in the chart reference.
Place this formula somewhere on the main spreadsheet.
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
This will call out the name of that spreadsheet in the cell you place this in, but only if you have already saved the workbook. It will call out the new name when a new spreadsheet is created. Use that name in an indirect formula that is part of a named range, and use that named range in place of the reference in the chart.
In my test, I put the above formula in cell G29. In cell d23 through j23 I placed random numbers, 1-7. I highlighted those 7 numbers and selected a simple bar chart, placing it next to the row of numbers. I named my first sheet tester and named the spreadsheet TRIAL.XLSX
In another random cell, I used D4, I put =INDIRECT("'"&$G$29&"'!$d$23:$j$23")
Mind all “, ‘ and $. Copy and paste this adjusting for the cells but do not change the syntax.
It should display the number that is in the row 29 cell it is over, but that does not really matter. We are wanting to make sure the indirect formula is working. Click on the formula in D4 and copy it.
Now, open the name manager under the Formulas tab.
Select New, and give it a name with letters only. I chose the word TRIBE for no reason. In the Refers to box, erase what is there and paste your indirect formula in it, making sure the = is there. Say ok. This should take you to a bigger box that displays all the names in the manager. Just close it.
Click on one of the bars in the chart to display the formula for the chart. It will look like SERIES(,,'tester'!$D$23:$J$23,1).
Change it to = SERIES(,,trial.xlsx!tribe,1)
That is using the name of the spreadsheet, not the worksheet, and the name of the range created in the name manager. As you copy this sheet, the tab name will change, and the indirect formula will pick that up. You can change the name of the tab and it will still work.
You do not need to keep all of the indirect formulas in the d4-j4 range.

@SqueakySneakers 

Thanks!  I'll give this a try soon.