Validation and filters with sub scrolls
eli
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
Leo Heuser
"eli" <elit...@hotmail.com> skrev i en meddelelse
news:04f501c348e0$f468f820$a001...@phx.gbl...
This can be accomplished using VBA in the change-event of the sheet,
but this event isn't fired in versions prior to Excel 2000,
when data is chosen from a validation box (as far as I recall!).
So, which version are you using?
BTW always disclose that fact.
>
> 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
See comment above.
>
> 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
>
I believe, it's not possible.
> 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.
>
You could set up a list somewhere on the sheet with
dates down and headings across. In the datacells you
then use the SUMPRODUCT-function.
With dates in e.g. M3:M33 and headings in N2:Z2,
"English" in N2, "French" in O2, "Internal" in P2 etc.
something like:
=SUMPRODUCT(($A$2:$A$1000=$M3)*($B$2:$B$1000=N$2)+0)
for cell N3.
--
Best Regards
Leo Heuser
MVP Excel
Followup to newsgroup only, please.