Inserting Rows With Formulas And Formats Based On Cell Value
Jan 11, 2014
I am attempting to create a template to make time and dollar budgets for various projects easier to create (20 plus projects a year with varying number of employees).
I want to have a template in which you input the number of employees working in a cell (IE. 3 employees), and with that the table expands to have enough rows to have 3 employees data input. However, only insert the number required but not overriding "totals" row below. With the new inserted rows I would like to keep the same format and formula for the row it was based on.
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,
I have a spreadsheet with customised formats and formulas and the problem is that when add a new row I have to go through and manually copy all the data to the new row and as there are many columns in the spreadsheet this takes forever.
Is there anyway I could automatically copy all the formats/formulas when I insert a row?
PS: I do not want to copy any data into the cell only the formatting and formulas
I have a very large table with 7000 rows and each time I open the file it takes excel about 1 minute to open it.Initially all cells in the rows are empty. Each row in the table has the same formats and formulas(eg. in J106 I have =IF(C106=1,1,"") and in J107 I have =IF(C107=1,1,"")).So the excel allocates all the memory and does the formatting and computations beforehand. How can I add new rows(preserving the formats and formulas of previous ones) dinammycally and automated? An example will be if I start enter data in a row add another row at the end of the table or a button that when I press it adds lets say 100 new rows in the table. Below is the table, the rows in the table to which I add data start form C15 to C7014.
I have a spread sheet that I have locked with the expection of certain cells. I also have left the ability to insert and delete rows. The cells that are locked have functions in them run in sequance.
Is there a way that when the new row is inserted its copies the formula from the row above automatically but also adjust for the new row and adjusts the rows below it automatically?
For example if I insert a new row between rows 2 and 3 below. (orginal layout) a1 (unlocked) b1 (locked)(function is =sum(a1:b1) a2 (unlocked) b2 (locked)(function is =sum(a2:b2) a3 (unlocked) b3 (locked)(function is =sum(a3:b3) a4 (unlocked) b4 (locked)(function is =sum(a4:b4)
(layout after inserting row) a1 (unlocked) b1 (locked)(function is =sum(a1:b1) a2 (unlocked) b2 (locked)(function is =sum(a2:b2) a3 (unlocked) b3 (unlocked) *inserted row* a4 (unlocked) b4 (locked)(function is =sum(a4:b4) a5 (unlocked) b5 (locked)(function is =sum(a5:b5)
this is what i want the end product to be. (layout if formulas are copied and adjusted after inserting row) a1 (unlocked) b1 (locked)(function is =sum(a1:b1) a2 (unlocked) b2 (locked)(function is =sum(a2:b2) a3 (unlocked) b3 (locked)(function is =sum(a3:b3) *inserted row* a4 (unlocked) b4 (locked)(function is =sum(a4:b4) a5 (unlocked) b5 (locked)(function is =sum(a5:b5)
On sheet "Create Package" in cell "AA14" I have a value (lets say 2).
I want it to go to sheet "Samples" and insert a number of rows equal to the value on sheet "Create Package" cell "AA14" (so 2 rows)
I have a header row in row 1, so I would like it to insert the designated number of rows beneath that.
Then I would like it to copy a designated number of rows (based off of the "AA14" value, so 2) from the "Create Package" sheet starting at row 66 and then paste special values into the new rows that were inserted on sheet "samples".
I was tired of making my list smaller to upload it here, so I uploaded the complete thing on megaupload :P Here is the link:
http://www.megaupload.com/?d=1W6PBADE
On the bottom of the list is a button; "Add New Anime". When you click on that you get a UserForm where you can enter; Title, Type, Total Episodes, D/L Eps and Watched Episodes. When you press ''Add Anime" the entered values are entered at the bottom of the list, underneath the corresponding list names on the top.
Now there are 4 list names left (Left, Status, Status 2, and Progress (%)) I still have to make a option to enter Status 2 as well in the UserForm.
Now the question:
In columns F (Left), H (Status) and I (Progress (%)) are formulas. How can I copy those formulas automatically when I add something new with the UserForm? And can it automatically insert a new row, since I have to move the buttons down with every new title.
Each week I format a report that I receive that lists every product a particular employee closed. Its a list of each employee, the product and date. I'll have about 20-30 lines for each employee. I can easily sort by the employee name to have a nice list.
From here what I do is use a forumla to mark where each employee's name changes, then filter by that changed line and insert page breaks for each of these lines. Is there a way to macro this to save me time, having 270 employees takes alot of time still, because thats 270 manually inserted page breaks.
I have a table with 15 columns and 10 rows (initially all cells are blank but with formulas and formatted) and I want a way to add automatically a new row in my table each time something is entered on first cell in each row. For example if I enter something in cell A1 (first cell in my table) then automatically add 11th row and if I delete the value in cell A1 it is ok to leave the 11th row there but if I I go on and enter a vlaue in A2 then add row 12th. Of course I want the new rows to have the same formats and formulas as the previous ones.
1. cuts+pastes all formulas and formats to another location. 2. cuts+pastes required formulas but not cell formats to another location.
The s/s is over 330,000 rows deep which makes manual changes impossible.
I attach small s/s containing both codes.
What I want to do is copy+paste formulas+formats from cells having a number in the cell below to cells 4 columns along and two rows down. The s/s illustrates this.
I have a names in column A starting with row 7 as follows:
Walden, Douglas E Haden, Michael Wilson, Matt David
I need help with coming up with 3 formulas:
(1) That returns the First, Middle Initial, & Last Name in column P of the same row: Cell P7 Douglas E Walden Cell P8 Michael Haden Cell P9 Matt David Wilson
(2) That returns the First & Middle Initial in colum Q of the same row: Cell Q7 Douglas E Cell Q8 Michael Cell Q9 Matt D
However, I am so new at this, the simpler the formula the better because I want to try & understand it, not just learn it.
The problem: I am getting values when doing a pastespecial for formulas and formats.
Manually copying and doing a pastespecial with the mouse gives me the correct results. Below is code I got from the forum, "here", and am running in a test workbook.
Option Explicit Sub Test2()
' Dim rSource As Excel. Range Dim rDestination As Excel.Range Set rSource = ActiveSheet.Range("A1:C1") Set rDestination = ActiveSheet.Range("A1").End(xlDown).Offset(1, 0)
1. In whatever cell is selected when the macro is run, enter a new row.
2. Copy the information from the row directly above the new row and paste (values, formulas, formats, etc) into the new row.
3. Return to column P in the new row, i.e if the new row is row 11, then return to P11, for row 12 return to P12, etc.
I have tried recording the macro but because it is hard coded to specific rows, its not working. I have attached a sample copy of the sheet (had to zip due to the size of the file).
In my spreadsheet, I have approx. 300 'blocks' of data, one for each 'vendor'. I am tracking 5 stats for each vendor for 12 months. Each 'block' has 5 formulas WITH conditional formatting (only one condition used) for each month, so each 'block' has 60 individual cells with conditional formatting. Excel throws an error when I try to copy the block of formulas and formats down the sheet a few hundred times. I have figured out it isn't the formulas that is the problem. It must be some kind of limit Excel 2003 has for how many cells in a workbook can have conditional formatting.
I`m making a list of gas and electricity readings and want to show the difference between the last reading such as =h5-h4 and then the next to show =h6-h5 and so on. How do I copy this formula for subsequent rows or columns.
I have formulas in cells B8:F8. I need to copy these formulas down based on the number of rows in column A that has data. The data in column A will vary based on user input.
I want to be able to a place a picture on a workbook based on a cell. In column A, I will run a sql query to pull a list of items. In column B, I have the items formated thus they have a the .jpg. next to item of the picture i.e Hammer.jpg. whereas column A lists Hammer and Column B Hammer.jpg. In column C, I would like the pictures to reside in this column.
So far I have this macro but I can only get two pictures to come up even though my list has 4 items on it.
Sub pic() Dim x, c, d, a As Long d = 2 c = 40 For a = 2 To 6 b = Cells(a, 2)
I can insert lines fine, but the problem is that now I want the worksheet to be able to delete rows without inputting time stamps. It can insert them with no problems. I have attached the file on as well.
I have a problem where my conditional formatting is broken when new rows are inserted because Excel is auto updating the cell references. I am not able to use absolute references because I need to be able to Copy the entire information many times on one sheet to handle an ever changing number of projects.
I have attached an example file which shows a simple version of the sheet. There are areas for two projects now, but more would be added to the sheet as needed by just copying the entire section of one project and pasting it at the bottom. For each project, there is a cell which has a data validation list, e.g. B6, from which the user can select the current stage. The list of stages is obtained from column A, e.g. A9:A18. Whatever stage is selected, I need it to be highlighted in some manner. I currently have conditional formatting that checks if the value in B6 matches the information in the current cell and will change the text font color if True.
The problem occurs when new rows are inserted into the project. For example: if the project requires a Beta 1.1 stage, then a row would need to be inserted and labeled for that stage. However, Excel auto changes the cell references so that it no longer looks at B6. I need some way that the current stage can still be highlighted when selected in the Data Validation list.
I know that what I am needing to do may not be the best method to go about this, but I am having to work within the confines of the software available to me and the intended users of the file. Since the number and length of projects can change on a daily basis, the users need to be able to add and remove room for additional stages and projects whenever they want. I have a basic solution available to me using a macro, but the overall solution is clumsy and just leads to more problems.
I have a spreadsheet that has formulas in cells C3 to C10 (cells C3 to C10 have =(sheetc), (cells C3 to C10) formulas. I want to be able to add a row somewhere in between those cells and have the that new row take on the same cell formula as the others...resulting in cells C3 to C11 now having formulas..
I have a very simple inventory spreadsheet that I used to keep track of certain products. The issue is that I often have to insert new columns and when I do, the formulas get convoluted. Is there a trick to making them follow the structure of the formula before?
My formula is =SUM(GT4:GU4) and appears in the Running Total column. When I insert two columns - a white column where an order will be inserted, as well as a running total column. When I perform the insert, the formula for the inserted columns, as well as the set of columns immediately to the right require corrections:
Inserted columns: =SUM(GT4:GW4) [Should be GV4:GW4] Columns to the right: =SUM(GV4:GY4) [Should be GX4:GY4]
I know how to manipulate the formulas correctly, I would like to keep a clean file for my predecessor.
I am wanting to build a macro to calculate the average of a range of cells. I have about 2000 lines of data, and I want to average the first 12 cells (then paste the answer somewhere else), then average the next 12 cells, and so on. Using a loop to do this is simple enough. My problem is that I can't insert variables into the average formula as the cells to be averaged
Sub AutoAverage() For x = 0 To 20 For y = 0 To 171 FirstRowRef = Workbooks("NP FT01-03 010206.xls"). Sheets("NP - FT01") _ .Range("a5").Offset(12 * y, 12 * x) LastRowRef = Workbooks("NP FT01-03 010206.xls").Sheets("NP - FT01") _ .Range("a16").Offset(12 * y, 12 * x) Workbooks("Mega Spectrums.xls").Sheets("NP - FT01").Range("a5").Offset(y, x).Select ActiveCell.FormulaR1C1 = "=AVERAGE(FirstRowRef:LastRowRef)" Next y Next x End Sub
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?
I have about 10 rows of data that have columns of information describing computer hardware and pricing. There are 10 rows, because each row represents a different country and different tax rates and pricing changes per country. So each column of a row has a specific formula to calculate information for that row's country information. All these 10 rows of formulas are kept as a reference at the bottom of the sheet.
At the top of the sheet I need to have a drop down menu to choose a country and then it will automatically copy and paste the row of that country to the current row at the top.
Is there any easy copy/paste macro for such a thing?
I'm working with a large report that includes ~6000 rows of data. Within the report the data is separated into a couple hundred town names - "Town of XXX" - and I need a page break at each town. Is there a way to insert a page break based on text in a cell? Should be an option in conditional formatting but... Need to avoid VBA.
