Solved

Prohibit data entry in a cell based on another cell

Posted on 2011-02-28
9
218 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

813 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

12 Experts available now in Live!

Get 1:1 Help Now