The full formula is quite long as this pick up the data off over 300 work sheets. (there's probably an easier way to do this aswell) When I copy or drag this to another cell with on the same worksheet it gives me the correct formular with the correct cells changes as I want. However this formular does not work. I have auto calculate on and I manually press enter on the cells and still nothing. If I change the following for the entire formula (about 8min to do each cell) it works
I have a sheet that I need to turn on auto calculation when selected. The problem I need to turn on auto calculation as well as sort another sheet "test" I do not know how to as the other portion of the code. attached will not work because it just runs in circles.
Private Sub Worksheet_Activate() 'THIS IS A SEPARATE SHEET THAN WHAT I NEED TO SORT FROM Application.Calculation = xlCalculationAutomatic
Sheets("6180").Select Application.Run "'BILLET-SLATE P414D working copy.xls'!Sortbypeprorprd"
I have 2 sheets in my excel document. The first sheet (sheet1) has student data (grade they got on a test) from C5:C35 all in 1 column. On the 'second' sheet (sheet3), I have made a profile for each student (there are 30 of them, spread out 1 per printable page). I want to take Sheet1!C5 result and put it in sheet3!D7. The formula in sheet3!D7 would just be =sheet1!C5. I want to 'drag' that formula to L7, then to T7 then to AB7 etc etc (they are all 8 columns apart), obviously not affecting any of the cells between them.
There are 12 pieces of data in sheet1 that I want to auto update in sheet3, for 30 students, so copy pasting, and adjusting the formula would take a long time.
I would like to drag down the formula from the last cell in Column A in reference to the last row in column B.
E.g My last data in Column A104 (code to select the last cell and this can vary from time to time the report is updated) Column B has data till B142. I want to drag A104(contains formulas) down to A142.
Here is what I have but Selection Autofill doesn't work.
I have a long list of inventory parts and there can be additions or deletions at any time. Currently the inventory is determined using formulas from other worksheets and this uses formulas. If I have a user insert a row to create a part in inventory, is there a way to make the formulas automatically be created in the cells within the row?
If I need to replicate a formula in some cells, I usually drag it down or sideways. Is there any easier way to do this? I have large numbers of cells into which to drag the formula. Can I specify which cell range the formula is applicable to (i.e. A1:A10,000 or something) instead of dragging? If so how?
I'm using excel 2000 and I just noticed something and am unsure how to change. I guess early on my building of a workbook I accidentally left calculation to manual. I would like to change calculations to automatic on permanent basis. I keep changing it and it changes back to manual after I close then reopen the workbook. I would prefer to change permanently to automatic....Any ideas would be a bonus. On all new workbooks I can save in automatic but I cannot change from manual to automatic.
Please find attached a daily copy of a spreadsheet that is used to monitor train running times.
The columns on the left - "Serv No, Serv Start Date,Train No:, Scheduled Arrival, Scheduled Depart" are provided to us automatically and the underlying cell formatting/formula cannot be changed. This information is cut directly from a daily report sent to us the previous day. The cell formatting for the start date is custom - dd-mm-yyyy hh:mm.
The sheet is usually locked out with user access only to the left hand side to cut and paste train times in and the input columns on the right hand side. User has no requirement to adjust any details in the left had column apart from to add additional trains at the bottom is need be. I have left the loaded sheet blank and complete with all underlying cell formatting, conditional formatting and formulas intact for you to play with.
Both sheets essentially do the same thing so whatever formula changes are made to the empty sheet can be adapted for the loaded sheet.
For simplicity, I will focus on the empty sheet. What I am mucking around with is having the user input the train arrival time in the "Actual Arrival" column - (column F)
What I am looking at then making occur is the spreadsheet automatically filling the adjacent columns with the appropriate text and calculations.
To do this excel needs to compare column the number (cell input is text formatted) as a number with the arrival time in column G. It then needs to calculate if the train is Early (16 minutes or more before scheduled arrival), on time (+/- 15 minutes either side of scheduled arrival) or late (16 minutes or more after scheduled). It then needs to automatically place the letter "E", "O" or "L" in column K and the time differential in column L.
My problem is two fold.
Firstly, I cannot seem to make the spreadsheet automatically enter a letter into column K depending on the above conditions.
Secondly, I have had limited success in having the spreadsheet compare column J with column F and working out the differential. However, this only works if the time remains linear. IE only if the train runs on time or late. It gives an incorrect time differential if the train is early. I will attach this spreadsheet tomorrow as an add on to this post.
I am doing a report which analyzes a specific person's transaction per country (3 criterias). The workbook has 3 sheets. First sheet is the database, 2nd is the report itself and 3rd is a reference table. I am using a sumproduct formula to count per person's transaction per country. It is giving me slow calculations.
I cannot possibly use a pivot table since there will still be other calculations involved after determining the transactions. I tried using database functions but i can't seem to understand how to go about it when there are multiple criterias involved.
I have an Excel file that has a certain amount of Array Formulas. It takes 30 seconds to calculate every time the user makes a change. Some users do not have the Status Bar showing, and they think that Excel is "locked up". I want to make a user form that will appear when the Stats Bar is "Calculating..." There are a lot of "progress indicator" tutorials on this site, but they all seem to relate to VBA code being inserted in between certain lines. That doesn't apply. My Excel file has no VBA. It just takes a long time to recalculate. I want a VBA form to appear, that will match the % progress shown on the Status Bar, whenever a change is made to the spreadsheet.
I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.
I have worksheets named 1 to 100. In a different worksheet "Summary" I have a summary of data of those worksheets. I want to change the worksheet number automatically in a row by dragging. For example, in worksheet "Summary" ....
I am looking for a way to get a spreadsheet to automate calculations of unit costs based on variable packaging names.
I have a series of packages that are denoted by text phrases. Examples:
4/6/12 2/12/12 6/4/12 18/12 24/12
For our purposes let's say the package names above will always be in column A. Column B contains the frontline price of a case of product represented by the phrase in column A. Column C will contain the cost per unit of product - this is obtained by dividing column B by the number of each package arrangement that can be found in one case. Most of the time the number of package arrangements per case is denoted by the very first number in the package name (ie, 4/6/12 would be 4). This won't always be the case though (18/12 would be a package arrangement of 1).
I am looking for a way for the spreadsheet to do all of the following and return the results in column C: if the package name contains "4/6/12", divide column B by 4; if the package name contains "2/12/12", divide column B by 2; if the package name contains "6/4/12" divide column B by 6; if the package name contains "18/12", divide column B by 1; if the package name contains "24/12" divide column B by 24.
Still adjusting to 2010 Excel...How do you use the automatic fill in feature of formulas; e.g. when typing =convert....a fx function appears....not sure how to use it
appending excel sheet number in a formula. Here is the example.
In an excel book, I have sheets 1 to 50. In each sheet i have a standard format to enter the details. And in one sheet I want to keep some summary with required cells. I made a table by applying formula, for example in cell A1 i kept formula ='9'!$D$9. In this '9' refers sheet9. In the similar way i want to capture the data from all sheets.
Is there any formula to append the Sheet number automatically (like dragging the shell) intead changing sheet number manually. because it is wasting so much time.
So I have a guest list workbook. There are two sheets. On the first one is a list of names on Column A. On Column B is a classification: 'C' if confirmed; "D" for declined; "I" for pending. I want to have all the names with "C" on sheet 1 appear on sheet 2 automatically.
My 10 MB excel file has calculation mode always automatic. It has 100 worksheet. Only a few of these worksheets are enable for calculation, for performance optimization.
Therefore, I use the macro: Worksheets("sheet1").EnableCalculation = False When needed, I do: Worksheets("sheet1").EnableCalculation = True
Unfortunately, from time to time, the calculation does not work. By chance I realize that by insterting a cell in this worksheet, the EnableCalculation became active. My problem is that I feel this is happening randomly, depending on worksheet, maybe depending on users or on sequences of entries.
how to merge data automatically in one sheet (master sheet) from specific sheet with different name sheet but same format?
Adding, deleting or editing should be automatic update to master sheet.
For example:
Sheet name:
Sheet 1: Salesman 1 - with data range A10:F310 (as his data file) Sheet 2: Salesman 2 - with data range A10:F310 (as his data file) Sheet 3: Salesman 3 - with data range A10:F310 (as his data file) Sheet 4: Salesman 4 - with data range A10:F310 (as his data file) Sheet 5: Salesman 5 - with data range A10:F310 (as his data file) Sheet 6: Salesman 6 - with data range A10:F310 (as his data file) Sheet 7: All Salesman - - with prepared data range A11:F2000 (as master data file/record for 6 salesman)
Column Name as a Header:
A10: Customer Number B10: Customer Name C10: Salesman ID D10: Promotion Type E10: Promo Item F10: Free Item
I have an automatic macro that runs from every sheet but is only based on one sheet. I had to protect the worksheet and in order to make the macro work I had to add an unlock and a lock code. The problem now is every time a user enters something it looks back to the other sheet and then comes back. Is there a way to prevent the user from seeing this. See code below
Private Sub Worksheet_Calculate() Sheets("Profit Calc Local Currency").Unprotect Password:="hspricing1" Application.EnableEvents = False Dim lr As Long lr = Cells(Rows.Count, "XEA").End(xlUp).Row Range("$XEA$1:$XEA$" & lr).AutoFilter Field:=1, Criteria1:="1" Sheets("Profit Calc Local Currency").Protect Password:="hspricing1" Application.EnableEvents = True End Sub
I have a nice little matrix which looks up loans and tells me the status of various modules within them
It looks something like this:
Code: =IFERROR(INDEX(AssignedLoans,MATCH($H$6&$D49,ModuleName&LoanNumbersList,0),8),"") and it is an Array.
Now, it is working fine until it gets to row 50 then it stops returning values. What's up? There is no difference between the rows except for the loan number change (from D49 to D50...etc). I looked up the loan number in the source document and it's there, so it's not that the source document is missing the information.
I need to calculate the total hours worked for a series of 22 locations. Each Location has weekly allocated hours ranging form 0 to 80.
For example: User1 worked 4 Hours on Monday at Location1 and 4 Hours at Location2. User2 worked 4 Hours on Monday at Location1 and 4 Hours at Location3.
I am assuming I will need to use VBA for this calculation.
Is it possible to loop through a list of sheets and execute some calculations that have sheet references from a different list of worksheets? For example, you have a list (list 1) of your worksheets, which will be the destinations of the calculations, and you have a second list (list 2) of worksheets that the calculations are based on. So, lets say there are sheet1 and sheet2 in list 1, and sheetA and sheetB in list 2. The calculations based on sheetA would appear in sheet1, and calculations based on sheetB would appear in sheet2. I thought the code would look something like this:
Code: Sub LoopthroughWorksheets() Dim sheet_name As Range Dim sheet_name2 As Range Set sheet_name2 = Sheets("WS").Range("F:F")
[Code] ......
I'm getting a "Run-time error '1004: Application-defined or object defined error" at this line:
I have a report blank that is comprised of numerous excel worksheets (fixed letter size). During the completion of the report, one may add, delete, and/or move worksheets. I want each worksheet to have a cell that dispalys 'page # of total number of sheets'. Is there a way to automatically update this information?
