Budget Worksheet - Lock Cells With Formulas
Jul 24, 2014
So I am trying to create a budget worksheet for past and future expenses, basically an excel checkbook with running balance
In Column A I have my debits/credits
In Column B I have my running balance so that there is a current total next to each of the debit/credits in column A, it is formulated as follows, it adds the cells directly above it to the cell directly to the left of it (i.e. Cell B10 = B9+A10, which was dragged and iterated to many cells below)
I wan to be able to drag in drop cells in the A column (Just moving them up and down different cells in the A column) with out it changing formulas in the B column.
I tried the $ trick so that for example B10 = B9+$A$10 but if I moved cell A9 to say A10, I get a reference error in cell B10 (why is it doing this when B10 is locked with $)
I then tried going to format and locking the B column and not the A column and then protected the document but I still get the same REF error.
I know I can copy and paste with out effecting it but this is tedious, the whole reason I did this was so that I can move around bills EASILY and play around with what get paid when. There has to be a way to do this, but I'm probably searching with the wrong keywords.
View 1 Replies
ADVERTISEMENT
Feb 7, 2014
I need to reference the projected and actual expenses from the total on worksheet 'expenses' So i did so, however, if I change the drop down on the expenses worksheet to only display housing data, then the projected and actual expenses on my budget worksheet changes as well to the new data portrayed on the expenses worksheet.
I need to reference the cell, without it changing when I change the category display, but I need the cell to change accordingly if I enter new data in the overall tablet on the expenses worksheet.
Or do I need to create a seperate worksheet that has the data in and reference my cells on my budget worksheet to that new worksheet?
View 1 Replies
View Related
Mar 16, 2014
In looking for examples of business budgets I found a sample spreadsheet that used these two conditional statements:
A) to calculate the difference between the Budget and Actual numbers: =IF(OR(B10,A1), B1-A1,) where B1 is Actual $ and A1 is the budget $.
B) to calculate the percentage (+ or -) =IF(A1,C1/A1,) where A1 is, again the budget, and C1 is the result of the difference calculated in A) above.
What is the advantage of these conditional formulas over the more straight forward B1-A1 and C1/A1
View 1 Replies
View Related
May 21, 2006
In the attached spreadsheet i have a budget amount, billed to date, %complete, %remaining and forecast figure. What i am trying to do is estimate the forecast spend vs the budget or billed to date and percent remaining. I am struggling with how to do this based on in some cases the budget is already overspent but the %complete is less than 100%. What i really want to do is create a forecast based on the billed to date or budget depending on which is greater and work out estimated spend based on whether the task is complete or there is still a % remaining.
View 2 Replies
View Related
Dec 17, 2013
I am trying to create a spreadsheet that will keep track of budget spent and budget remaining.
I need the values to take what is spent from expenses claim forms that are filled out,
Each budget category has a code and within that code there are sub heddings
View 2 Replies
View Related
Jul 23, 2014
How to Lock Individual Cells in a Worksheet excel 2007 .... i.e.
A2:A8
I want this selected area locked with password.
View 1 Replies
View Related
Mar 19, 2013
I have a Master Worksheet that I am using to populate other worksheets using the Table function. I would like to keep the format the same as the Master worksheet even if lines get added or deleted. I have seen that others group the sheets together when making the changes but I can not really us that options. I will be giving the Budget to someone else to fill out and they will not know how to do that.
View 1 Replies
View Related
Jul 2, 2009
do you know if there is vba code to lock formulas so someone sees but does not touch and the same for values see but cant change??
my sheets with formulas are SUMMARY and Sheet2
sheet with value is TABLE
View 9 Replies
View Related
Jun 28, 2008
How do you lock a column of formulas so you can't change the format by cut and paste? Also, the same for data, locking data so it cannot be overwritten in Excel 2003 and 2007.
View 4 Replies
View Related
Nov 30, 2009
In the attached example, I need to create a formula for cell I2 (Balance Remaining) that is driven off the most recent calculation (in this case, F68) but still allows for additional rows to be added to the ongoing list of orders. I believe I'm supposed to use an index formula but not sure how to write/apply it? I need to be able to add rows past 68 and still have Cell I2 (Balance Remaining) continue to calculate correctly.
View 10 Replies
View Related
Jun 6, 2012
I have a SS that puls data from another sheet using the following formula
=IF(OR('Site Visit ASSESSMENT'!D9={"PARTIAL COMPLIANCE","NOT COMPLIANT","NOT ASSESSED","ADDITIONAL INFO NEEDED"}),'Site Visit ASSESSMENT'!A9,"")
I also have VB Code in teh sheet as follows to hide rows. If I protect colum A and protect sheet I get an error. How can I protect the sheet and still have the following commnad work?
Private Sub CommandButton1_Click()
'Toggle rows hidden where cells in column D are blank
If ActiveSheet.AutoFilterMode Then
[Code].....
View 6 Replies
View Related
May 21, 2014
Fabrication and Site Visit in Budget/Spend Analysis - Budget, not pulling through information from drop down boxes - in both cost type columns (they dont seem to be active any more either, not sure what I have done there!
View 1 Replies
View Related
Apr 21, 2012
I have a workbook that contains 50 worksheets named 1-50. I need to add more worksheets. all the formulas in the worksheets always refers to the previous worksheet.
How can i make a copy of the worksheet named 50, name it 51 and have all the formulas in worksheet 51 refer back to worksheet 50?
View 1 Replies
View Related
May 28, 2009
I have code written to auto save the active worksheet when a button on my user form is pressed. What I would like to know is if there is a way to lock the worksheet when the button that triggers the save event is pressed behind the scenes so the end user has a more difficult time editing the sheet. I know they can just resave the document and the protection will be gone, but for minimizing user manipulation.
View 2 Replies
View Related
Oct 3, 2008
I need to lock a worksheet tab (Main menu) in place so that it is always visible. There are multiple tabs that are all linked to the main menu by hyperlink. When work is finished in a worksheet I want the main menu tab to be visible and usable rather than using the scroll functions to get back to the main menu.
View 9 Replies
View Related
Aug 3, 2006
I have an excel workbook we use for quoting jobs. All our pricing references are in another workbook on our network. I have reference in the 'quoting' workbook that refer to the 'pricing' workbook.
The trouble is, when some users open the 'quoting' workbook, and have another Excel file open, some references to the 'pricing' workbook change to the other file they have open.
I suppose there is some sort of replicable condition that is happening, but I can't figure out what is going on or why.
So my question is: Can I ' lock' an in-cell reference to an external workbook so that it NEVER changes?
I tried hand entering the reference (network location+workbookname+sheetname) into a cell and then referencing that cell, but I couldn't get that to work.
View 9 Replies
View Related
Sep 13, 2009
It is possible to lock the Author of the Worksheet in File --> Properties --> Summary Tab..?
I am using Excel 2003 & 2007..
View 8 Replies
View Related
Feb 1, 2013
I have a worksheet that is protected with data entry cells unlocked.
1. Unprotect worksheet
2. Lock all cells in the worksheet and then protect.
3. After a date in a cell in the worksheet.
I'm a newbie to vba and at least know vba is the only way. And this need to be automated.
View 1 Replies
View Related
Oct 16, 2009
I have a worksheet that, unless a form for data entry is activated, should be read-only for columns 'A' thru 'M' and 'O' with column 'N' left editable.
The form is activated by a button with a macro assigned to enter the data in the above columns when the form is closed. This has been compiled and works nicely with the sheet unlocked, however, as i need certain columns to be read-only unless the form is activated, i'm stuck with leaving the whole sheet unlocked for editing with or without the form being activated.
Is there any way a VBA code could be used to unlock the worksheet columns that are read-only when the form is activated, and then lock them once the form data is entered to the worksheet and the form closed?
View 9 Replies
View Related
Aug 18, 2006
Is it possible by using Visual Basic coding to position an image on a worksheet so that no matter what the screensize is it will always appear in the center of the screen, a corner or a side?
View 9 Replies
View Related
Apr 8, 2008
i have a worksheet which is a form which feeds into another worksheet which acts as a database. i have a button which clears the form based on code which is essentially "clear contents".
the problem is i have a few cells where i would like to retain the formulas. i tried to do this with custom in validation but this did not work. i also tried to enter the code directly into the worksheet but this didnt work either (my skills are limited..). i am avoiding using protect sheet bc that in turn will affect many of my other buttons. is there something i am overlooking?
View 9 Replies
View Related
Mar 29, 2014
am making Excel for private use, i need the following makro to be active, here is the idea because i didnt yet work with makros at all:
If A1 = False
then Range B1:D1 will be locked cells
If A1 = True
then Range B1:D1 will be unlocked cells
Those rules apply to 1 day in the year.
the range A1:D1 will be copied about 400 so the makro should be active to each day separatly.
Check the picture attached for example of one day
View 1 Replies
View Related
Apr 22, 2009
I'm using the code below to lock certain cells depending on the value of other cells. The code below deals with one line of my spreadsheet only and as the spreadsheet comprises 38 data entry rows I've repeated this code 38 times in the worksheet module with the appropriate changes to row numbers.
It works, but causes much screen flickering and "thinking". I'm new to vba so no doubt I've made this code too extensive or lengthy or whatever (or just plain wrong).
Can anyone assist with suggestions on how to simplify the code and/or help with code to handle all 38 lines without repeating the routine 38 times?
View 7 Replies
View Related
Apr 18, 2008
I am trying to lock the unused cells in 32, 2 column by 7 row named ranges, based on whether or not two cells, above each range are equal or less than each other. In other words while one of the cells is less than or equal to the second cell all cells in the range below should be unlocked, as soon as that condition is no longer true the blank cells need to be locked.
I am trying to use this in the Workbook_Sheetcalculate so that the macro will run automatically.
View 3 Replies
View Related
Jan 10, 2013
I am running Excel 2007 on Windows Vista Business 32 bit. Recently I have noticed that if I enter a formula into an empty, unsused cell, it is recognized as a formula. If I modify that formula, it is then recognized as text and does not work as a formula. The only way I can get the cell to recognize a formula is to delete the cell and start over. This same scenario does not occur on previously stored workbooks. I have checked all of the flags that I know about, including the Options function.
View 3 Replies
View Related
Nov 7, 2006
I am aware of setting protection to certain cells by the Excel Options. which is unlocking the by defalut locked cells and then setting the sheet protect in tools->protection. I want this to be done through VBA code.
i have a template which has text in some cells and some cells are blank, i need to lock the cells which has text and keep the blank cells unlocked for user input. As this is a template it will be copied to another sheet, so i need to retain the protection properties in all new sheets (copied from the template). Attaching the template for which the protection needs to be done.As of now the sheet1 has protection property set through the toolbar properties.
View 2 Replies
View Related
Feb 8, 2014
I use the attached workbook to keep tack of feed use in turkeys. when a new flock is added to a farm (Airport View or Home farm) A template is copied and pasted from the "templates" sheet depending on what type of diet they will be on. (Ralco ABF or Ralco Standard) that template refers to the Ration sheet to calculate how much corn is in each diet. After a flock is complete I will never change the numbers again, it is just for record keeping. I want to be able to change the Corn/ton numbers in a ration (on the rations tab) if need be and not affect all the older completed records.
View 1 Replies
View Related
Oct 22, 2008
Is there a way to protect a sheet from a user dragging a cell?
View 6 Replies
View Related
Aug 1, 2014
I have used the following code to lock cells when saving.
I need to exclude Cell B2 when saving
let me know what changes i should make.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
if ActiveSheet.Name = "Summary" Then
On Error Resume Next
'Resume to next line if any error occurs
Dim Cell As Range
With ActiveSheet
'first of all unprotect the entire
'sheet and unlock all cells
.Unprotect Password:="pw"
[code]....
View 2 Replies
View Related
May 1, 2009
W3:W300 is a protected range containing formulas returning numbers from one upwards, with two decimal places.
I need some vba to check this range and, if a number greater than 1 is found, to make the corresponding cells in Columns X and Y blank and lock them.
View 9 Replies
View Related
