Excel

[elitehak]

Hey everyone,

Hope you can help with my challenge. I will first explain my challenge, and then my questions. Feel free to answer some or all of the questions if possible, or to suggest other alternatives. As always, thanks in advance.

I have a worksheet that i create on a monthly basis (each sheet is a month). On each sheet, i have the following:

Column A: date
Column B: language
Column C: source
Column D: sender
Column E: line of business
Column F: event

All the columns (except column A) have predefined validation criteria:

Column B: english or french
Column C: internal or external
Column D: broker, client, GA
Column E: life, savings, retirement
Column F: surrender, deposit, inquiry

I make entries on a daily basis filling the lines on the sheet with the different criteria, ie each time i am on a cell, i can only choose the options of the validation : column B i can only choose english or french, etc.
The number of entries (lines) per day varies, never the same.

My questions:

1) is there a way, that i can validate that column A, B, C, D, E, and F all have to be filled, before beaing able to create another entry on another line.

2) is there a way, that as soon as i have chosen for example an option in a column that another column entry is put by default (it appears). For example, if i put english in column B i would like column D to be automatically put to the entry : broker

2) is there a way that i can create in the validation of column E, a sub-level containing the column F entries. It would look something like this:

Life : surrender, deposit, inquiry
Savings: surrender, deposit, inquiry
Retirement: surrender, deposit, inquiry

I would not need column F anymore if this would work

3) is there a way to know at the end of each day what is the total entries of each criteria. I can do this currently with a filter on top of each column, but i would like this to be automated. I can use the COUNT function also but again it is not automated. I would need something to determine that the last entry of the day has been entered, and then do the calculations.

Thanks again for all your help

Eli

[Paul Komski]

Not quite sure what you mean by validation. Do you mean an ability to just make one of a number of choices - as opposed to validating such things as:- to whether the entry is all text; or a number in a certain range; and so on in the sense of validating against certain criteria.

The simple answer is yes - all of this can be done in excel - but IMO a simple database in access, would achieve very much more, be far more flexible and be much easier to setup than using multiple spreadsheets, running macros or vba within excel, using complicated formulae, running totals on different sized sheets and relating values on one sheet to those on another sheet or form control.

If you want any help on getting a simple mdb setup then pm or email me; you would need a version of access at your disposal.