Really simple Excel question

[akn846]

FOrgive me if this seems a stupid question, but I can't think of how to do this !!

I've just been asked to record 5 pieces of information, for 5 different departments on an ongoing weekly basis.

How is the best way to organise this in Excel - as I will also have to produce reports as well!!

Any help would be appreciated

MAny thanks

Andy

 

[Hasit]

Give each of the departments their own worksheet. You could have a 6th worksheet which consolidates the 5 departments to give an overall view. This is only if you have a lot of data.

The other option is to set aside blocks of cells for each department and name them "Dept 1", "Dept 2" etc

What sort of and how much information are you going to be asked to record? Also, will you have to perform calculations etc on the information?

 

[ETID]

If you need to keep historic data and not overlay values each month then please setup your data in column and row format...


Dept. data1 data2 data3 data4 data5
_____ _____ _____ _____ _____ _____
sales 1 grn 3/3/01 x01 b
mkting 5 red 3/5/01 b99 z
prod. 4 blu 3/6/01 z70 r

All of excels built in tools are designed to analyze col and row much easier than report format.
....If you setup your data in report format, you will have a nightmare when it comes to reprorting totals and stats.

For this reason I recommmend 1 sheet for all

 

[akn846]

Hasit

In terms of the information that is to be reported, it is going to be 5 numeric values for each department for each period (currently expected to be weekly).

I will be asked to present the data in chart format as well - and I am looking for a process which will simplify/automate this as much as possible.

ETID

Basically I think the problem is that the data I want to store/report on has 3 dimensions to it 1) the department name 2) 5 different values related to 5 different KPIs 3) the period (ie week).

Thanks for your input so far

 

[Hasit]

akn846, I still stick with my original recommendation of a sheet for each department. It makes seperating the data for each department a lot easier and certainly from my point of view easier to manage.

I created a similar KPI spreadsheet, except for an entire organisation, and based on around 30 KPIs. I had a sheet for each year, and then had a consolidation sheet, which presented all the information, on a per group basis as well as for the entire company. I was also able to produce charts on that basis.

As the sheet grows with old(er) data, you can perform analysis and comparisons of each department against themselves and others.

 

[akn846]

Hasit

Thanks very much - I shall try your suggestion over the next few days, so don't be suprised if I yell for help!!

Thanks

 

[Hasit]

akn846: Shout as much as you need to, I'll be happy to help. If you are really stuck, feel free to contact me at:

hasitbakhda@yahoo.co.uk