Input to a cell

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.
Bright01Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
dubadayConnect With a Mentor Commented:
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
 
StephenJRCommented:
Fairly sure you can only do that with a macro. Can you give an indication of what you want to do?
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
slycoderCommented:
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
 
Bright01Author Commented:
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
 
dubadayCommented:
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
 
Bright01Author Commented:
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
 
Rob HensonFinance AnalystCommented:
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
 
Bright01Author Commented:
Great job Dubaday!  Much thanks with the readjustment.  "Spot on".

B.
0
All Courses

From novice to tech pro — start learning today.