# Copy A Formula With A Name In It

Oct 22, 2009
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"

View 3 Replies
ADVERTISEMENT
Jun 24, 2008

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 Related
May 1, 2014

I 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.

View 11 Replies
View Related
Feb 18, 2008

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.

View 9 Replies
View Related
Jul 16, 2007

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.

View 10 Replies
View Related
Nov 6, 2008

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 Related
May 22, 2014

I 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 Related
Feb 23, 2009

I 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:

View 2 Replies
View Related
Sep 8, 2009

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..?

View 3 Replies
View Related
Sep 14, 2009

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

View 4 Replies
View Related
Aug 18, 2009

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 Related
Sep 7, 2007

I 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 Related
Jul 24, 2013

I 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

View 3 Replies
View Related
Mar 12, 2009

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

View 9 Replies
View Related
Apr 22, 2009

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.

View 9 Replies
View Related
Apr 8, 2006

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)

View 9 Replies
View Related
Aug 21, 2012

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.

View 3 Replies
View Related
May 19, 2014

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?

View 2 Replies
View Related
Jun 18, 2014

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)

View 11 Replies
View Related
Mar 25, 2014

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 Related
Dec 29, 2007

I 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 Related
Dec 11, 2008

For 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.

View 4 Replies
View Related
Apr 23, 2009

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 Related
Sep 4, 2008

I 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?

View 2 Replies
View Related
Sep 27, 2011

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.

View 2 Replies
View Related
Oct 6, 2011

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.

View 2 Replies
View Related
Apr 2, 2012

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 Related
Jul 27, 2012

I'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.

View 4 Replies
View Related
Apr 25, 2013

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.

View 3 Replies
View Related
Jul 14, 2013

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.

View 4 Replies
View Related