In cell b5 I have =today() which automatically updates the date on a daily basis. What I would like to happen is for each day there is a new line is automatically inserted with the date so:
Sat26/04/2008gone off sickFri25/04/2008Thu24/04/2008Wed23/04/2008Tue22/04/2008Mon21/04/2008Sun20/04/2008Sat19/04/2008Fri18/04/2008days holidayThu17/04/2008Wed16/04/2008Tue15/04/2008
I would also like any information on each of the line to move down when the new line is inserted. I am not really that good with macros or VBA but feel that is the only option to solving this problem. Also would everything still update even if the file wasn't opened for lets say the weekend. So it is constantly rolling.
I this, We are distributing work on 6 hrs/day basis. What I need is, I need to automate the work distribution on daily basis and should show the hrs also. and the projected date of completion. (yellow highlighted cells are where the formulas are needed)
How to go about fixing my spreadsheet so I am not having to manually update it each day..here is my forumla I am currently using...=(AVERAGE($D$2:$AH$2)-C5)*AI5...basically i need the cell below in D2 to change as every day a new day rolls off..for example the following day I need this formula to be =(AVERAGE($E$2:$AH$2)-C5)*AI5 ....so just that day changes.....do I need to use an If/then statement? if so how?
While I was working my daily expense I come up with this issue. I do eat outside while I am on work. Sometimes I go to Pizza, sometime I go to mexican etc etc. The common between them is word FOOD. I would like to modify the formula suggested by Ron Coderre
I'm creating an log to track the total number of hours an employee works in one day and calculate regular and overtime hours worked based on the following criteria: overtime will be >8 hours in one day and >40 hours in one week.
Right now I have it 'mostly' figured out, but under certain conditions, it calculates more than 8 hours in one day for straight time. Is there a way to set a maximum value for the straight time cell and have the difference be added to the overtime cell?
I am trying to build a sheet to track deliveries into the company I work for.
We book loads to come in at set times for production, I am wanting a sheet which I can enter the due time and the actual time of the the different companies and then produce a report in graph format to see which are the worst at late or early deliveries.
I have a spreadsheet, in which I need to lookup invoice numbers from a raw data file with ~5,000 line items on a daily basis. The lookup is based on two criteria searches (1) search product type (2) search product make. In this example, I have 4 product types:
1 – car 2 – truck 3 – boat 4 – motorcycle
For this example I want to search invoices; (1) first search for cars only (2) search for product make. In my attached example, the first item (cell E2) would return invoice number 7147875-FRD from the raw data file. The second item (cell E3) would return invoice number 7147877-NSN.
I have a spreadsheet to record profit or loss on a daily basis. The figure for each day can therefore be positive, negative or zero.
I want to add a column to display the total for the last 7 days (NOT the last 7 calendar days), in which either a profit or a loss was recorded (so excluding any cell that is zero).
I would prefer to add (insert), the column for each day as it comes and the range would obviously vary if the new day's figure was not zero.
See the attached excel table. I need the cell E4 to keep adding the values typed in the cell D4 on daily basis.The cumulative shouldn't be changed if there is no value in the cell D4. E4 should accumulate and keep the totals typed in D4...
I have an amount of data split into various fields. I need to do something similar to the following:
In each row I have three fields separated by columns: name, sector and specialty. There is only one name and one sector for each record, but possibly several specialties (of which there are fifteen types in total, so they do not appear in a consistent column. specialty 'i' could appear in columns 3, 4, 5 through to 17. ie, sample entries below:
sheet 1 a1 name a2 sector a3 specialty i a4 specialty iv b2 name b2 sector b3 specialty ii b3 specialty iv b4 specialty x c3 name c4 sector c5 specialty i c6 specialty ii
The thing is, for every specialty in a row, I need new records created, so the above would become:
sheet 2 a1 name a2 sector a3 specialty i a1 name a2 sector a3 a4 specialty iv b2 name b2 sector b3 specialty ii b2 name b2 sector b3 specialty iv b2 name b2 sector b4 specialty x c3 name c4 sector c5 specialty i c3 name c4 sector c6 specialty ii
i want to generate sheet automatically from existing sheet applying auto filter on specific column. i attached the sample which 3000 rows actually i have more than one lac rows so i have to put filter on sub_div column and then copy and paste to another sheet and give the name of sheet like F21. i want to do automatically this provide vba code or function for this
I need to create a spreadsheet that has approximately 1000 rows with the same exact information in each of them prefilled (as I use the "drag-down" method). I use this chart throughout the year to enter various bits of data in each row. I also use conditional formatting in each row. The company I work for wants to have an empty space between each row. " You can create the blank rows separately and then interleave them with the existing rows by sorting. To start, insert a new column to the left of the existing column A. Enter 1 in cell A1 and highlight column A all the way to the last row that contains data. From the Edit menu select Fill | Series and click on OK. Column A should now contain numbers from 1 to the total number of rows. Press Ctrl-C to copy these cells to the clipboard, click in the cell just below the last of them, and press Ctrl-V to paste. Now highlight the entire data area, including the new rows with just a number in column A. Select Sort from the Data menu and choose the No header row option in the resulting dialog box. Under Sort by select Column A, under Then by select column B, and click on OK. Finally, delete column A. You now have a blank row after every one of the original 1,000-odd rows."
This works great for the data that I copied (with the drag down method) to all the rows. However, this method does not insert an empty line in between all the conditional formatting I have throughout the spreadsheet. This is my conditional formatting formula I have "manage rules" which is under "conditional formatting" =NOT(ISBLANK($A6)) (*please take note that this is selected for 1000 rows.) Maybe there is some kind of change in the formula that I need in order for the conditional formatting method to be copied onto every 2nd line of the 1000 rows".
I have a spreadsheet that I have people fill in. A few columns have VLOOKUPS. I want to allow people to insert lines in this spreadsheet and have the formula automatically copy in the new line. It seems to already have that behavior for formatting and conditional formatting. Is there a way to also have that for formulas when inserting lines?
Name City Assigned Salesman ----------------------------------------------------- john Mountain View (formula using VLOOKUP) cindy Palo Alto (formula using VLOOKUP) ronnie sunnyvale (formula using VLOOKUP) ---------------------------------------------------
Insert line between john & cindy. Formula doesn't copy.
I ask them to copy the formula but they often forget. If I can make it a little more robust,
1. to leave a blank line inbwteen Shop A's Apples, Shop A's Pears etc
2. I also wanted to total up the price totals....(see example below).So if the last price was in E4, the total of all those prices above would show in F5 ....
One has around 129 lines for the first years 2006 in sheet1. The other links to the first 129 rows. Then I've linked the top 129 in sheet2 130 down for 2007 and so on for 5 years.
How would I add a line in Sheet1 then would insert 5 lines in sheet2 keeping the same order.
e.g
Sheet1 Name Rev Year John £120 2006 Row 5 Jack £150 2006 Row 6
Sheet2 (linked) Name Rev Year John £120 2006 Row 5 Jack £150 2006 Row 6 John £130 2007 Row 134 Jack £160 2007 Row 135 John £140 2008 Row 263 Jack £160 2008 Row 264 John £150 2009 Row 392 Jack £170 2009 Row 393 John £155 2010 Row 521 Jack £180 2010 Row 522
One of my job responsibility the following up our store stock, usually we consume constant daily value form our stock, as example we used daily 200 mt of corn, 150 mt do soya bean meal and so on, I would like to creat excel sheet to deduct this value from the previous column daily automatically.
I have an excel file that I use to track all my financial investments. The file is already set up with a query to retrieve stock prices when I refresh the document.
I would like to be able to open up the document, and store the closing price of the stock(s) with a date in the next column over. With this information I can graph weekly, monthly annually, or any time period I want.
I have been searching for a couple of hours now, and I could not find anything similar to this. I would think this is a pretty common idea for many people tracking their investments with Excel, so if it has been covered perhaps someone can point me in the right direction.
I don't have any programming experience in excel so as detailed of an explanation as possible
I have 02 separate excel workbooks 01 = source file 02 = data list
The workbook "source file" contains all the data The workbook "data list" contains data for work with Drop-down list. And i can easily select my required description by just dropping down the list.
In case, i have new description i add it in the source file and it is updated in the working "data list" sheet.
This applies to Field device column also. But i want to improve the selection criteria by creating such script or formula which could check the input of the description and automatically selects the field device and its relevant signal (output type).
Screen shot is attached : Automatic Selection.jpg‎
I want to set up a table that automatically adds rows, ideally to the top... The way I was guessing to do it would be to alter rows in the table to have a variable... My variable is 'acctstotal' anyway I figured there would be a way to edit the rows to acctstotal+ 1 or somthing along those lines but I havent been able to figure it out
btw acctstotal is a variable that simply counts the number of nonblank lines that are in the first column of the table which tells me the total number of accounts
I have no experience programming although I'm vaguely familiar with Excel offering ways to automate many processes. My current situation involves me having to insert a new line whenever a there's a change of term in a row after a long sequence of repeated terms in a specific column. So, for instance, we have something like this:
How can I have a number inserted into text on an excel sheet. for example if I have the number 100 in cell A1 and I want it inserted into the following sentence in sell A2:
You are 100 years old. I want the number to be able to change automatically in this sentence when the number in A1 also changes.
I can arrange for a new row to be automatically added to a spreadsheet after I have sorted the currencies in order?
So basically I am trying to record a macro that sorts the column into currency order then after the currencies have been sorted I would like an extra row added so that I can enter a total field in between....
Macro that would insert multiple pictures at once into one column with two rows per page (so that only two pictures are on each page) for word?
If so, would there also be a way to make it to were photos would have a caption box underneath each photo that list them as "Photo #1: Photo #2: Photo#3:" ect...
I know this is quite a task to achieve but I'm still relatively new to Macros and VBA and have a ton of pictures to integrate into Word for work.
i have a question with regards to the shading of a table in excel 2010.The grey/white shading of the sheet should adjust automatically when lines are inserted / deleted...what would be the most efficient way?
