Please help with date insertion

[SM]

Not very good with excel so hope you can help

I want to have a table with the date in column A
a figure in column B wihich calculates a figure in colomn C
Have worked out the formula for column B & C and a graph drawn for these
figures.
What I want to know is 2 things
1. how can I automatically have the date inserted in Column A when I enter
this No?
2. How can I have a graph that only takes the sample data from colummn B
against C for the last 7 days, constantly updating itself evry day that a
new figure is entered
Hope I have made myself clear
dates from say 1st-31st of each month in column A and the data in columns
B&Cand a 7day graph from the last 7 entries
Thanks in advance

Stephen Murphy

 

[Earl Kiosterud]

Stephen,

For the automatic date insertion, you'll need a macro. You can also use the
fill handle, which will increment the date as you drag it down.

For the last 7 days in a chart, define three names (Insert - Name - Define)

Name: Last7DaysX
refers to: OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A$2:$A$65000)-6,0,7,1)

Name: Last7DaysB
refers to: OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$A$2:$A$65000-6,0,7,1)

Name: Last7DaysC
refers to: OFFSET(Sheet1!$C$1,COUNTA(Sheet1!$A$2:$A$65000)-6,0,7,1)

This presumes you have headings in row 1 (you should), and the data starts
in row 2.

Now make the chart, and in Source Data - Series - Series 1, in the "Category
(X) axis labels" box, put:

=Book1.xls!Last7DaysX

In the Values box, put
=Book1.xls!Last7DaysB

Add a series (Add button), and do the same with it selected in the left box,
using
=Book1.xls!Last7DaysC

This presumes you have one of each date in column A (no duplicate dates),
and no empty dates before the last one.

 

[SM]

Stephen,

For the automatic date insertion, you'll need a macro. You can also use the
fill handle, which will increment the date as you drag it down.

For the last 7 days in a chart, define three names (Insert - Name - Define)

Name: Last7DaysX
refers to: OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A$2:$A$65000)-6,0,7,1)

Name: Last7DaysB
refers to: OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$A$2:$A$65000-6,0,7,1)

Name: Last7DaysC
refers to: OFFSET(Sheet1!$C$1,COUNTA(Sheet1!$A$2:$A$65000)-6,0,7,1)

This presumes you have headings in row 1 (you should), and the data starts
in row 2.

Now make the chart, and in Source Data - Series - Series 1, in the "Category
(X) axis labels" box, put:

=Book1.xls!Last7DaysX

In the Values box, put
=Book1.xls!Last7DaysB

Add a series (Add button), and do the same with it selected in the left box,
using
=Book1.xls!Last7DaysC

This presumes you have one of each date in column A (no duplicate dates),
and no empty dates before the last one.

Thanks will give this a go

SM