Excel Charts - need help designing/customising charts!

[jamessl]

I think this might be complicated, but I am hoping it isn't.

Firstly, I have data that will need to be imported from a table in Microsoft Access. An example of the data fields is -

Employee Number Time Out Time In

161 07:00 06:00
84 08:00 05:00
23 07:00 09:00

There can be a variable number of these records but not more than 33.

I need a chart which has a 'permanent' y-axis scale of the time intervals 07:00 08:00 09:00 up to 07:00 again ie a 24 hour period. The x-axis must be a 'permanent' scale of 1 2 3 4 5 etc up to 33.

Now even more trickery required. The data within the chart will be the fields from the table mentioned above.

Best to show in a picture I think, after all isn't that what charts are for.

07:00
06:00 161
05:00 161 84

etc

09:00 161 84 23
08:00 161 84 23
07:00 161 23
1 2 3 4 etc 31 32 33

So as can be seen, the chart displays the Employee Number for the given Time Out/Time In Period as a range between these values in the actual detail of the chart.

It is a booking chart so it is very handy to be able to see the Employee numbers within the chart, and at each given interval, so I can see what employee has what equipment at any given time.

Does anyone know if this is possible? I thought a customized stock chart might be the way to go, but I don't know how to make the necessary customizations.

any help greatly appreciated!!

 

[xlbo]

Why use a chart ? - your little table there seems to provide exactly what you want........keep it simple - keep it as a table HTH
~Geoff~

 

[jamessl]

I wish I could but, I need to have the data displayed in a graphical format so that the data is user friendly, ie the users of the system can see all of the time intervals in a 24 hour period and the employee number at each time interval (between the Time Out and Time In values for the give employee number). Looking at the chart customization options in Excel (Excel 2000 by the way) I am wondering if this can be done at all??

....Help!!

 

[xlbo]

I still say you should use a table - just add a few lines and some bacjground colour - you'll be amazed at what you can make it look like - I honestly don't think you're gonna be able to do this as a chart

You will also need a record for each hour that the item was out (or a sub to derive this 'cos neither a chart, nor a table is gonna like dealing with time periods vs actual time slots

Oh yeh, final point, your table will need an item reference number as well to keep things straight (1-33)
HTH
~Geoff~

 

[Guest_imported]

You need to use Excel as follows per work sheet
1) store your raw data in a simple database table on a worksheet as a series of transaction rows
2) use an excel pivot table to display the raw data ie PVT are summaries by definition. Include only columns you want to then chart
3) chart the pivot table just right click over the PVT and let excel do the rest

The data table must be a complete database table.
ie no empty columns - they effectively break one table into two separate tables.

Each table column MUST be a named range (a single column of cells including the column title must be inclusive in the named range).

Create a pivot table based upon the data table.
Having named the columns in the original data table you use the same range names in the pivot table. (if you choose to create more than one pivot table you will need to uniquely rename the pivot table headings while keeping the original data column the same - yes you can do that)

There is a check box you can check to ensure all blank values are displayed - thus the pvt will have a static dimension rather than a dynamic dimensions for only the data that is non-blank.

It would be nice to give you an example as its very easy to implement your requirement... but it takes abit of planning to ensure its as easy as that.

NB: there are some functionality such as axis labelling which you can force to be regular ie every hour etc regardless of data found this makes things look very professional.

After all said and done all you have to do is update the data and refresh all pivot tables for everything to be updated including the charts.


Hope i have given you some tips - I use this again and again and it works a treat, you can use forms to even input values to the data table.

all the best
Jay

 

[Guest_imported]

You need to use Excel as follows per work sheet
1) store your raw data in a simple database table on a worksheet as a series of transaction rows
2) use an excel pivot table to display the raw data ie PVT are summaries by definition. Include only columns you want to then chart
3) chart the pivot table just right click over the PVT and let excel do the rest

The data table must be a complete database table.
ie no empty columns - they effectively break one table into two separate tables.

Each table column MUST be a named range (a single column of cells including the column title must be inclusive in the named range).

Create a pivot table based upon the data table.
Having named the columns in the original data table you use the same range names in the pivot table. (if you choose to create more than one pivot table you will need to uniquely rename the pivot table headings while keeping the original data column the same - yes you can do that)

There is a check box you can check to ensure all blank values are displayed - thus the pvt will have a static dimension rather than a dynamic dimensions for only the data that is non-blank.

It would be nice to give you an example as its very easy to implement your requirement... but it takes abit of planning to ensure its as easy as that.

NB: there are some functionality such as axis labelling which you can force to be regular ie every hour etc regardless of data found this makes things look very professional.

After all said and done all you have to do is update the data and refresh all pivot tables for everything to be updated including the charts.


Hope i have given you some tips - I use this again and again and it works a treat, you can use forms to even input values to the data table.

all the best
Jay