Hi all. I'd like to add dynamic text in a chart. The user should enter the text in a cell and this text should appear in an SPECIFIC part of the chart. How to do that??
Thx a lot.
You can link chart text items, title, axis titles and data labels, to cells by selecting the text item and then in the formula bar typing the full cell reference.
for instance,
=Sheet1!A1
Hi, thx for the quick response, but still the problem is unsolved....I would explain it more carefully. The basic functioning of the spreadsheet is as follows:
1) The user writes some values about certain product and a plot in the chart is created.
2) The user writes some especifications about that product and they appear within the plot.
The problem is that We don't beforehand where the text is gonna be since it depens on the plot position. So the question would be if it is possible to find a function which allows to write a text box in certain position. (Within a chart or spreadsheet)
I could give you more details if needed.
THX A LOT
Hi, thx for the quick response, but still the problem is unsolved....I would explain it more carefully. The basic functioning of the spreadsheet is as follows:
1) The user writes some values about certain product and a plot in the chart is created.
2) The user writes some especifications about that product and they appear within the plot.
The problem is that We don't beforehand where the text is gonna be since it depens on the plot position. So the question would be if it is possible to find a function which allows to write a text box in certain position. (Within a chart or spreadsheet)
I could give you more details if needed.
THX A LOT
Hi, thx for the quick response, but still the problem is unsolved....I would explain it more carefully. The basic functioning of the spreadsheet is as follows:
1) The user writes some values about certain product and a plot in the chart is created.
2) The user writes some especifications about that product and they appear within the plot.
The problem is that We don't beforehand where the text is gonna be since it depens on the plot position. So the question would be if it is possible to find a function which allows to write a text box in certain position. (Within a chart or spreadsheet)
I could give you more details if needed.
THX A LOT
You can use the same trick to link a textbox, embedded in a chart, and link it with a cell.
If you want the textbox positioned relative to any data in the chart then the simplest way is to use a data label.
An example workbook may help us understand your problem
OK!Here you can find the spreadsheet and the and a .jpg image how i would like it to look like.
The user modifies the products, the name of the products, and values so the end up matching in the same position in the chart.
Thx a lot for your time.
The picture makes a look clearer.
See attached. I've used xy-scatter chart to draw the 10 items.
Simply update the table of data and all elements should update.
If the data is outside of the existing min and max's you may need to extend the fake axes values and labels.
The scale labels and titles are provided by linking data labels to cells.
Hi Andi, thank you very much for your help! It was an amazing solution to my problem, you are more than a master.
I've checked your additional things and I'm able to understand it so I could go forward in case I want to change length axis and so on...
Only one more question if it does not bother you. Is it also possible for the user to set the color of the boxes by writing in any cell or something like that? I know it is possible by clicking the boxes but the other way I think is better.
Only in conjunction with VBA code.
Or I suppose you could set up multiple duplications of each product plot and colour each one of those. The via formula only output the correct colours value.
The colour choice have to be restricted to say 10 colours. And the selection would be by colour description rather than appearance. That is a list of colors like Rad/Blue/Green.....
OK! Thank you. I'll continue struggling with it.
Hi Andy. I'm working hard with the file you sent. I'm having problems for changing the labels. I don't find out the way I could change the scaling. You said that you used links for the labels, but where are they?? How to change it??
thx once more.
The labels for the products are data labels displaying the Series Name. Only the last point in each of those series has a data lable applied.
The axis label ones a linked to a cell.
If you select the labels and then select an individual label you will see the formula in the formula bar. So for Speed (m/s) the formula is =Products!$AG$17
The axis labels are also done in this way.
To change the labels that current exist just change the content of cells
$Q$15:$Q$20 and $T$15:$T$20
It's getting a bit clearer. But still confusing with some parts. When i try to add a simple point (see Z20) it does not appear together with the rest in the picture. What am I missing?
When you add information in to Z20 you will need to do the following.
Extend the formula in AA20 and AB20
Extend the series to include these cells.
You should see data labels appear but they will be label with the series name.
So select the data labels
Select spefici data label
In the formula bar enter cell reference.
Change alignment of data label to Left, via the Format Data Labels dialog.
You will also need to update the formula in AF18 to increase the plot of the Y axis.
You will find this free add-in makes the linking of data labels to cells easier.
http://www.appspro.com/Utilities/ChartLabeler.htm
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks