To Automate A Formula Sheet
Feb 16, 2008
I am trying to automate a formula sheet used by truck drivers to determine how many hours are available to work as they cannot work over 14 hours in a day or 70 hours in 8 days. This must be charted daily, including days off. Taking two consecutive days off resets the 70 hour rule. So...
Col B2:B8 Enter the number of hours worked each day.
Cell C8 Sum B2:B8 except when there are two consecutive zeros entered during the seven days being counted, then count only from after the second zero. (ie. b2=6, b3=0, b4=0, b5=8, b6=10, b7=7, b8=11 only sum(b5:b8)). So I am looking for the formula to to sum under this condition.
View 9 Replies
ADVERTISEMENT
Nov 14, 2005
I am trying to reference a date in a formula that points to a sheet name.
See Below.
A B
November 11, 2005 ='11-11-05'!$v$35
How can I automate the date to pull from column A and place that value in the date for column B? Column B is the name of a different sheet in the workbook.
View 9 Replies
View Related
Jan 26, 2010
I create a schedule based on abbreviations which is given to staff. Then this is retyped up with actual times and put in a schedule book that is the final product. I am trying to automate this change from one sheet to another
n = night shift = 2330-0800
D = day off
etc, etc
I have alot more abbreviations, but I hope I just need some advice and can figure it out from there....................
View 2 Replies
View Related
Feb 14, 2010
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
View 9 Replies
View Related
May 20, 2008
I have a stock sheet of about 1500 items that is printed every 3 months for stock take.
The changes are then entered manually. I have set up a stock order sheet using VLookup and manually enter the items that need to be ordered. I'm wanting to know if i could use a macro to do this automatically and check only for the stock items that have changed (ie. items have been sold ). Stock levels needed on hand are usually constant but is changed from time to time so will have to be taken into consideration. I have included an example worksheet which would better explain what i have been doing so far. Also if possible could the macro generate a new worksheet for the order sheet and name it the corresponding date and month of generation.
View 11 Replies
View Related
Nov 16, 2013
I want to copy certain columns from Sheet6 to Sheet1. For instance, Column G in Sheet6 would be copied to Column A in Sheet1. I have over 20 columns that need to be copied. There are over 5,000 rows in Sheet6. Thiomething that I have to do daily.
View 2 Replies
View Related
Oct 10, 2008
Is there any way to automate the calculation of a single sheet? IE to calculate when moving from cell to cell after entering data without calculating the whole workbook and any other open workbooks.
View 9 Replies
View Related
Jun 20, 2014
how to automate my excel sheet i have got.
It has code which when you click the "Save" button it saves the document and then converts a copy as CSV with the same name.
I have been trying to automate this to run every 2 minutes but everything i have tried does not work.
I have tried using OnTime events but again does not work.
View 2 Replies
View Related
Feb 24, 2013
I am required to fill up a sheet and provide to our Work force management team every week and it takes a lot of time from my day. I know this can be done easily with a code but I don't have enough experience with vb so can't write a good one myself
So here it goes -
I have a roster for my team (e.g. - sheet 2 "Roster") team members are required to work for 8 hours each day in their designated shift.
The codes in roster correspond to specific shift start time (e.g. - sheet 1 "codes")
Sheet - [URL]
I am required to fill up the sheet 3 "Staffing" each week for each half hour interval of a day that an advisor would be present for. So that means, I have to fill up 20 "P" for each member who is working on day from the time he would be starting his shift.
View 3 Replies
View Related
Aug 6, 2014
I'm looking to automate the process of repeating an excel worksheet (which is set up with various tables and formulas) for various countries.
I have a referenced list of countries set up in worksheet 1. So for example: in worksheet 2 I have a sheet set up for Albania - and I want to repeat this for another 100 countries. . Can I use the reference list of countries to auto-repeat worksheet 2 for worksheets 3 - 102 and auto-title the worksheet tabs? (E.g. Brazil, Canada, Chile..............)
View 2 Replies
View Related
Apr 18, 2010
I have the office 2007 Suite.I have a small business, where i sell online mostly. I'm creating a simple spreadsheet using Excel 2007, a workbook that has simple financial statements.
I'm creating a list of customers along with what they order, what they paid, how they paid, (paypal, cheque) etc, The first column is the invoice number, and each is unique to each customer. I add as i get orders. so my list of invoices has the following fields as column headers
Quote:
Invoice Number Customer Name, Paid by Paypal, Paid by Cheque, Amount, Cusotmer email, Customer address, There are more and i won't list them all. Now each time i send an order, i have to fill in the excel worksheet. I then create the invoice separately in Word 2007 which i print to send to the customer with the order in the mail. The third step is sending an email to the customer upon despatch, using Outlook 2007.
I don't want to use accounting software as that is too complicated for my simple little business. What i do want to do however is combine all three tasks instead of having to open different programs and copying and pasting between them. I'd like to be able to add the data to Excel either using a form (ideally) or direct, then i'd like to be able to click on the "customer email" field to send a confirmation email to the customer, and also to be able to have an automated invoice, either in excel or Word, which draws the information automatically for each invoice, so that i only have to print it.
At the moment i'm doing all 3 tasks separately, when i know they can be automated.
1. I have about 20 columns of data for each invoice, ideally i'd like to be able to enter them from a form as it gets cumbersome going through them all in Excel. The form could be either in Word or Excel, which would be easier?
2. Using the invoice number as the unique field, how can i create an invoice that would draw out the required fields from the Excel data automatically, just by clicking a button? I dont' mind using either Excel or Word, whichever is easier, and i do have Access too, but i don't know if i should use that, or not. I don't need to draw any other data. I only want to print invoices and send confirmation emails.
3. After printing the invoice and posting the order to the customer i sent them an email confirming. Again i have to manually enter or copy and paste data in the email, which i know could be automated, if i create a template, and have some fields that are automaitcally populated using the fields in the date already entered.
These fields ususally include the total paid, currency paid, address, name and how many ordered. All of this could be automated, but i'm not sure how to do it.
View 9 Replies
View Related
Dec 6, 2009
I have some formulas that combine template text and values from cells in that row to result in text that I then Copy and use in another place. I am trying to convert that to a Macro.
My Skills are Novice -- I have searched Google and Tutorials and it seems that my usage is not common so, I have not found answers to help me move ahead.
I'm working with a simplified version of what I want to do to eastablish a
Working model that I can modify and expand. I had a macro that ran to completion but I abandoned it. I realized that I was on a Wrong Track. It was created from recorded macro sections and googled routines and very clumsy.
Heres my Scenario
If my Excel Cells were
Cell A1 = 135252
Cell B1 = $13.95
Cell C1 = 3
Cell D1 = Oversize
I want to run a Macro from E1 that would Result in text of
Our Stock Number is 135252 - The Price is $13.95.
We have 3 in stock. Type of Packaging is Oversize
being left in Cell E1.
I would like the Macro be able to run on other rows as well when launched from Cell E of that row.
My Goal would be to make the macro work on a range of rows by Highlighing Cell E in those rows.
My Excel Formula to accomplish the same thing is -
="Our Stock Number is "&A1&" - The Price is "&B1&".
We have "&C1&" in stock. Type of Packaging is "&D1&"."
If I could get this much working, I think I can expand and edit to encompass my actual application.
View 9 Replies
View Related
Jan 15, 2012
Rules:
First rule is that no breaks can overlap.
1st Break is 40 Mins.
2nd Break is 20 Mins.
1st Break is at least 2 Hours After Time-In.
2nd Break is at least 2 Hours Before from Time-Out.
Each Employee might have a unavailable time for breaks.
Not sure if it is possbile to create a formula like this, or I might need an Excel Wizard to do this.
View 3 Replies
View Related
Nov 3, 2008
I'm trying to get a formula to show me the value of one cell based on the value of another to automate some internal processes. Cell J3 returns today's date, then J4 says what week number that is. J5 concatenates the two to make the look up. Column A is named range "PN", and columns C onwards are named ranges to match the column heading.
What I'm wanting is for the result in test cell 2 (J8), is to return the stock requirements for the current week, based on the result of cell J5. I can do this by using nested IF statements, but that would be 8 cells to calculate, then another one to find the cell that has a value. If there a way to get Excel to recognize the value in J5 as a range name, so it just needs the one formula?
View 3 Replies
View Related
Aug 17, 2007
I have a worksheet of 5 columns. What I would like to do is subtotal an amount in column d, where the item code in column b are the same. Lets say there are 5 rows with code 8150 in column b, I want to add all these rows up BUT ALSO add up any rows which are 8150+1 (x+1) i.e. 8151. All transactions where the code in column B, x + (x+1) = 0. All true results should then be cut and pasted into a new worksheet (one worksheet for all transactions that are true).
e.g.
8150 1233
8150 12
8150 644
8151 -2084
8151 -10
8151 -205
Total of above amounts is zero, therefore cut and paste to new spreadsheet.
Now that I think about it, its actually quite complicated.
View 9 Replies
View Related
Jan 2, 2009
I am want to copy a formula across several work sheet and have the formula always take data from previous work sheet.
2) I am working with this formula =C12+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C12")
and it comes from this thread http://www.excelforum.com/excel-gene...orksheets.html. I have included a worksheet attachment that has explanations
View 2 Replies
View Related
Jul 7, 2014
refer to attached file.
I have monthly sheet Jan,Feb,Mar.....Dec.
I also have Summary Sheet, Cell A85:C96 is labeled as Jan,Feb.....Dec (Vertical) Cell B84:E84 refers to Store1,Store2,Store3 and Store4.
I need a formula to summarize the monthly value for each storein row 60.
View 2 Replies
View Related
Oct 25, 2009
Sheet 1 is my main page containing all my data, full of formulas and is protected. Sheet 2 is a simple sorting page with no color and is simply for sorting and printing.
Sheet 2 has IF formulas refrencing rows and cells on Sheet 1.
Example Sheet 1 A2= Bob
Sheet 2 A2="" until I put in this formula:
=IF(Sheet 1!A2="","",Sheet 1!A2)
Now Sheet 2 A2= Bob
Simple...
But when I delete a row in Sheet 1 (using my cool new macro...long story)
I get #REF! in the cells on my sorting sheet, as I should I guess, because there is no more row there to reference. It's annoying, but what do I do to fix it without just unchecking the #REF! in autofilter on Sheet 2.
I just want to eliminate #REF!
Do I need to put something else in the IF formula?
View 9 Replies
View Related
May 21, 2014
I am trying to figure out the calculation steps that Goal Seek is doing behind the scenes so I can create the formula rather than clicking on goal seek whenever I need to make a change. I need to maintain certain weeks of supply every week depending on the sales. Let's say i want to maintain 5 weeks of supply every week, I would like to insert a column for "Suggested To Order' column that would change if weeks of supply goes below 5. In this scenario I would do a Goal Seek, but I want to automate the calculation so I don't have to do this step every week.
View 2 Replies
View Related
Feb 11, 2009
I have a workbook that I use for my purchase orders which is pretty simple. Each row is a separate PO, with 10 columns (po#, description, cost, date, etc.).
Is there a way to automatically generate new workbooks that contain all of the info from each individual row? For example: if I have 100 rows on my spreadsheet, which means I've issued 100 POs, then I want to have 100 individual xls workbooks, with the filename for each workbook representing the PO #s (206904.xls, 206905.xls, etc.).
View 14 Replies
View Related
Feb 22, 2007
Dont know if this is possible. I have a spreadsheet where cell A1 contains a value which is not fixed and can change daily, the value can increase and decrease. At present at each month-end I manually record the position of cell A1 at month end in cells C1 through C12 repsenting the 12 months of the year.
Example:
A1 @ 31/01/07 = 50 therefore cell C1 = 50
A1 @ 28/02/07 = 45 therefore cell C2 = 45
and so on for 12 months
what I would like to do is automate this process to automatically capture the value at month end into cells "c" and once captured, the figure to remain absolute for that cell/month and to ignore changes in Cell A1 that may subsequently occur.
Could this be done by date formula?
View 9 Replies
View Related
Nov 9, 2011
I have a VBA Macro that starts with a user form that gives me two options. I always need the same option selected, so is there a way to automate this? I want it to select Option B and click "OK" automatically. I don't want to get rid of the user form completely in the background because it has some other functions that need to remain intact.
View 3 Replies
View Related
Feb 2, 2013
Is it possible to set up 2 tables whereby if you change a field in 1 table it automatically cuts and pastes a row of info in to another table. Or possibly by clicking a macro hot button? E.g. An entry in 1 table is no longer 'live' so moves to the 'archived' table when a field is changed.
View 3 Replies
View Related
Oct 1, 2013
I have the table below and I am trying to automate the YTD calculation.
Jan
1
Feb
1
Mar
1
Qtr 1
3
[Code] ........
YTD
Need to automate.
View 4 Replies
View Related
Oct 27, 2006
I find that the picture toolbar opening automatically when I click on a picture useful (sometimes).
Is there anyway of automating other toolbars to open (such as 'chart') when I click on an object?
I've been looking at various add-ins in google - but can't find one to do that.
View 9 Replies
View Related
Jun 10, 2007
When I save an Excel workbook, I would like to automate it to also save as a web page in 3 different locations (and automatically close after save as a web page). I would like to be able to automate the save as a web page in the 3 different locations and close these web pages. But my Excel workbook (which I have saved as an .xls) I will close manually.
One location to save the web page would be on my desktop and the other 2 locations would be on different shared drives. Can this be done with ******* event or something?
View 9 Replies
View Related
Aug 22, 2008
My goal is to automate an excel userfrom. So from outside of excel, i would look to be able to interact with the controls (simply fill in a textbox for example). I'm thinking this may be possible using windows API (user32) and system hooks, but not entirely sure where to start.
I saw this previous thread, that suggests it may not be possible :
[url]
View 9 Replies
View Related
Nov 17, 2008
i have 110 different columns. i want to do the same thing to each of these formulas (ie i want to calculate things like ln (A2 / A1)
is there some way i can automate this process so i only nhave to type in my formulas once and it will automatically enter in the same formulas for the other columns?
View 9 Replies
View Related
Feb 27, 2010
I work in the accounting department. We have an excel workbook for each account with sheets for every month. These sheets contain a template with pre-formulated cells.
For example, the 2010 workbook for account XYZ will have sheets named - Jan 10, Feb 10, Mar 10 and so on.
What we do, is move and copy the previous month sheet, and rename it to the current month.
But each person is responsible for over 100 accounts, so it becomes rather tedious.
So I wrote a code which would automate this process.......
View 9 Replies
View Related
Oct 30, 2004
I have Adobe 6 and would like to automate the printing of Worksheets to PDFs using coding.
I want to control the location of the output i.e. specific directory. I can do this manually by selecting File...Print Select the Adobe PDF printer....go into options and deselect view PDF results. On printing I then get asked to soecify a location and all works fine....but how can I do this in code.
Unfortunately it also appears that when you restart Excel, the printer properties for the Adobe PDF printer are reset.
How can I code in the change in properties so that I can fully automise this task?
View 22 Replies
View Related
