i have a spread sheet which has a very complex simulaton in it. excel is not the ideal place for it, but it is not possible to port it wholesale to something more sensible. i have modified the code so that it is possible to run two copies of the spread sheet (in two seperate excel processes) at one time. Due to the dual core nature of the machine this has almost no overhead.
I have created a controlling spread sheet that is cabable of launching multiple copies of the simulation and starting the code executing. the problem is the controlling spread sheet always waits for the code to return before it continues, hence it would open two seperate versions of excel, start the first simulation executing and then wait until it completes before starting the next one. is there any way of starting a function in another work book without waiting for the execution to complete?
I want to copy several separate sections on a worksheet and place these sequentially in an array, then paste these in order into another worksheet.
I have written a macro to copy and paste between the two spreadsheets, however, this is rather basic as it involves straddling between the two and performing multiple alternate copy and paste operations, as only one section can be copied at any one time.
I am copying a large sheet into a spread sheet. The problem is one of the columns contain numbers with some cells having an asterisk at the end of the number. This causes problems with the formulas on other sheets that use this data. Can I use a formula or what is the best way to eliminate the "*".
I have included the table. What I want to do is in a user form pick a county ie Devon, Then by clicking "go" all entries that contain "Devon" are then copied to a " sheet 2" I don't want to use the Ctrl-F
I need to convert data from column IDS into separate rows, all other columns need to stay in tact. There are several distinct patterns for the IDS column, main identifiers are always starting with FILER or TEAL and the trailing numbers behind it have no more than 6 digits.
I have a spread sheet (sheet2) that runs an add-in and does some calculations and arrives at a result which is a number in cell P6. All I have to do is type in a Stock Symbol in cell A1, and the sheet runs the add-in, calculation automatically. If I change the symbol in A1, the sheet re-runs the add-in/calculation and gives me the result for the new stock in cell P6.
Sheet 1, has a list of stocks, indexed in Column A.
Rather than me changing the symbol manually in sheet 2 cell A1, how can I automate it such that it will run the functionality automatically for each symbol in Sheet1 column A sequentially and then tabulate the results for each stock/symbol in Sheet 1 column B.
I tried to attach the sheet/book herewith, but could not. It is less than 100KB in size
I am setting up an excel sheet, which requires over 40 sheets + an Input Sheet. The sheets are names, sheet 1, sheet 2, sheet 3...
now, cell A2 in sheet 1 uses a formula, say: 5.42*Input!A2
Cell A2 in sheet 2, would have the formula: 5.42*Input!B2
so and so so forth.
Since I am dealing with over 40 sheets, Is there any way of simplifying this process rather than manually typing out the formula in each of the 40 sheets (especially since each sheet would have over 40 rows, with Sheet 1, linking to Column A in the input sheet, Sheet 2 linking to column B and so on and so forth).
I have a few hundred work orders done in Excel. Each spread sheet is a seperate file but all are identical in structure (basic order form). I need to change the value in the same cell on all the forms (manager's name needs to be changed on all our forms).
Question: How do I do this without having to open and edit all five thousand forms?
I would like to calculate the average number of hours worked per day in a particular week from my time sheet (in "Google spread sheets"). Daily hours are calculated in hr:min:sec format. Days not worked display 0:00:00. The formula I use to calculate total hours worked that weeK is for row 9 as follows: =INT( SUM(G9,K9,O9,S9,W9) )*24+LEFT(TEXT( SUM(G9,K9,O9,S9,W9) ,"HH:mm"),2)&TEXT( SUM(G9,K9,O9,S9,W9) , ":mm:ss")
How do I calculate the average number of hrs and minutes worked per day (excluding days not worked, cells containing 0:00:00)
Im trying to import data from a plumbing supply website to an excel spread sheet.
On the supply house web site, for each part catgory there are corresponding pages containing a price list data table.
there are from 1 to 3 pages (price list data tables) for each part category.
i can import data from page 1 and page 3 fine. When i try to import data form page 2, it returns no data, or imports page 3 (instead of 2)
I spoke to the person who designed the website and he said that it was a problem within excel and not a problem with his program. (said it was a problem with excel not being able to recognize the page)
Public Sub ListMyFiles() Dim fso As New FileSystemObject Dim fso_Folder As Folder Dim fso_File As File Dim file_count As Long
Set fso_Folder = fso.GetFolder("S:SHARED All of TransportationTraffic AssessmentMultiway Warrants") file_count = 1 For Each fso_File In fso_Folder.Files file_count = file_count + 1 Cells(file_count, 1).Value = fso_File.Name Next fso_File Set fso = Nothing End Sub This works great, but is there a way for the macro to recognize the file names that have already been added to the spread sheet and only return new ones that have been added to the folder?
I am currently working on a very large spreadsheet with a current size of 94mb. So obviously I have problems running and using it. My question is a general one, I need to understand better why a spreadsheet gets so large. It has 55 tabs with the largest having 1000 rows. It also links in a number of places between sheets but also to external locations. I have moved all files that it is linked to in to the same file. I am at the point where I am thinking about starting again! And I was wanting some tips or even a recommendation of a book that could help me optimize my spreadsheet going forward so to increase the speed but also reduce the size.
I am trying to put together a projection spread sheet for a client for cash flow purposes. I have it laid out but I cannot wrap my head around the formula and I've tried a few. Here it is.
A1: Client Name B1: Invoice Date C1: Projected Due Date ( I have 45 days added to B1) D1: Invoice Amount E1: Jan F1: Feb G1: March H1: April I1: May J1: Juneetc...
I am trying to forcast out the invoice collection date (C1). So basically in the month of June column I am trying to say if C1 falls between June 1 & June 30th then it should equal D1 (the amount due in that month). So if the invoice has a due date (C1) that falls in that particular month than I am forecasting the cash flow in the month it's collected.
On the attached spread sheet here is what I am trying to do. In B2 I will either enter D (digital label) or F (Flexo label). With that than if a D is entered nothing else will happen with in spread sheet but if F is entered D23:Q40 will need to be cleared out and blank.
I am fairly new to macros and I am currently working on a project where I would like to create a 2 buttons which will filter by date and by owner. The spread sheet I have is fairly large and is added to weekly.
I have two objectives that I am trying to reach:
First I need to filter for all past due task items from the Thursday of the week I am working in back and for a specific person (owner) by the finish date
Second I need to filter for upcoming tasks one week out for a specific person (owner). The objective of this is so that on any given day a user can find any upcoming tasks that are due one week out.
If this is not an option. My thought was to create an input box where the user can input the date and the information on the spread sheet will pull all past due items or upcoming tasks. These two items need to pull seperatly not together.
I know I can create a macro by autofiltering by columns however I am trying to avoid having to go in and change the date each week.
Start date is column E Finish date is column F Owner is column L
I am setting up a spread sheet to calculate call duration it would be a great help if data could be entered using one hand IE use decimal point on the number pad instead of the colon is this possible. I will need to subtact start time from end time to get the duration
I have a large amount of data in a single spread sheet. Each row has a branch number on it, there are multiple branch numbers. Each branch number is located in Column A. I want to separate this sheet and put all of the branch numbers into their own sheet. how to do this without manually copying and pasting?
I have data in columns E through J under the headings of Monday-Saturday (E is Monday, F is Tuesday...J is Saturday). Then out to the left of the data, in column A, I have the week ending date (using Sunday as the last day of the week) which corresponds to the data under each day. There are multiple rows with different week endings but all the data is under columns E-J with the week endings in column A. So for instance, on row 13 the week ending in column A is 12/22/2013 (a Sunday) which means the corresponding data in the same row under columns E-J (the Mon-Sat columns) belong to 12/16/2013 (the Monday in the week ending on 12/22/2013), 12/17/2013 (the Tuesday in that week), 12/18/2013...and so on and so forth until 12/21/2013 which is Saturday in the week ending of 12/22/2013. Lastly, out to the right starting in column N and going horizontally out to column FD (will go out further as more days are added) I have the specific dates by day, it starts with 12/16/2013 and goes on incriminating by 1 day at a time until the end of all the dates included in the data (which at this point so happens to be 5/11/2014).
What I need done now is to take all the data which currently sits in columns E-J (the Monday-Saturday columns) and copy and paste it out under the correct specific date column that it belongs to starting in column N (going all the way out to FD) based on the week ending in column A. So with row 13 since the week ending in column A is 12/22/2013 the data in columns E-J needs to get pasted to row 13 columns N-S, then the same thing for row 14, except the week ending in column A row 14 is 1/5/2014 so the data in columns E-J row 14 needs to get pasted to columns AB-AG (AB is 12/30/2013...and AG is 1/4/2014). Below is the loop I came up with that accomplishes this, but for the reasons already stated.
Code: Sub newtest() Sheet2.Unprotect Dim drd As Long Dim rrd As Long drd = 13 rrd = 14 Do Until IsEmpty(Cells(drd, 1).Value)
Each sheet has the same basic formatting. A1 contains a name. B1, C1, D1 are column headers. B2:B is data. C2:C is data and always stops at the same row B2:B range does. The only differences between the sheets is that they might not stop at the same row. I want a macro that merges A1 vertically as shown in my spread sheet to the end of column B and C. I want a border around the merged data, as well as around the B data and the C data individually.
I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the