is it possible, after inserting new sheet rows, to automate the copying of formulas and cell formatting into the newly created space, instead of manually copying the formulas and formatting down into each column?
View 6 RepliesI have applied conditional formatting to a cell using formulas and i want to copy that formatting to a new cell using similar formula but pointing to different cells. Is it possible to do so? I have tried to copy and paste special>formats, but that just copies the conditional formatting with the same formulas.
Sample: =IF($C$5/$C$295>$R$5,TRUE,FALSE)
I want to copy to $D$5/$D$295>$R$5,TRUE,FALSE) without having to paste the new formula in each time.
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?
As you can see in the attachment: worksheet: calculations. I make conditional sums in this sheet. After completing the 1st possibility of 1st condition with all possible 2nd and 3th conditions, I now need to make the same conditional sums for the second possibility of the 1st condition. So basically, I can copy past the formulas only "TV" does now need to become "MB" thats all that needs to change in the following 170 formulas. Is there a way to automate this proces, so that I do not need to do this manualy one at a time?
View 2 Replies View RelatedI 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 RelatedI need to be able to copy a formula from a row that is 180 rows before the current cell.
I then need to edit the formula so that the rows all start at 6. then i need to change the column references.
The formula that I am editing looks like this after it has been copied from before:
=IF('Entry Form Portrait'!$d870="m",IF('Entry Form Portrait'!$n870="a",'Entry Form Portrait'!$a870,""),"")
So I need d870 to be changed to d6, the n870 to be changed to o6 (current column +1), the a870 to be changed to a6.
Then this resultant formula to be copied to the next column and a6 changed to b6, then copied to next column again and b6 changed to f6.
Then all 3 columns to be copied (or autofilled) down 105 rows.
I tried to do it by recording the macro, however it only works for the first time that i use it, and then just keeps repeating in the same place. I need it to use the cell i have selected as the starting point.
I've got a problem with copying cells from table A to B.
A short insight of the situation:
Table A shows: ITEM_1 = 3, consequently ITEM_1 from table A is copied to table B for 3 times.
I've got a few huge information blocks to handle (up to 5000 items) obviously it's unreasonable to do this manually.
I've added an xlsx file with more detailed description.
Example_WHS.xlsx
I am trying to capture data for statistical analysis, but have hit a snag. As shown in attached sheet I have each day for the year broken down into hour time slots into which data will be added. As I will need to come up with a number of similar sheets in the future I was wondering if there is a way to automate through vba, a quick way to copy down the hour slots for each day, 365 times, rather than the laborious task of copy and paste manually.
View 5 Replies View RelatedMy problem is automating a process that takes information from a single column with a range of D7:D77 in 22 spreadsheets and places this information in a summary spreadsheet as rows corresponding to the names of each spreadsheet. This is within the same workbook.
For example say spreadsheet A1 has a column from D7:D77 I would like this information in a summary spreadsheet with the row titled A1 and D1:D77 transposed. This would then be repeated for the remaining spreadsheets
I have a piece of code that runs through various excel files and takes the data (minus the header) and pastes that into a separate workbook. The piece of code that does the actual copying i think is this (i have used code from the msdn website)
Code:
With sourceRange
Set destrange = destrange. _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value
I have tried replacing the sourceRange.Value with sourceRange.Text however when i do that it will copy nothing any more.
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.
I am having a problem with a excel spreadsheet. There are no formulas or anything, just text that I used excel to get everything lined up. I haven't had any issues before, I was copying and pasting within a document. It was all text with different row heights set. I ran into a problem now where the text copies and pastes fine but the row heights aren't coping into the cells I am pasting into, just the text. What do I have to do so they will copy? I don't want to have to go an individually adjust each cell.
View 2 Replies View RelatedExcel 2010
Conditional Formatting 1 cell with 3 different formulas
I am monitoring tank levels using a program called "PI". I need to know if the tank is rising, lowering or staying the same.
I am using conditional formatting to turn red if high, blueif low and yellow if stays the same. Column B, F and J are tank volumns, Column C is in feet and D is in inches.
A B C D E F G H I J K L
1 TIME LEVEL FT IN TIME LEVEL FT IN TIME LEVEL FT IN
2 7:00 3628 18 11 9:00 3456 18 0 11:00 3321 17 3
AB AC AD
1305 5600 3600
LOW HIGH CURRENT
I started using formals =$B$2=$AD$2 COLOR YELLOW
=$B$2>$AC$2 COLOR RED
=$B$2
i have created a table. when i m editing new row then formulas is copying bu value is not copying ?
View 3 Replies View RelatedHow to copy and paste conditional formatting with our changing the cell values. My first row of data starts in row 4 and here is what I have in D4
If D4 is less than or equal to AA4, AW4, BS4, CO4 then it will highlight D4
Now I want to copy that format and paste it for all rows in column D. The problem I'm having though is when I paste the formula it doesn't change to match the row I'm on. For example, when I paste it in D5 this is what I get
If D5 is less than or equal to AA4, AW4, BS4, CO4 then it will highlight D5
How do I paste it so that it will update to show AA5, AW5, BS5, and CO5. I want to be able to do this for 500 rows.
Excel 2007.
I have conditional formatting set up so that the cell becomes highlighted if it contains a specific text.
Example of wanted conditional formatting:
Conditional formatting rules manager
Rule
Format
Applies to
Stop if true
[Code] ......
For some reason this formatting is inherited by another adjacent cell as we continue to input information.
For example:
Column M is formatted so if 'AP' is placed in any cell in that column the cell fills pink. As more information is inputted into the sheet, the conditional formatting copies to Column N. It does not happen with every entry and I have not been able to isolate the specific steps to recreate the copying. Multiple people use the same sheet and fill it out and needed.
After working with the sheet changes are made to the "applies to" column without people meaning to.
For example:
Conditional formatting rules manager
Rule
Format
Applies to
[Code] .......
I would like to make this formatting so that is only applies to the Column M and not "travel" to other cells of the sheet.
I have attached the file. The row being copied is row "X" for both buttons. I have 2 buttons in the same worksheet to copy a row and insert the copied row below it. I have this macro running for 2 different rows in the worksheet, assigned to the respective 2 buttons. See my code below.
View 4 Replies View RelatedI wanted to see if it is possible to copy a table from one sheet to another sheet, but only the values of the cells, without the formulas in it. As there has to be a different calculation in the new table, when the calculations are done, the numbers go all crazy in the Row that has a formula in it.
This is the code I have got so far:
Sub Copy_fromSheetinMA()
Dim CellValue As Range
Sheet2.Range("Table1").Copy Destination:=Sheet1.Range("Table2")
For Each CellValue In Range("D2:CW50")
CellValue.Value = (CellValue.Value) * (135)
Next CellValue
End Sub
When I insert a row in my table (below the headers, which are between rows 1 and 4), I want the formulas from the above row (or below row) to be copied down to my newly inserted row. I say "below row" as well in case I want to insert a new first row and want the formulas in the row underneath to be copied up.
e.g., formulas currently in the first row are:
Column A: =SUBTOTAL(3, $B$5:B5)
Column H: =IF((F5="N*"),(1),"")
Column I: =IF(((SUM(H$4:$H5))=(SUM(H3:$H$4))),"",(SUM(H$4:$H5)))
Column J: =I5
Any other values in the other columns should not be copied to the new row.
I also do not want my table to be scrambled if I should delete any particular row.
How can I create a macro that will:
Copy a formula in multiple cells to the next cells in each tab that I designate in my workbook.
For example,
I need the EXACT formula in cell N13 to be copied to O13. And then paste special the "value only" back into N13.
I also need the EXACT formula in O19 and O20 to be copied to P19 and P20, respectively. And then paste special the "value only" back into O19 and O20.
Can I have this exact process done in several different tabs within my workbook?
I am working on a file that has multiple worksheets with many links, lookups and formulas; some between the worksheets and some external to another Excel file. The plan is to use this file as a template and copy it over and over with new names. Once I save the file as a new name in a new folder on my network, will I lose all these links, lookups and formulas? Or is there a way to maintain the links or do a global change of the formulas (i.e., the original file name is "TEMPLATE" and the new file name is "PROJECT!1")?
View 2 Replies View RelatedI am running Excel 2007.
I have set up a table and for ease of explanantion Column C has a formulas in it to add together the values found in Column A and B.
EG C3 foumula = "=sum(A3+B3)"
Now in cols d & e I have manually entered figures and in colum F I want to add up those figures so F3 should read "=sum(D3+E3)"
In the past I have always been able to just copy the cell C3 and paste it into F3 and the formula would automatically copy and offset the various cells to the correct cells for the new posiitoning.
However, for some reason when I copy the cell, it now pastes the value only into the pasted cell and does not copy the formula, if I click on paste special to try to just copy the formula I now get a pop up asking me if I want to paste as Unicode Text or Text.
I have no recollection of changing any settings.
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)
How do I copy a range of cells containing formulas to another range of cells without chaning any of the letter or numbers from the original. I want a mirror copy of whats in A3:B10 to got to D3:E10..
View 3 Replies View RelatedI have a spreadsheet with a lot of financial information going down a column (about 500 rows down). Most of the cells are just typed in, but certain rows have sum formulas in them. Is it possible to copy values from another workbook, paste the values in, but somehow keep the formulas that are running through?
View 1 Replies View RelatedI'm working on a spreadsheet that tracks project accepted and done over a course of time. I am expecting the data to eventually reach the thousands in a year's time. I have several formulas in specific columns that I need to replicate as the user enters new data into the next row.
While it is easy to copy paste the formulas from the cells above, we are looking to save time by having the sheet do this automatically which also prevents the user from accidentally deleting/modifying the said formulas. I am also looking to save file size which is why I am considering doing this in VB. An example of a formula that I need to move down into the next column is:
=IF(ISERROR(VLOOKUP(C2,Tables!A:B,2,FALSE)),"",VLOOKUP(C2,Tables!A:B,2,FALSE))
I am new to VB but would like to gain some understanding on how this works.
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.
I was wondering if there was a way to create patterns when copying and pasting formulas?
For example, here is what I want to do right now:
=AVERAGE(Sheet1!I2:I650)
=AVERAGE(Sheet1!I2:I649)
=AVERAGE(Sheet1!I2:I648)
=AVERAGE(Sheet1!I2:I647)
=AVERAGE(Sheet1!I2:I646)
=AVERAGE(Sheet1!I2:I645)
=AVERAGE(Sheet1!I2:I644)
=AVERAGE(Sheet1!I2:I643)
=AVERAGE(Sheet1!I2:I642)
=AVERAGE(Sheet1!I2:I641)
=AVERAGE(Sheet1!I2:I640)
In other words, have a pattern in the formula?
Or something like this, but filling out cells horizontally.
=A1/A100
=A1/A99
=A1/A98
=A1/A97
=A1/A96
=A1/A95
etc...
Or:
=A1*A1
=A1*A2
=A1*A4
=A1*A8
=A1*A16
=A1*A32
=A1*A64
etc...
I'm working in one workbook with 2 sheets.
sheet 1 have simple values, for example
cell A1:5
Cell A3: 10
Cell A5:15
Cell A7:20
Cell A9:25
Cell A11:30
Cell A13:35
Cell A15:40
In sheet 2 i have the following formulas:
Sheet 2 Cell A1: =Sheet1!A1+Sheet1!A3
Sheet 2 Cell A2: =Sheet1!A5+Sheet1!A7
I need to respect this sequence when copying these 2 formulas in the same sheet 2 cell A3, however when I do that I get the following formula: =Sheet1!A3+Sheet1!A5. While i need it to sum up A9 and A11. i.e respect the order of the first 2 formulas.
If I have formulas in A1, A5, A19, and A36, highlight them with goto, how do I move them into B1, B5, B19, and B36? Copying and pasting puts them in B1, B2, B3, and B4.
View 9 Replies View Related