Solved

Input to a cell

Posted on 2011-09-07
9
334 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
ID: 36499325
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:Ingeborg Hawighorst
ID: 36499336
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
ID: 36499477
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:Bright01
ID: 36499626
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
 
LVL 1

Expert Comment

by:dubaday
ID: 36501086
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
ID: 36501817
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 32

Expert Comment

by:Rob Henson
ID: 36502223
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
ID: 36502828
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
ID: 36502956
Great job Dubaday!  Much thanks with the readjustment.  "Spot on".

B.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Best Excel  formula for  this scenario 2 38
Excel - Data Validation 3 28
Data Copy 4 24
ProperCase in Excel (Sheet) 3 12
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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 will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

777 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