Solved

I need help with an Excel template

Posted on 2014-11-30
5
150 Views
Last Modified: 2014-12-14
Hi Experts,
I have an Excel Spreadsheet that I want to use as a template for employees to fill in their work availability for any given month.

In cell B9, I have a drop down control with all the months as values (see image 1, below).
In column A, starting at cell A11, I have values 1-31 for the days of the month
In Column B, starting at cell B11, I have a combo box where employees select from the following values (Available, or Not-available)

Image 1

If an employee is submitting his availability for a month with only 30 days, I want the cell in column A with the value 31 and the combo box next to it to become disabled (see image 2, below).  
image 2I have used VBA before just not in Excel.  How do I do this in Excel 2007?  Do I need to use VBA to accomplish this?

Thank you very much in advance,
mrotor
0
Comment
Question by:mainrotor
  • 2
  • 2
5 Comments
 
LVL 50

Assisted Solution

by:teylyn
teylyn earned 250 total points
ID: 40473115
Hello,

I'm wondering why you would use VBA controls (combo boxes) for this. They are a pain to place (individually) and a pain to maintain. You'd have to assign a sheet cell to capture the value of each combo box. Why the hassle?

Instead of combo boxes you could use Excel's spreadsheet tool for data entry validation: Data validation with a list.

Here is a sample file. Invalid days are greyed out. Selections are made with data validation.

Note that users can still use the availability drop-down in the gray cells, but with the color coding of the user interface they are less inclined to do so.  Who would log availability against a non-defined date? (I know, I know..... users will do anything to upset our carefully constructed solutions).

cheers, teylyn
sample.xlsx
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 250 total points
ID: 40473504
Alternative option with no helper columns/sheets.

In A11 use following formula:

=DATEVALUE(1&" "&B9&" "&YEAR(TODAY()))

In A12 use following formula:

=IF(A11="","",IF(MONTH(A11+1)>MONTH(A11),"",A11+1))

Copy down to A41

Format A11 to A41 as "dd" or "d"  depending on whether you want single digit values to appear as two digit, eg 09.

Also slightly simpler Conditional Formatting Rule:

Formula:   =$A11=""

Applies to $A11:$B41

You could get round the input into invalid cells by using a Custom Data Validation that checks whether column A contains a Date but you lose the drop down arrow functionality. You can get round it by making sure the "Available" and "Not Available" are in cells above the input area so using Alt & Down arrow gives a Pick From list.

New version attached.

Thanks
Rob H
sample.xlsx
0
 

Author Comment

by:mainrotor
ID: 40474611
Teylyn and Rob thanks for your replies.

I will need to import the information from the Excel spreadsheets into an Access database.  There shouldn't be a problem with me doing so using either of your solutions, correct?

Thank you both, I will try your solutions later today.

mrotor
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40475726
The only issue I would foresee is Access normally expects tables to start with headers on row 1. The way round would be to give the data area a range name. The import can then look at the specific area. The range name can be dynamic to ignore the rows with no date.

Thanks
Rob H
0
 

Author Comment

by:mainrotor
ID: 40485556
So, I tried both of your solutions.  I like them both.  I will try to incorporate both of your solutions in my project.  I will keep you posted on the outcome.

thank you,
mrotor.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now