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

x
?
Solved

Prohibit data entry in a cell based on another cell

Posted on 2011-02-28
9
Medium Priority
?
225 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

963 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