Date-orientated update of cells in different spreadsheets
 

Dear Excel experts!

Hope I am right here...

I would like to create a customer-orientated & task daily checklist.
Basically, I want to have a single spreadsheet (spreadsheet1) and worksheet
(worksheet1) with customer names along the left (1 per row) and tasks along
the top (1 per column) which I update on a daily basis. Once the task has
been performed for a customer, a value will be written in the appropriate
field i.e. for customer1, the task1 result "passed" would be typed into B2,
however, for customer2, task1 might have "failed" which would be typed into
C2.

I would then like this value "passed" or "failed" (or other value) input
automatically written to another customer-oriented speadsheet
(speadsheet_cust1). This value needs to be written in the correct
spreadsheet (determined by the name of the customer in spreadsheet1) and
appropriate date-orientated field depending on the day & month the task was
actually performed in the original worksheet.

If this can be done, I can quickly fill out a daily checklist, the results
of which are automatically written to their respective
customer-spreadsheets, which I could open to see the results of tasks on a
daily / monthly basis. For this 'destination' customer-orientated
spreadsheet, my thoughts were a new spreadsheet per customer (named
spreadsheet_cust1, spreadsheet_cust2...) and separate worksheets per month
(worksheet_jan, worksheet_feb...), with days of the month along the left (1
day per row) and task along the top (1 per column).

Finally, I don't mind if the original checklist spreadsheet1 requires input
for the month (i.e. value 10 for October) and day (value 12 for the 12th) -
if this is automatic, even better. From these values, perhaps the correct
customer speadsheet's worksheet cell position could be determined?

Is this possible and do I make any sense at all? I am new to Excel and have
little function or programming experience - go easy on me! Thanks for your
comments or tips.. .

There's probably a hundred different ways of approaching this, but
one way which I think will give you want you want fairly simply is
like this ...

Column headings:
A = Date
B = Customer
C = Task
D = Result

You could use Data Validation on these columns, linked to lists of
acceptable values. This would speed up data entry & avoid typos. (But
I suggest leaving this step out until you're happy that the rest of
the suggestion gives you what you want.)

Once the database is populated, then create a Pivot Table from it,
using Customer in the Page Area, Date in the Column Area, Task in the
Row Area, also put Result in the Row Area, and Count of Result in the
Data Area.

When the Pivot Table is generated, you can use the Customer drop-down
to show results for a particular Customer.

Give it a go, check out the help file on Pivot Tables, see if it
gives you what you want.

hth,
ScottO



"omb" wrote in message
...
| Dear Excel experts!
|
| Hope I am right here...
|
| I would like to create a customer-orientated & task daily
checklist.
| Basically, I want to have a single spreadsheet (spreadsheet1) and
worksheet
| (worksheet1) with customer names along the left (1 per row) and
tasks along
| the top (1 per column) which I update on a daily basis. Once the
task has
| been performed for a customer, a value will be written in the
appropriate
| field i.e. for customer1, the task1 result "passed" would be typed
into B2,
| however, for customer2, task1 might have "failed" which would be
typed into
| C2.
|
| I would then like this value "passed" or "failed" (or other value)
input
| automatically written to another customer-oriented speadsheet
| (speadsheet_cust1). This value needs to be written in the correct
| spreadsheet (determined by the name of the customer in
spreadsheet1) and
| appropriate date-orientated field depending on the day & month the
task was
| actually performed in the original worksheet.
|
| If this can be done, I can quickly fill out a daily checklist, the
results
| of which are automatically written to their respective
| customer-spreadsheets, which I could open to see the results of
tasks on a
| daily / monthly basis. For this 'destination' customer-orientated
| spreadsheet, my thoughts were a new spreadsheet per customer (named
| spreadsheet_cust1, spreadsheet_cust2...) and separate worksheets
per month
| (worksheet_jan, worksheet_feb...), with days of the month along the
left (1
| day per row) and task along the top (1 per column).
|
| Finally, I don't mind if the original checklist spreadsheet1
requires input
| for the month (i.e. value 10 for October) and day (value 12 for the
12th) -
| if this is automatic, even better. From these values, perhaps the
correct
| customer speadsheet's worksheet cell position could be determined?
|
| Is this possible and do I make any sense at all? I am new to Excel
and have
| little function or programming experience - go easy on me! Thanks
for your
| comments or tips.. .
|
|