Solved

I need help with an Excel template

Posted on 2014-11-30
5
164 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:Ingeborg Hawighorst
Ingeborg Hawighorst 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 32

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 32

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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