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
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 RelatedIn 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 RelatedSo 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.
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].....
I am trying to make a button that will re-initialize the workbook: clear (delete) all unlocked cell's values. I have the following
View 5 Replies View RelatedI have an excel file with 9 sheets and I want to copy all the data from those sheets to a master sheet but with out the formulas . I need the values only to appear in the master sheet. I used the following vba macro code which I found it while I was searching for an answer, it did it perfectly except for the formula part. !! I guess, it has to be edited by adding some codes with paste options but I don't know how!
Code:
' CollectMasterData Macro
'
Sub CopyToMaster()
Dim wkSht As Worksheet
Dim DestSht As Worksheet
Dim DestRow As Long
Set DestSht = Sheets("MasterData")
[code].....
Note: my headers are @ row 1 and 2 and my formula is in column A.
I am looking to write a code to lock/unlock cells based on values selected using a drop down list (see attached Excel) For example if Netherlands is chosen as the Country (Column A), then except for the columns Amsterdam (column B) and Eindhoven (column C), all other columns must be locked. Similarly, if India is chosen as the country from the drop down list, then only the columns New Delhi and Mumbai must be editable for the user, the rest of the columns remain locked.
I tried tweaking some of the lock/unlock codes around, but got lost especially with getting to run the macro - still trying to come to terms with running a "Private Sub Worksheet_SelectionChange" function in the sheet from "Module"!
I have a workbook that updates from external source and creates sheets depending on a cell range.
I have put tab 1 and tab 0 on either end of where the new sheets will be inputted, will never know how many sheets
What i need to happen is if someone fills in "complete" in A7 in my "summary" sheet then the values in row 6 in all the other sheets get hardcoded. This needs to happen from A7 down to A26, so A8 = complete then copy row 7 etc
This is what i have so far
I get compile error here ........Sheets(ArrSh(1)).Activate
Also need it to work for all the other rows.
Sub hardcode()
'
'Sheets("Summary"). Select
If Range("a7") = "complete" Then
'
Sheets(Array("1", "0")).Select
Sheets(ArrSh(1)).Activate
[Code] ......
I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.
View 9 Replies View RelatedI have a workbook with 4 sheets.
1st sheet is called "FORM"
2nd is called "BIDS"
3rd is called "RESULTS"
4th is called "BIDDERS"
Some of the data on the FORM moves to the BID and RESULT sheet. The cells on the BID sheet are moved to the RESULT sheet with a cell formulated to save the highest bid. On the BIDDERS sheet I have a list of Bidders and I use the Row numbers to match up with the column letters to place their bid in the cell. I need the formula on the Results page to give me the name of the highest bidder. Here is a sample of the workbook so far. you will see some data placed there for testing!
I have a spreadsheet that requires the user to use ALL CAPS when they fill it out. Is there a way to have VBA turn on the CAP Lock regardless of the persons keyboard setting? I have asked that the users use ALL CAPS but of course that leave a lot of room for error.
If I have a formula on Sheet2 which creates conclusions depending on data already existing on Sheet1 and then I need to paste the same formula on Sheet4 in order to get conclusions from Sheet3, then what can I do? Every time I am trying to paste the formula, it connects it with Sheet1 instead of Sheet3
In other words
Sheet1: Data
Sheet2: Extracted conclusions from Sheet1 data based on a formula
Sheet3: Data similarly organized as in Sheet1
Sheet4: How do I extract the same conclusions, but this time from Sheet3?
The formula on Sheet2 is:
=INDEX(Sheet1!$E$312:$E$5000;((COLUMNS($A4:B4)-1)*20)+1)
I would like to copy this formula from Sheet2, then paste it to Sheet4 and then to have it appearing as
=INDEX(Sheet3!$E$312:$E$5000;((COLUMNS($A4:B4)-1)*20)+1)
Is this possible?
Do array formulas work if the array is across sheets instead of across columns or rows? I'm getting a #Ref! error when I try to use an "across sheets" array.
I'm doing this:
=sum(if('Sheet 1:Sheet 2'!A1=1,'Sheet 1:Sheet 2'!A2,0))
With a CTRL + SHIFT + ENTER return.
I'm looking at creating some templates that people will submit to me on a monthly basis but i'd like to 'lock' the workbook name, is this possible?
View 2 Replies View Related=IF(A4='Exported Data'!B2,'Exported Data'!C2,"")
How can I lock A so when I copy over to the right the formula doesn't change that reference to B4?
Is there a way to have the True value (Exported Data'!C2) reference one cell over instead of selecting the cell to reference from?
Is there a way to lock the title of the workbook so if someone else has it, they can't rename it even if they move it to their personal computer?
View 2 Replies View RelatedHaving a problem locking the sheet and still have it work. Not sure if their is a easy work around for this. Just lock sheet and see what I get.
View 7 Replies View RelatedWould like to set CAPS-LOCK On and Off using VBA. Can someone help please?
View 5 Replies View RelatedI have a excel document with 5 tabs (sheets) and, because different people work with a different tab (sheet), I want to lock each tab (sheet). I want the tab to be visible on the tab bar but when you click on it you will not be able to see the content of that tab (sheet) until you provide the right password (each tab with a different pasword).
View 2 Replies View RelatedIs there a way in excel to prevent someone from "re-ordering" tabs in a workbook without using a macro?
I am using a sum formula that goes across multiple tabs, but noted when I rearranged the order of the tabs, that the results of the formula changed. After some minor research, I noted that I had moved a tab outside the "sum range" inadvertently.
How do you programatically enable the CAPSLOCK key in VBA? I've tried:
Sendkeys "{CAPSLOCK}"
When I run this, Excel's status bar flashes "CAPS" for about 1 second then disappears. It seems to have no impact on the keyboard for case-sensitivity - which is what I'm shooting for.
Need code that would make cell J2 be populated with U no matter what was placed in cell J2 either manually or externally.
View 9 Replies View Relatedhow to lock a sheet in Excel 2007 so that no one can navigate to that sheet.
I can have amacro which will lock each cell where a "Y" or "N" is inserted........ eg the netire sheet is unprotected but the moment Y is inserted in C 5 the cell gets locked, next entry in D 10 has a N entry and that too upon entry get's locked
The idea being once Y or N is typed that cell can no longer be changed
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.
How it is possible to lock the VBA coding and Modules? I mean when Some one want to view VBA coding the Password window should not appear, istead of that a message box appears with message something like "Not viewable"
View 9 Replies View RelatedWhat I want is one sheet that has all of the fomulas, then 4 other sheets that take those formulas and calculate the output based on the formulas and one column of input.
So essentially, I want to be able to change the formula once, and have it applied to all of the other sheets, resulting in a new output for each one.
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 RelatedI want lock a picture in excel like logo, if i copy that logo and past in another sheet the position should be same as parent sheet.
View 1 Replies View Related