Microsoft Office Forums Enter a number in a locked equation within a cell that then calculates

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-10-2014, 08:12 AM
Carchee Carchee is offline Enter a number in a locked equation within a cell that then calculates Windows 7 64bit Enter a number in a locked equation within a cell that then calculates Office 2007
Advanced Beginner
Enter a number in a locked equation within a cell that then calculates
 
Join Date: Dec 2013
Posts: 46
Carchee is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 06-10-2014, 01:06 PM
gebobs gebobs is offline Enter a number in a locked equation within a cell that then calculates Windows 7 64bit Enter a number in a locked equation within a cell that then calculates Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 832
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

Yes. Just be sure to unlock the cells where you are entering the data. The default state of any cell is locked.
Reply With Quote
  #3  
Old 06-10-2014, 02:43 PM
Carchee Carchee is offline Enter a number in a locked equation within a cell that then calculates Windows 7 64bit Enter a number in a locked equation within a cell that then calculates Office 2007
Advanced Beginner
Enter a number in a locked equation within a cell that then calculates
 
Join Date: Dec 2013
Posts: 46
Carchee is on a distinguished road
Default

gebobs,

Right so how do you it? I was looking more for an explanation on how this can be done, not yes or no
Reply With Quote
  #4  
Old 06-10-2014, 03:28 PM
gebobs gebobs is offline Enter a number in a locked equation within a cell that then calculates Windows 7 64bit Enter a number in a locked equation within a cell that then calculates Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 832
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

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.
Reply With Quote
  #5  
Old 06-11-2014, 07:58 AM
Carchee Carchee is offline Enter a number in a locked equation within a cell that then calculates Windows 7 64bit Enter a number in a locked equation within a cell that then calculates Office 2007
Advanced Beginner
Enter a number in a locked equation within a cell that then calculates
 
Join Date: Dec 2013
Posts: 46
Carchee is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 06-11-2014, 08:58 AM
whatsup whatsup is offline Enter a number in a locked equation within a cell that then calculates Windows 7 64bit Enter a number in a locked equation within a cell that then calculates Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

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.
Reply With Quote
  #7  
Old 06-13-2014, 08:23 AM
Carchee Carchee is offline Enter a number in a locked equation within a cell that then calculates Windows 7 64bit Enter a number in a locked equation within a cell that then calculates Office 2007
Advanced Beginner
Enter a number in a locked equation within a cell that then calculates
 
Join Date: Dec 2013
Posts: 46
Carchee is on a distinguished road
Default

That's what I was thinking, it would be great if they added something like that to the conditional formatting. Thanks everyone
Reply With Quote
  #8  
Old 06-13-2014, 11:11 AM
whatsup whatsup is offline Enter a number in a locked equation within a cell that then calculates Windows 7 64bit Enter a number in a locked equation within a cell that then calculates Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

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
Now every time you enter a number in cell A1 the old value will be multiplied by the entered value.
Reply With Quote
Reply

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
Enter a number in a locked equation within a cell that then calculates 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 a number in a locked equation within a cell that then calculates Enter Number on any sheet one time only. paulrm906 Excel 1 04-28-2006 07:35 AM


All times are GMT -7. The time now is 12:42 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft