Solved

Prohibit data entry in a cell based on another cell

Posted on 2011-02-28
9
220 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

679 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