A
anon
Hi all,
Over the last year i've built a pretty good (and quite large) planning
wb. Before anybody mentions I know i am trying to get excel to act as
a db, i originally started design in access and it was rejected by my
company (users not familiar with access). Knowing what i do now i
should have built in access and used excel as the front, however i'm
pot commited now as the build so far has taken a year part time!
Essentially the sheet stores customer details. These are split into 6
geographical areas (1 to 6). Each of the customers are also split
further into 5 more categories (A -E) which represent the type of
customer. The wb was designed for a user to be able to plan when to
visit their customers.
They would plan a category a day, then select customers from that
category which would programatically be put into the calendar (a ws in
excel). So on 1st April they might plan to to be in area 4. They would
select customers from the ones in area 4 and use a button 'plan' which
would run code to copy the customer details into the day on the
calendar worksheet relating to the 4th april.
This process works however it means each day has to be planned
individually, and with 11 customers a day this is a lengthy process.
So I have been asked to automate further some of this process.
I am aiming for;
- Monthly calendar view, possibly populated by code so i do not have
to have a worksheet for each month
- Ability for the wb to automate some of the planning process based on
selections inputted by the user (eg. user inputs date, geographical
area and customer type into a form and the sheet selects 11 customers
that match the selections)
- When planning automatically as above will need to wb. to consider
the date when the customer was last visited, and only plan if was
longer than a certain number of days since the last visit
-Ability when planning as above to not plan the same customer twice.
In one month a user might use the same criteria to plan and i need the
wb to select unique customers each time. Although further to this if a
user deletes a customer already planned from the calendar i need the
workbook to recognise the deletion and consider it for planning next
time
- Ability to plan 6 working days automatically as above and then leave
a day empty ie. only plan 6 working days in every 7 working day
cycle.. It is crucial this is automatic.
So the issues i have whilst trying to understand the best way to do
this;
- Calendar design - i would like the user to be able to view a month
(similar to outlook) and expand each day (as in outlook) so they can
see all entries. (If i wanted entries to all be visible at once the
monthly calendar would be very large). I guess this is a form
triggered by double clicking on the cell and auto populating with data
- i presume this is possible and therefore i simply need to discover
how to populate the form
- Automation part reasonably simple, however cannot figure how to
manage not planning the same customers in twice in a month if the same
criteria is passed to the planner. Thought about putting an entry in a
cell that relates to the customer record, however this will prevent
the customer being planned in again if the user deletes where it is
planned
- No idea how to do the 6 working days!
I think i need to start building a huge amount of code, however would
much appreciate any thoughts or ideas that may make my life (which
feels like it is going to be spent coding for the foreseeable future!)
any easier!
Over the last year i've built a pretty good (and quite large) planning
wb. Before anybody mentions I know i am trying to get excel to act as
a db, i originally started design in access and it was rejected by my
company (users not familiar with access). Knowing what i do now i
should have built in access and used excel as the front, however i'm
pot commited now as the build so far has taken a year part time!
Essentially the sheet stores customer details. These are split into 6
geographical areas (1 to 6). Each of the customers are also split
further into 5 more categories (A -E) which represent the type of
customer. The wb was designed for a user to be able to plan when to
visit their customers.
They would plan a category a day, then select customers from that
category which would programatically be put into the calendar (a ws in
excel). So on 1st April they might plan to to be in area 4. They would
select customers from the ones in area 4 and use a button 'plan' which
would run code to copy the customer details into the day on the
calendar worksheet relating to the 4th april.
This process works however it means each day has to be planned
individually, and with 11 customers a day this is a lengthy process.
So I have been asked to automate further some of this process.
I am aiming for;
- Monthly calendar view, possibly populated by code so i do not have
to have a worksheet for each month
- Ability for the wb to automate some of the planning process based on
selections inputted by the user (eg. user inputs date, geographical
area and customer type into a form and the sheet selects 11 customers
that match the selections)
- When planning automatically as above will need to wb. to consider
the date when the customer was last visited, and only plan if was
longer than a certain number of days since the last visit
-Ability when planning as above to not plan the same customer twice.
In one month a user might use the same criteria to plan and i need the
wb to select unique customers each time. Although further to this if a
user deletes a customer already planned from the calendar i need the
workbook to recognise the deletion and consider it for planning next
time
- Ability to plan 6 working days automatically as above and then leave
a day empty ie. only plan 6 working days in every 7 working day
cycle.. It is crucial this is automatic.
So the issues i have whilst trying to understand the best way to do
this;
- Calendar design - i would like the user to be able to view a month
(similar to outlook) and expand each day (as in outlook) so they can
see all entries. (If i wanted entries to all be visible at once the
monthly calendar would be very large). I guess this is a form
triggered by double clicking on the cell and auto populating with data
- i presume this is possible and therefore i simply need to discover
how to populate the form
- Automation part reasonably simple, however cannot figure how to
manage not planning the same customers in twice in a month if the same
criteria is passed to the planner. Thought about putting an entry in a
cell that relates to the customer record, however this will prevent
the customer being planned in again if the user deletes where it is
planned
- No idea how to do the 6 working days!
I think i need to start building a huge amount of code, however would
much appreciate any thoughts or ideas that may make my life (which
feels like it is going to be spent coding for the foreseeable future!)
any easier!