I'm trying to copy formula on the X axis and past/transpose to a Y axis.
When I do this, the cells say #REF! If I use $ it works, but it is very timely to manually add the $. Is there a better/faster way to do this?
Formulas in H25:BF25 to C19:C69
I am currently working on a data analysis project (data mining) and need to collect and later analyze statistics for the inputs which control a series of calculations. These statistics are shown in the Statistics 1, Statistics 2 and Statistics 3 cells in the workbook that I attached. The inputs are X,Y; all possible values for these inputs are listed in the N,O columns. Basically I need a macro which would take the values from these two columns and place them pair after pair into the controlling cells (K3, L3), then it would copy cells H2 through L3 (updated stats) to a new sheet after each copy operation - so that I will finally have a list of statistics for all of the input pairs.
View 3 Replies View RelatedI need the macro to look at cells B9:B84 on the Sheet1 tab of the Cost Template. If it finds an x I need it to copy the 3 cells to the right of the x and paste them in a template. For example if it sees an x in cell B9 it would copy cells C9, D9 and E9, open the Purchase Order to the Detail tab, then paste it to cells B3, C3 and D3. It would continue looking for an x down to B84. So if it found 5 cells with x, it would give me 5 instances of the Purchase Order with 3 cells pasted into each.
I've attached my Cost Template and the Purchase Order it needs to copy to. In the Cost Template is a macro called Create_PO. This is what I was trying to alter to make this happen. I can't seem to get it right! FYI in case it matters, I had to change the Cost Template from .xltm to .xlsm in order to upload it on this site.
I have a macro that inserts a formula on the first row and then copies it down. The problem is the row count is variable but I know it won't exceed 5000 so I just copy it down 5000. For the blank rows I get the ugly #NAME, #VALUE, etc errors.
I know there are ways to supress those error messages, but how about a way so that the macro checks to see how many rows are populated so it doesn't copy down on the blank rows.
I would like to copy a formula in a cell and then paste only the text of the formula, but I can't figure it out. Basically, I would like to avoid going into the cells and absolute referencing or hitting F2, then copying the text.
When I hit "Ctrl C" to copy the cell, then hit "Alt/E/S/F/Enter" to paste the formula, it is just like a regular copy/paste formula-wise in that the references move.
I use a macro to fill data from different files (in the same folder) to a master sheet. One particular cell (C16) of the files contains a formula for calculating Young's modulus (E) based on temperature (entered in cell C14). The same cell in all files has the same formula but since the calculations are based on temperature the value changes. In once case the formula is like:
View 2 Replies View RelatedI have searched and unable to find how to keep a formula as a formula in a cell so I can copy it to other cells. Every time I enter a formula, example: =if(a2>0,trim(v2)&"."&trim(x2),"") the cell immediately displays the value and I am unable to copy the formula to other cells because I get the same value all the way down the sheet. Example star.plus, star.plus, star.plus... Every cell in the range should have a different value and change whenever the cells in column v or x change. I also tried entering this formula on another workbook which displays correctly the formula in a cell and then copying and attempting to paste onto the existing worksheet but the past and paste special are greyed out.
View 3 Replies View RelatedI am doing the aged old problem of copying data from one sheet to the next vailable empty column on another sheet, this is done and is working fine. I need to adjust a single line of code so that it copies the value of the cell rather than the formula (so the same as paste special, value).
The whole code is:
I am using the following IF statement to guide users to complete columns B, D and E so that column R auto-completes:
=IF(OR($B$8="", $D$8="", $E$8=""), "You must enter a CI Name, Tag Number and Serial Number before completing this field","Hardware")
I have tried copying the formula down and using Paste Special but I still get references to the original rows (i.e. Row 8). I have other formulas in the sheet and have tested them in coloumn R and I am able to get them to correspond to the relevant row (e.g Row 26, 27 etc for: =IF(ISBLANK(U26),"",VLOOKUP(U26,ModelManu,2,0)). Have checked cell validation is off, data type etc but nothing seems amiss. Any ideas if it's formula related but this doesn't seem to be a logical root cause..?
Sheet 1 has a lot of data on it with hundreds of rows with a total at the bottom.
Columns are months for the next 5 years and the rows are the store names. At the very bottom is the grand total for the month. Each of the totals are named cells. Example Refund1, Refund2, etc
On another sheet I have a summary that I want to pull in the grand totals. So for the first month the cell has "=refunds1". I need to do that for all 60 months. I would normally just copy the formula across but when I do that the formula stays "Refunds1" in every cell.
Is there a way to copy what is in cell a1 "refunds1" all the way across while having the name change? Meaning cell a2 would be "refunds2"
I have put below a starting point for a macro that I am trying to complete. It does do what I want but I think that it is very inefficient as it is going down line by line. Is there a way to copy this down instead of visiting each cell?
View 5 Replies View RelatedI have created a timesheet in Excel, with meal breaks, end times and total hours worked for a two week period. The formula =SUM(INT(X10)+Z10) calculates correctely in day 1 of week 1 and through the first week. When I copy the formula to week 2, it does not calculate. I have even entered the formula again, but it does not work. I even tried putting it on a new sheet. I also tried doing a paste special. I need help to complete this timesheet.
View 12 Replies View RelatedI am writing code where I will click the macro and depending on the number of days I wish to be calculated, it will go to various different sheets, calculate various different figures and bring that result back to my main sheet.
Everything is great except for when it copies the specified range, it is copying all the data within that range, whereas, I just want the sumtotal of the data (so the sum value that appears in the bottom right hand corner of the screen). An example of my code is listed below:
Worksheets("A13").Range("B27:E27").Copy
Worksheets("Weekly M2M").Range("B24").PasteSpecial xlPasteValues
there are 4 columns PC5,Product Code, Sample and I need a formula in the options column...
PC5Product CodeSAMPLEOptions
6361263612-003720
6361263612-00380
6361263612-00410
U6002U6002-00120
U6002U6002-00130
U6002U6002-001620
U6002U6002-00170
In the table above Under the options column , Wherever there is 20 under the samples column, In the options Column,I want all the other Product codes with the same PC5 ....meaning...I need the below output.
PC5Product CodeSAMPLEOptions
6361263612-00372063612-0038,63612-0041
6361263612-003800
6361263612-004100
U6002U6002-001200
U6002U6002-001300
U6002U6002-001620U6002-0012,U6002-0013,U6002-0017
U6002U6002-001700
I have a extemly large spreadsheet which i have to pull certain data from i have left a small example below;
AccountingEvent-GenericRADIUS@TueNov1111:36:00Code=Accounting_RequestAttrb:User_NameValue:unauthenticatedAttrb:NAS_IP_Address
Now the Attrb: and Value: cells continue all the way down for around 40/50 cells but the only information i require from this data is the Row which starts with Accounting and the Value which in this case is set to Unauthenticated. Now the information in this cell is not contstant and changes at every entry.
I am looking for a formula which will scan the spread sheet and just pull the information described above.
I play with 12 lots (rows) and these refer to another tab sheet where the result of lotery numbers are.
So the first draw refers to Tab Results $B3 (=number.if('Lotto numbers'!$B$3:$G$3;Draw!B$3).
Now I want for Draw 2 to copy the formula that Draw!$B3 becomes $B4.
If i only needed to copy this 1 row below it is not a problem but I need to copy the formula 12 rows below !
Draw 1:
Row 1: =number.if('Lotto numbers'!$B$3:$G$3;DRAW!B$3)
Row 2: =number.if('Lotto numbers'!$B$4:$G$4;DRAW!B$3)
Copied 12 rows below should become:
Row 12: =number.if('Lotto numbers'!$B$3:$G$3;DRAW!B$4)
Row 13: =number.if('Lotto numbers'!$B$4:$G$4;DRAW!B$4)
I want to link the sums from one sheet to another.
In the first sheet the sums are in the rows. I want to link them so when i copy my formulas through columns in the first sheet it refers to the rows in second sheet.
I've been trying this on cell L6, ='sheet2'!T31
Now I want cell M6 to be ='sheet2'!T32. But when i copy, it becomes U31.
I see the logic in why my formula dosent work.
See the attached workbook if needed
Column G
From G2:G102 has a number in it.
This number is an average of column D:F same respective row +2
What I am looking to do is if the value in H2:H102 is equal to or greater than the number in G2:G102 same respective row go bold green.
So I did a simple CF for H2 to look at G2 and that works fine but when I copy and paste format all the way done to H102. It is comparing all the values in H3:H102 to G2 instead of the respective row H3 to G3, H4 to G4, H5 to G5 you get the idea.
I don't want to do a CF on each row in column H because it will take forever.
What is the simplest way to do this.
I think it is just about the formatting of the formula?
I would like to do the following in a copied down row where n5 is a cell that contains a number that is added to a row number in order for the range to maintain n5 rows when copied down.
=average(b1:b1+n5)
In DB2 I have = DB1. How can I drag this down and for each row I go down, I want to reference one more cell to the left of DB1, so in DB3, I would have DA1, and so on.
View 2 Replies View RelatedI have a column with many different numbers and varying amount of digits in the cell it says 9.85, or 35.09 but in the formula bar it is not rounded and I need to copy these numbers to a csv and when I copy it the row to the CSV it still has the non-rounded totals in the formula bar, I have tried protecting the sheet and tried to paste special but neither seem to work how would I go about just copying exactly what is in the cell to another?
View 14 Replies View RelatedFor all worksheets in the activeworkbook,
i want to automatically copy a formula (=INDEX(KEBSIDF20080925_26!$A$3:$A$10000,MATCH(A4,KEBSIDF20080925_26!$A$3:$A$10000,),1)
to a target cell (D4). Each worksheet has the same headers.
Anyone see any problems with this code? I used this in another application and it seemed to work just fine? However now it isn't working I just changed the reference area's and changed the formula.
View 4 Replies View RelatedI have a supply worksheet that is set up where the new data is added into a new column, so that the report extends horizontally. All of the pricing is in Column I, quantities are listed in the new column, and then totals at the bottom with the following formula:
=(SUM(AF7:AF10)*I7)+(AF12*I12)+(AF13*I13)+(SUM(AF15:AF18)*I15)+(SUM(AF20:AF23)*I20)
When I copy and paste the cell, into the next column for a new order, it changes the quantity column from "AF" to "AG", which I want it to do, but it also changes the pricing column from "I" to "J". I then have to go in manually and change all the J's to I's. When I copy the just the formula, it keeps everything the same, and then I have to manually change "AF" to "AG".
Is there a way to copy and paste so that the pricing column stays fixed within the formula, but the quantity column changes?
Trying to use a standard formula based on a variable workbook name. The INDIRECT solution suggested worked fine until I realised that I'd need to have the additional workbooks open in order to return the results.
Variable Workbook name in formula?
The reason I wanted to use a standard formula was so that I could use a piece of code I've written that would simply copy the formula from one cell and paste it accross and down for all cells within a range of rows and columns. Seeing as the formula now needs to be unique per column I need to change the code to copy the entire row of formula down instead of the one formula down and across. Here's my original code that copies a formula from B2 and pastes it down to the last row and across to the last column:
Code:
Sub CopyFormulaDown()
Dim lngLastrow As Long
Dim rngTargetStart As Range
Dim rngTargetEnd As Range
lngLastrow = Sheet3.Range("A65526").End(xlUp).Cells(1, 1).Row
[Code] .....
What I assume I need is to change the Range("B2") in the last line to Range("B2:???"), where ??? is the last column eg AA2. I'm not sure how to do this dynamically though.
If I have a formula that I want to copy throughout a column, but I want to keep one of the parts of the formula constant, is there a short way to do this?
For example:
First cell is A1*O24
Second Cell is A2*O24
Third Cell is A3*O24
Right now when I try to copy the formulas, the second cell always reverts to A2*O25, which is not correct.
I have a workbook containing 101 sheets on the first sheet is a list of numbers in H1 to H100. Sheet2 will have the value in Sheet1 H1 in cell G3 then Sheet2 will have the value in Sheet1 H2 in cell G3 and so on for the 100 sheets Sheet101 will have the value in Sheet1 in cell G101 in cell G3 how do I automate this task. This would be easy on a small number of sheets to enter manualy but I dont fancy entering into 100.
View 7 Replies View RelatedI've been using this macro
Sub FillDownFormula()
Dim LR As Long
LR = Range("J" & Rows.Count).End(xlUp).Row
Range("A1").AutoFill Destination:=Range("A1:A" & LR)
End Sub
to copy down a formula down column A where the number of rows varies from file to file. The problem that I'm having is that when I run it the cells come up with the #VALUE! error. When I click on the cells the correct formula displays in the formula bar and if I double click on the cells and hit enter then it works. I thought that maybe the problem was that the formula in A1 has text (column header) and rest of A has numbers but that didn't seem to be the issue.
I am trying to copy formula as follows:
2
3
1
10
=A4*B4*D2
=A4*B4*D3
[Code] ......
I'm trying to copy the formula both down and across but I'm guessing both issues are the same.
I'm trying to copy an Excel formula as value with the code below, but VBA is only copying the formula
Code:
Cells(1, 3).Value = cells(2,3).Formula
So, if for example, the formula is =A1+B2, I want A1+B2 (without the equal sign) to be copied to the other cell.However, what I'm getting is =A1+B2 (so a copy of the formula).
Its important to highlight that
Code:
Cells(1,3).Value = Cell(2,3).value
will give me the result of =A1+B2, which is not what I want.
