Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

I need help with an Excel template

Posted on 2014-11-30
5
Medium Priority
?
199 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 (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 1000 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 34

Accepted Solution

by:
Rob Henson earned 1000 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 34

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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

972 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