#1
|
|||
|
|||
Enter a number in a locked equation within a cell that then calculates
Does anyone know if you can add an equation to a cell and somehow lock it, then be able to enter a number within the same cell where the equation where it is then calculated? To explain the process a bit further.... you would enter a formula in a cell and lock it, then within the same cell you could enter any number and the result would vary depending on what number you entered. I hope that makes sense. It's kind of like conditional formatting, it would be nice if Microsoft would add this to their conditional formatting.
I would like to do this without vba. |
#2
|
|||
|
|||
Yes. Just be sure to unlock the cells where you are entering the data. The default state of any cell is locked.
|
#3
|
|||
|
|||
gebobs,
Right so how do you it? I was looking more for an explanation on how this can be done, not yes or no |
#4
|
|||
|
|||
To unlock a cell, you can go to the Format Cells dialog box (Home:Cells:Format Cells) and under the Protection tab, you can lock and unlock cells.
Personally, I do a lot of locking and unlocking, so I have a Lock Cell toggle button on my Quick Access Toolbar. To do that, * Right click on the toolbar and select "Customize Quick Access Toolbar". * Under "Choose Commands from", select "Home Tab". * Scroll down to "Lock Cell", and double click it. Voila! Now you have to lock the sheet. You do this with Review:Protect Sheet. There are lots of options for what can be done with a protected sheet, but you can just hit OK until you figure out what's best for you. I usually do not password protect. You can unlock the spreadsheet the same way. Again, I do a lot of this so I have a Lock Sheet toggle on my QAT. Same general procedure except the command is under the Review tab. |
#5
|
|||
|
|||
Okay all that makes sense but still not quite what I'm looking for. I guess it's harder to explain than I thought. I'll give it another shot.
What I want, and I don't think is possible, is to be able to put an equation in cell A1. I would want this equation to look like the following: =5*A1 (just an example equation). Then lock the cell or equation to that cell somehow so it can't be deleted. Next in the same cell, A1, you would input any number, lets say 4 so the answer would then say 20 in cell A1. When you input this equation into cell A1 you get a "Circular Reference Warning" which makes sense. But it would be nice if I could put an equation in the cell and lock it and be able to input a number within the same cell and have it formulate without the use of other cells. Now wouldn't that rock! Hopefully you get the vision now. Thanks for you suggestions gebobs. |
#6
|
|||
|
|||
Hi
Generally in enabling Iteration you can overide Circular Reference Warning. But I don't recommend using iteration. - In your case it won't work anyway. The only way you can do it your way is using vba. |
#7
|
|||
|
|||
That's what I was thinking, it would be great if they added something like that to the conditional formatting. Thanks everyone
|
#8
|
|||
|
|||
I'm not too sure about this that you would find it "great". That's because conditional formats aren't what they appear. You wouldn't be able to do without vba - and even then it's quite difficult - any calculations with the value becuse its only a format. It's just some kind of fooling user's eyes, or in other terms conditional formats are another level you aren't supposed to access.
Just in case you will give it a shot with vba, open a new file and write a number <> 0 into cell A1. Copy the code to the modul of the sheet: Code:
Private strselected As String Private dblOldValue As Double Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const strRange As String = "A1" If Not Intersect(Target, Range(strRange)) Is Nothing And Target.Count = 1 Then dblOldValue = Target.Value strselected = Target.Address End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = strselected Then strselected = "" Target.Value = Target.Value * dblOldValue End If End Sub |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Graphic equation in a cell | edneco | Excel | 1 | 06-08-2014 07:43 AM |
VBA code to read number of footnote and enter in text | rekent | Word VBA | 2 | 05-13-2014 06:53 AM |
Enter text into next available cell in a table | molesy | Word VBA | 2 | 09-11-2013 02:25 AM |
how to enter what i want in cell | tasuooooo | Excel | 4 | 07-23-2012 05:45 AM |
Enter Number on any sheet one time only. | paulrm906 | Excel | 1 | 04-28-2006 07:35 AM |