Display Month in a label driven from data series

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

Can an independent label have a formula reference that
looks at a particular cell and displays that value?.
I am importing data on a monthly basis, that over rights
existing data (the existing data is copied and stored for
refernece) and have a chart which updates for that months
data. I can automate the printing of the chart but what I
wish is that some label on the chart shows the month that
the data is for. The label looks at a cell and shows that
value.
If it can, what is the format for the expression ? in the
label, better still where do I start, I'm thinking along
the lines of access forms and reports but not sure if this
can be done in excel.
Something like =Date() or =Cell(x,x) or =Range(xx:xx).
Kind Regards
Bill
 
Though not technically a solution to
manipulating "labels", a useful technique is to create
a "text box" on your chart and then link it to a desired
data cell. Draw a text box ON your chart area (from the
drawing toolbar). Click on the boundary of the box (to
highlight the box itself, as opposed to the text area
inside). With the text box highlighted, click in the
formula bar and type an equal sign. Then click on the cell
you desire. Side note: if your chart is on a separate
sheet, after typing the equal sign click on "sheet tab" at
the bottom of your screen to momentarily navigate to the
data sheet. If your chart is in a wholly separate file use
the "Window" menu to do the same thing. As long as both
files are open, you can navigate to the desired cell and
Excel still understands you're working on the chart in the
first file. After finding and clicking on the desired
cell, press Enter (or click the green checkmark in the
formula bar). Excel will return you to the chart, and the
text box will now show the contents of the selected cell.
Bonus: Look at your formula bar to see the syntax of the
reference you've created. Depending on whether your chart
and data are in the same workbook OR in completely
different workbooks, the syntax should appear,
respectively, as: =Sheet1!$A$2 OR =[Book2]Sheet1!$A$2
 
Thanks,
I have followed the instructions (and I hope) correctly
and the next time the data was imported and over wrote the
old data the text box did not change, but the chart re-
populated with the new information. If the text box is
linked to the cell should it not update with the new data?
Kind Regards
Bill
-----Original Message-----
Though not technically a solution to
manipulating "labels", a useful technique is to create
a "text box" on your chart and then link it to a desired
data cell. Draw a text box ON your chart area (from the
drawing toolbar). Click on the boundary of the box (to
highlight the box itself, as opposed to the text area
inside). With the text box highlighted, click in the
formula bar and type an equal sign. Then click on the cell
you desire. Side note: if your chart is on a separate
sheet, after typing the equal sign click on "sheet tab" at
the bottom of your screen to momentarily navigate to the
data sheet. If your chart is in a wholly separate file use
the "Window" menu to do the same thing. As long as both
files are open, you can navigate to the desired cell and
Excel still understands you're working on the chart in the
first file. After finding and clicking on the desired
cell, press Enter (or click the green checkmark in the
formula bar). Excel will return you to the chart, and the
text box will now show the contents of the selected cell.
Bonus: Look at your formula bar to see the syntax of the
reference you've created. Depending on whether your chart
and data are in the same workbook OR in completely
different workbooks, the syntax should appear,
respectively, as: =Sheet1!$A$2 OR =[Book2]Sheet1!$A$2
-----Original Message-----
Can an independent label have a formula reference that
looks at a particular cell and displays that value?.
I am importing data on a monthly basis, that over rights
existing data (the existing data is copied and stored for
refernece) and have a chart which updates for that months
data. I can automate the printing of the chart but what I
wish is that some label on the chart shows the month that
the data is for. The label looks at a cell and shows that
value.
If it can, what is the format for the expression ? in the
label, better still where do I start, I'm thinking along
the lines of access forms and reports but not sure if this
can be done in excel.
Something like =Date() or =Cell(x,x) or =Range(xx:xx).
Kind Regards
Bill

.
Click to expand...
.
Click to expand...
 
To see if you're referencing the correct cell after
overwriting old data, move your cursor slowly around the
name of the month on your chart until you get the four-
arrow cursor and click. This highlights the boundaries of
the text box. Look in the formula bar to verify that the
text box is referring to the correct cell (see syntax on
earlier note).

For me, the box updates without a problem if I overwrite
my old data in one of two ways (1) copy and paste or (2)
simply type new data into the cell. Try typing something
new in the cell. If it updates, you'll know the question
lies in how you're overwriting data, and not in the text
box's reference. Finally, my instincts tell me to make
sure the text box's reference formula uses "absolute"
values, signified by the dollar signs before the column
and row: (=Sheet1!$A$1), which you can add manually in the
formula bar if they're not there.

Good luck.
-----Original Message-----
Thanks,
I have followed the instructions (and I hope) correctly
and the next time the data was imported and over wrote the
old data the text box did not change, but the chart re-
populated with the new information. If the text box is
linked to the cell should it not update with the new data?
Kind Regards
Bill
-----Original Message-----
Though not technically a solution to
manipulating "labels", a useful technique is to create
a "text box" on your chart and then link it to a desired
data cell. Draw a text box ON your chart area (from the
drawing toolbar). Click on the boundary of the box (to
highlight the box itself, as opposed to the text area
inside). With the text box highlighted, click in the
formula bar and type an equal sign. Then click on the cell
you desire. Side note: if your chart is on a separate
sheet, after typing the equal sign click on "sheet tab" at
the bottom of your screen to momentarily navigate to the
data sheet. If your chart is in a wholly separate file use
the "Window" menu to do the same thing. As long as both
files are open, you can navigate to the desired cell and
Excel still understands you're working on the chart in the
first file. After finding and clicking on the desired
cell, press Enter (or click the green checkmark in the
formula bar). Excel will return you to the chart, and the
text box will now show the contents of the selected cell.
Bonus: Look at your formula bar to see the syntax of the
reference you've created. Depending on whether your chart
and data are in the same workbook OR in completely
different workbooks, the syntax should appear,
respectively, as: =Sheet1!$A$2 OR =[Book2]Sheet1! $A$2
-----Original Message-----
Can an independent label have a formula reference that
looks at a particular cell and displays that value?.
I am importing data on a monthly basis, that over rights
existing data (the existing data is copied and stored for
refernece) and have a chart which updates for that months
data. I can automate the printing of the chart but what I
wish is that some label on the chart shows the month that
the data is for. The label looks at a cell and shows that
value.
If it can, what is the format for the expression ? in the
label, better still where do I start, I'm thinking along
the lines of access forms and reports but not sure if this
can be done in excel.
Something like =Date() or =Cell(x,x) or =Range(xx:xx).
Kind Regards
Bill

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top