Solved

Input to a cell

Posted on 2011-09-07
9
332 Views
Last Modified: 2012-05-12
Is there a way to have a formula for a cell that if a condition is or is not met, it allows you to enter data?  Generally speaking, all my formulas are in the cells and so you can't enter data into that specific cell without distroying the formula.  Can I do it without a macro running in the background?  If not, what would the macro look like?

Much thanks,

B.
0
Comment
Question by:Bright01
9 Comments
 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
Fairly sure you can only do that with a macro. Can you give an indication of what you want to do?
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Hello Bright,

a cell should have only one purpose. Either data entry or formula. Any data entry into a cell will overwrite existing contents of that cell.

In good design practice, you would never need to enter data into a formula cell.

What are you trying to achieve?

cheers, teylyn
0
 
LVL 5

Expert Comment

by:slycoder
Comment Utility
Try something like this:


All cells are set to Lock by default (Right+click, Format cells, Protection)

You can lock the worksheet with Review Tab, Changes group, Protect Sheet)

So by using the above, you set the Cells that will be queried to be Unlocked,

then you can have macros run on update to lock/unlock other cells

NOTE - after creating the Auto_Open function, you may have to save and close the worksheet for it to take affect.


A           B
Flag1    1
Flag2    2
Allow    




Sub auto_open()

   ' Run the macro DidCellsChange any time a entry is made in a
   ' cell in Sheet1.
   ThisWorkbook.Worksheets("Sheet1").OnEntry = "DidCellsChange"

End Sub

Sub DidCellsChange()
   ' Unprotect worksheet
   ThisWorkbook.Worksheets("Sheet1").Unprotect
   
   ' Lock B3
   Range("B3").Locked = True
   
    If Range("b1").Value > 0 And Range("b2").Value > 0 Then
        ' Unlock B3
        Range("B3").Locked = False
    End If
   
    ' Protect worksheet
   ThisWorkbook.Worksheets("Sheet1").Protect

End Sub




Thanks
0
 

Author Comment

by:Bright01
Comment Utility
So here's what I'm trying to do.  I have two columns of data with 15 rows.  I want to be able to input TEXT (see drop down box) and have an associated numeric value (C23:C26 and E23:E27), put into Columns F and G. (I have a macro that already clears the values). Then, with a "Alpha/Numeric" button, switch to allow numeric values to be placed into Columns C and D (with limits of 0 to 10).  To get the sum values, the equation in C17, C18 would also have to change.  The reason for the button is that I only want Text OR Numeric, but not a mix.

I hope that's clear.  I started with a question on how to have a formula that I could change to a value.  Based on the comments above, I do believe I will have to have a macro to change the Alpha capability to a numeric capability (and back).

Much thanks,

b.
Alpha-to-Numeric-Input-Capabilit.xlsm
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Expert Comment

by:dubaday
Comment Utility
Attached is your worksheet withsome changes:

Select whether you would like to see Numerical or Alpha values by clicking on the Option buttons at the top. Now enter the Lookup Values into Column C for "Condition/Value 1" or Column E for Condition/Value 2".

You can change the selection in the option boxes at any time, depending on what you would like to see.

FYI, it seemed to me that you were calculating the averages at the bottom of the columns so I changed those formulae as well. Just check whether this works for you.

Hope this helps

Regards
Dawie Alpha-to-Numeric-Input-Capabilit.xlsm
0
 

Author Comment

by:Bright01
Comment Utility
Dubaday,

Thanks so much for the sheet/macro.  Unfortunately, it's not that simple. And I hope you like challenges or puzzles.  I'm not looking for a lookup table that displays the associated value/text. I'm looking for the ability to INPUT either Alpha or Numeric (col. C and E) and have the associated value displayed.  So IF I ENTER "TRUE", the value for true produces a 5. IF I ENTER "PARTIAL", the value for partial produces a 3. etc. etc.   If I hit the Numeric Value button it resets the model and I CANNOT ENTER TEXT;  I CAN ONLY ENTER A VALUE BETWEEN 0 and 10.  That value then must render a numeric value that looks at another table that has 3 ranges (to produce the same number of responses as if I were selecting TEXT);

Example:

0-3 = 1
4-7 = 3
8-10 = 5

So... in summary;  The simple approach is "TEXT" where you put in Text and one of 4 values are displayed; "n/a" = 0 False = 1 Partial = 3 True = 5 (e.g. Condition1) or if I switch and start over, I can put in values 0 - 10 and if I entered a 5 for example, it would render a 3 (see table above).

You must be asking, "Why would he want this?"  I'm dealing with two types of users.  Those who think in terms of something is true, partial or false (gray) and those who think in terms of numeric percision (0-10).  That's why I'm trying to offer both ways of inputing data while producing the same results.

Thank you,

B.
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
You might alos look at Data Validation.

This can restrict user entry to meet specific conditions, eg the numeric cells could be restricted to numbers between 0 & 10.

The text cells would be more difficult to validate unless you are able to create a list of allowable entries, the user would then only be able to enter from the list; alternatively the condition could be on length or something like that.

Thanks
Rob H
0
 
LVL 1

Accepted Solution

by:
dubaday earned 500 total points
Comment Utility
Hi Bright

Please see attached:
Again, select appropriate Option button, and enter data in areas marked yellow. I have included dropdown boxes in these cells that will limit you to either text or values. Alpha-to-Numeric-Input-Capabilit.xlsm
0
 

Author Closing Comment

by:Bright01
Comment Utility
Great job Dubaday!  Much thanks with the readjustment.  "Spot on".

B.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

772 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

11 Experts available now in Live!

Get 1:1 Help Now