The name of the data entry sheet is 'Enter Data Here', and my employees will be filling in information in columns A to U.
I want to be able to accomplish 2 things.
1) When an employee opens the file, he/she is not able to edit any non-blank cells in columns A to U.
2) Employee is able to add data to a new row in columns A to U, and is able to edit this until they press 'SAVE'
I have a button in a template file, that when clicked, saves the file as a new file with file name based on data in certain cells.
I want to disable the ability to save the file with the Save icon or Ctrl-S, but the button still needs to be able to save it (the button is technically doing a Save As, so no problem there). But, I, as the administrator of the template, need to somehow be able to save updates to the template file. Can this be accomplished by coding Save to work when used with a secret key combination that only I know (I can put it in password-protected VB code)?
Then, as if that's not tricky enough, the trickier part is that once the file is saved as the new file (no longer the template) - re-enable the Save button and Ctrl-S so the user can then save it as many times as they want.
I am attempting to create a macro that will create a new folder on the network drive Arie:quotes that will create the new folder and save the the file in the new folder. I need the name of the file and folder to be set to the master (worksheet) cell "B3". and then if possible have it be conditional that if cell "B40" on the same master (worksheet) has a value to have the value added at the end of the filename via hyphen. This workbook has numerous worksheets in it approx 15 worksheets. I am not sure if this is relevant.
As an brief example.
Cell "B3" on the master(worksheet) contains a job name I-40 bridge So initially I would like the Folder name and file name to be I-40 Bridge
However once the Cell "B40" has been filled in with the value 54213 then I would like the file to renamed to I-40 Bridge-54213 and saved within the same folder.
I am currently working with Excel 2007 Pro edition on windows 8. I
I have found several other macro's that work with an auto save but i am unsure of how to change the directory to the network drive and show the correct file paths. This is the example of the macro I am currently working with.
Public Sub SaveAsA1() ThisFile = Range("B3").ValueMkDir "C:NewFolder"ChDir "C:NewFolder"ActiveWorkbook.SaveAs Filename:=ThisFileEnd Sub
I have a workbook of contracts that I have designed in excel. Each worksheet tab/title is the contract number, i.e. 3487. On each individual contract I have the contact name, address etc. Each cell/field is in the same position on every contract. In other words, every B1 is the Group name, every B2 is the first name, every C2 is the last name, etc. What I'm trying to do is to make a new page with all of the contact info on it so I can make labels etc. I hope this is clear.
I am preparing an excel spreadsheet to be used as an order form. There are certain columns that I do not want customers to be able to change, for example unit price, but I would still like them to see it. Is it possible to have only certain fields that can be changed and others that are protected?
I have a spreadsheet with rows of cells which are either blank or have text in them.
Is there an easy way to write a formula which will make the cells which have text in them bold and make the cell colour grey. But if the cell is blank keep the cell white.
I have a big Excel file that is supposed to be used by many users and therefore I need some cells locked. But problem is I use these cells in my vba codes which the users must be able to run by using the buttoms in the file. But now I get error in every macro since the cells the code are trying the reach is locked. Can I make an exception for vba codes in some way so that I can run my macros?
How do i make a drop down list in a cell show Select One in the cell when the cells previous contence (Option selected from drop down list) have been deleted. E.g Cell E5:E400 must show Select One in the cell and when clicked on show drop down list I dont want a input message box or error box i can do that with validation
I want to make an excel workbook for a client. What I want to do with it is make a set of instructions telling them to add or insert as many rows in specific categories I have made for them. These options differ depending on the different duties performed. There will be an associated number (value for that duty, ex. duty might be Janitor and the number would be like 5 dollars per hour, so on and so forth), I want to make it to where there is a code that notices more rows and adds more IF statements or whatever statements are needed to keep adding those extra options. These values are connected to a data validation list that drives the if statement.
I'm looking for a way to make a cell copy the fill color from an adjacent cell.
in my case, cell A1 is a dropdown menu cell, where each option has a different fill color (through conditional formatting). what i want is to make cell B1 copy the fill color from cell A1 automatically. it may be important the both cells have dropdown menus.
I have a chart that shows up to a list of 28 people and the number of sales for that day. I'm try to make the chart only show the names and number of salesperson that are not = to null. This is what the chart has for values right now "=' Nest Average'!$C$6:$C$33". How can I make it so that it only shows those cells if not = to null.
how to record a value of a cell whenever F9 is pressed. The cell I want to record the value of is cell D101, which is a sum function of the previous 100 rows. I'd like to figure out a way to run 1,000 trials or so and record the value in cell D101 each time.
Some more details, if needed: I'm simulating a dice game (craps). There are 2 dice, each dice is based off of RANDBETWEEN (1,6) then the dice are added in a different column to achieve the roll. The payout is then based on whatever number was rolled. I'm summing the payout (D101) and would like to run this trial 1,000 times.
I have a formulas in Sheet1 linked to other sheets, but I have only formula and there are no sheets ( eg: in sheet 1 A1 =Data!A1) where as there is no sheet called Data. I have a macro to import this sheets from a different file.
The problem I am facing is, after importing the sheets, the formula still shows #REF but if I do double click the cell or press F2 and then "Enter" the value appears. I have about 1200 formulas in this sheets.
I recently upgraded to Excel 2007. I use Excel with "Allow editing directly in cells" unchecked. In previous versions, pressing F2 would not prevent me from using the mouse scroll, but in Excel 2007 I am unable to mouse scroll which makes it bothersome. Is there a setting somewhere that can enable mouse scroll after pressing F2?
I have created a qualification tracking spreadsheet to track when qualifications are coming due. Each cell may have a different date based on when that employee initially got that qualification. I want the cell that the initial qualification date is typed in to turn "red" after 180days, "orange" after 120 days and "yellow" after 90 days. I have a formula that does close to what I need, but it changes the cell next to it in color.
That code is as follows: =AND(A1<>"",Today()>=A1+90,B1="")
I have attached the sheet, what I am trying to do. Basically columns B-E and rows 2-35 are the ones that need the formatting.
I have set up a formula to sum data from specific columns in multiple sheets in the same file. The simple formula had worked perfectly until I needed the data from the last sheet. In all of the other sheets the data that I need to pull is all in consecutive rows but in the last sheet the data that I need pulled and added is in every 4th row. The sheet looks like this:
All of the columns I've filled in work fine. The ones blank are the ones I am running into issues with. When I drag down the formulas excel pulls the next consecutive rows, which is perfect for the first four sheets I am summing, but for the fifth sheet (I'll ref the Test1 column) I need it to pull 'Wayne 2014'!C6 and then 'Wayne 2014'!C10 in the next row and so forth.
Date All Test Test1 Test2 Test3 Test4 $ Amt.
Jan.16 =SUM(C2+D2+E2+F2)
[Code] ........
The first four sheets look like this, which is why they work fine:
Date All Test Test1 Test2 Test3 $ Amt.
Jan. 16 =SUM(C2:E2)
[Code] ........
The fifth sheet looks like this: I only want the formulas from the first sheet to pull the data from the dated rows in this sheet.
Date All Test Test1 Test2 Test3 $ Amt.
Jan. 16 =SUM(C2:E2) =SUM(C3:C5)
[Code] ......
Is there any way in Excel 2010 to get it do what I want? (I am not sure how that one cell became outlined and I do not know how to fix it.)
Is it possible to launch Excel at a specified time without someone pressing a button?
I'm working on a network which has disabled Scheduled Tasks. The purpose of this request is to launch Excel in the early hours of the day so that a macro would run and update the data from a data source. The macro works brilliantly, but I without anyone in the office at 2am to open Excel, I need to find a way to launch it automatically.
Also, I don't have permissions to install programs or add-ins from the Internet. It doesn't leave me with many options, but I'm just wondering if there's a trick that has so far eluded me.
I have set the default in Excel to move across to the next cell after I press Enter. This is fine for most workbooks but I have a particular workbook in which it would be preferable (save keystrokes) for the cursor to move down one cell. Is it possible to have one action as the default but another for a particular workbook?
I would like to design a button (with macros),so that users of my excel page do not need to press CONTROL+SHIFT+F1+ALT,which needs to be pressed on my firm to get the new data from a central server.
the title should read... pressing cancel in inputbox, continues the macro... In theory, pressing the cancel button should stop the macros, but it continues as if pressing ok button...
I have the following code to add dash mark to TextBox1 value depending on some condtions (mentioned in the code), the code works perfectly, but when we use Backspace Key to clear some charactors it is conflicting with the code running when changing Textbox1 value.
But it will not calculate. I have gone to Tools - Options - Calculation to see if calculation is set to Manual but it is Automatic. Is there another setting or have I exceeded some limit?
I know I can use Filter and filter out characters like &'!/; but that takes too long because I have a lot of lists to go through with thousands of entries. There must be a macro that will close spaces between words when only one space exists, but eliminate every word if 2 or more spaces exist between words - as well as eliminate unwanted characters like the ones I described above. I know I could use substitute and eliminate the spaces between words =substitute(a1," ","") but then I'd have to filter each and every instance of unwanted character.
I have a file with data in one sheet (unfortunately I cannot share the file because of confidential data..), and two sheets with both 9-11 charts in them.
The charts are all filled simply with a dynamic range from the datasheet, so that Last Year and all months from this year with data in them are always in there.
The problem: When opening either of the graph sheets, the (familiar?) error pops up: "A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a valid path, workbook, range name and cell reference."
One of the graphs has an error and only shows one point of data in it. But only until I press F9, and then the chart is repaired and functions perfectly like all the others!
I have tried lots of things, including checking all source ranges, deleting all graph names etc. and then repaired it so it would work again. The only thing that happened, is that the same error now pops up for another graph in the sheet. Still, when you press F9, the graph functions again!
Can I make the cursor use only 2 columns? In other words, I know that when I scan barcode data into A1, I have the cursor automatically move to B1. BUT, once I've scanned data into B1, how do I make the cursor automatically move to A2?
