Solved

Prohibit data entry in a cell based on another cell

Posted on 2011-02-28
9
219 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Formatting code for Excel Forms 17 48
Excel if statement 3 15
Random times with 12/24 hour switching 9 27
combine fist two words in a cell 2 23
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

809 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