Solved

Prohibit data entry in a cell based on another cell

Posted on 2011-02-28
9
216 Views
Last Modified: 2012-05-11
I have attached an Excel 2007 spreasheet that I wish to only allow data to be entered based on whether a the value 'x' is in another cell.
The sheet 'tempalate requirements' has an 'X' in those cells that require data from that user (columns B-K). I wish to hopefully accomplish two things. Require data be entered in Sheet 'Data' based on the User and the value X in Sheet "tempate requierments'...and disallow the converse.
dashboard-wip.xlsm
0
Comment
Question by:singleton2787
  • 6
  • 3
9 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35000865
See attached.

Kevin
dashboard-wip.xlsm
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35000893
To implement the above solution, I named the key ranges in the template tab, created a second validation list with just "NA" in it, and used this validation formula:

   =IF(INDEX(TemplateData,MATCH($A7,TemplateStatus,0),MATCH(B$1,TemplateCategory,0))="x",Stoplight,NARange)

I also have to copy the headers from the template to the data sheet because they were not all the same.

Kevin
0
 

Author Comment

by:singleton2787
ID: 35001150
I downloaded the file, enabled the macros (if any) ...but it still allows me to input data in cells that should not be...like Data!B9
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35001180
I thought you wanted to limit entry in cells B7 through J7.

What does cell B9 have to do with the problem at hand?

Please clarify the question.

Kevin
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35001195
If you want to completely lock certain cells from any input then you can use cell locking.

A worksheet can be protected in such a way that only cells that need to be changed can be changed. Protecting a worksheet involves two different settings or attributes: worksheet protection and cell locking. When a worksheet is protected, all cells that are locked become uneditable and those cells that are not locked remain editable. To lock or unlock a cell, select the cell or cells and choose the menu command Format->Cells (in Excel 2007 press ALT, O, E), navigate to the Protection tab, and check the Locked check box on to lock the cell or cells or off to unlock. Note that on all new worksheets all cells default to being locked.

To protect a worksheet, choose the menu command Tools->Protection->Protect Sheet (in Excel 2007 press ALT, T, P, P). Click OK to lock the worksheet without a password. To protect the worksheet with a password in order to prevent others from unprotecting it, enter a password. Other settings on the Protect Sheet dialog provide the ability to prevent or allow other functions such as selecting locked cells, sorting, filtering, deleting and inserting rows and columns, formatting cells, etc. Note that most of these settings are only available in Excel 2002 and later.

To prevent the user from viewing formulas while the worksheet is protected, select the cells for which the formulas need to be hidden, choose the menu command Format->Cells (in Excel 2007 press ALT, O, E), navigate to the Protection tab, and check on the Locked and Hidden check boxes.

Kevin
0
 

Author Comment

by:singleton2787
ID: 35001567
These cells I wanted to limit data entry are:
=Data!B3:J6
=Data!B8:J10

If there is an 'X' in Template Requirements'!B2:J10, then require data entry,
if there is NOT an 'X' in ='Template Requirements'!B2:J10, then data entry in
=Data!B3:J6
=Data!B8:J10
should NOT be allowed.  BTW, the tips you listed above were very helpful, thanks!
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35001909
Done.

I copied the validation rule from row 6 to the other entry rows, unlocked the data entry cells, and then locked the worksheet.

Kevin
dashboard-wip.xlsm
0
 

Author Comment

by:singleton2787
ID: 35008939
Still no dice...it allows entry into cell that do not have an X in the Template sheet. I will try to rephrase the scope and objective.

In the Template Requirements sheet there are Columns with staff listed. Int the Rows there are data entry types. Some have Xs in the cells.
In the Data sheet, I want to:
SIMPLE SOLUTION? Just pre-popuate the X in the corresponding cell from Template Requirements to Data sheet? I would imagine that I would have to clear the Data sheet to allow the user to see which cells are to be filled in?
Optimal Solution: Disallow/Prevent data entry into the Data sheet in those corresponding cells which DO NOT have a 'X' in the Template Requirements sheet.

dashboard-wip.xlsm
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 35010158
That is essentially what I provided. In the cells in which no input is required/desired the only choice is "NA". In the cells in which input is required/desired the user can choose from the three levels of completeness.

Kevin
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
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.

757 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

20 Experts available now in Live!

Get 1:1 Help Now