Pasting Formulas In A Formatted Spreatsheet
Dec 15, 2009
I have been asked by my manager to make new shelf labels for all our stationery products for the VAT change in the new year. I'm sure I can get excel to help me make the task quicker but i'm just having trouble with pasting the formulas into the label template that I have to use, I was wondering if anyone could tell me why and if it is possible to do with the way I set the document out. Attached is what I have so far.
if there is a simpler way of doing this. The data sheet is the item description and the old price. The labels sheet i need to be able to print off in that format so i can cut it up and use the labels on our shelves. The formula for the VAT change is on the labels sheet, it works on the old price on the data sheet. I want to be able to quickly apply the formulas and format of the labels to more cells in the sheet so i can print off multiple pages of labels.
I have tried just copying the current formatted cells (A2 to C29) which I have seperatley typed the formulas into (takes ages) and pasting them beneath that. It doesnt continue the formula though. Instead of the next label cells formula carrying on and being Data!A43 it become Data!A29. Perhaps what I want to do is not even possible!
View 2 Replies
ADVERTISEMENT
Jul 10, 2014
So, I have a document 25 pages long. It has 26 sections (1-26) and each section has up to 10 (.1 - .10) sub sections which contain 16 (a-p) sub-sub-sections. There are 6 columns of varying widths. Some cells, in a row, merge into adjacent cells.
I would like to break this document into 26 documents - one for each section - so that I may recombine and regroup them in the future incorporating a few modifications. Whenever I copy and paste to a new Workbook, I loose the formatting of column widths. Resetting these alone takes lots of time.
I have come up with is to duplicate the entire document and delete everything above and below the section I would like to save. Then repeat for next section. There's got to be a better way?
View 10 Replies
View Related
Feb 7, 2012
I am trying to display a formatted formula (combination of greek characters, superscripts, subscripts, bold, italics, etc...) based on a selected input. I have a selection cell with a drop down offering the choice of 4 difference scenarios. I want to display the resulting formatted formula depending on which scenario is selected (the formula is difference depending on the selection, as well as perform the calculation.
The calculation works fine, but I cannot figure out how to show the formatted formula. The selection works using IF statements, or VLOOKUP, but does not bring the formatting across. How can I do this?
View 1 Replies
View Related
Dec 28, 2009
I would like to do a mass paste of this formula: =VLOOKUP(A23,A2:C9, 2)
With the only variable that changes along the way (A24, then A25, etc). But as I paste, (A2:C9), the table array, keeps changing as well, to A3:C10, A4:C11, etc.
How can I prevent this and only have A23 change as I paste?
View 2 Replies
View Related
Feb 12, 2010
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 Related
Dec 17, 2011
If I have a formula on Sheet2 which creates conclusions depending on data already existing on Sheet1 and then I need to paste the same formula on Sheet4 in order to get conclusions from Sheet3, then what can I do? Every time I am trying to paste the formula, it connects it with Sheet1 instead of Sheet3
In other words
Sheet1: Data
Sheet2: Extracted conclusions from Sheet1 data based on a formula
Sheet3: Data similarly organized as in Sheet1
Sheet4: How do I extract the same conclusions, but this time from Sheet3?
The formula on Sheet2 is:
=INDEX(Sheet1!$E$312:$E$5000;((COLUMNS($A4:B4)-1)*20)+1)
I would like to copy this formula from Sheet2, then paste it to Sheet4 and then to have it appearing as
=INDEX(Sheet3!$E$312:$E$5000;((COLUMNS($A4:B4)-1)*20)+1)
Is this possible?
View 7 Replies
View Related
Jul 10, 2014
I am trying to copy data from an employee worksheet into a database that tracks the history of that data when they click submit. Everything is working well, except I can't figure out how to change the code to copy and paste values instead of the formulas.
View 2 Replies
View Related
Jul 14, 2014
I've set up a spreadsheet structure at work which is three workbooks linked together.
I created this initially within My documents saved locally to my machine. Now though, I need to move the folder containing these workbooks onto the server to be accessed by others. My problem is that when cutting and pasting the folder, the formulas within the three spreadsheets still refer to their original location within My Documents rather than following the folder to their new location.
View 4 Replies
View Related
Jan 24, 2012
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...
View 1 Replies
View Related
Jul 19, 2006
I need to paste the format and formulas of the last row of data into a new row beneath it. I've seen a bunch of different end row functions, but which one is best and how do I only paste the formulas and formatting?
View 8 Replies
View Related
Nov 8, 2013
I recently received an .xls book which I then saved as .xlsx (I'm using 2010). There are just under 8,000 rows and 20 columns. File Size 1MB.
The only formulas in the sheet are the ones in a column which I inserted and copied down for all 8,000 rows. Nothing too complicated: no arrays or anything. The sheet calculates fine.
I am simply trying to copy and paste these formulas as values (into the same cells), though at every attempt Excel crashes. I tried on smaller sets of the column and just got it to work for a few hundred rows, though it struggles with any more than that.
I opened a different workbook of mine, and tried the same operation on twice as many cells containing complicated, lengthy array formulas and the action completed instantly.
There is no Conditional Formatting in the book, no code, no 'last cell' issue, no Named Ranges, no external links.
I have even copied the data to a new workbook, then copied the text of just one of the formulas over into this book, added an equals sign, copied down and recalculated, then tried to paste as values again. Still crashes.
Formula:
=IF(AND(N3>1,ROWS($1:1)<>MATCH(M3,$M$3:$M$7979,0)),"Exact Duplicate","")
is far more resource-hungry than I thought, though if that were the case, wouldn't the issue be during calculation (which, as I said, is fine) and not during a paste attempt? No, it can't be this.
View 1 Replies
View Related
Feb 24, 2009
I'm trying to copy and paste this formula to multiple cells and am having difficulty doing so without everything in the formula changing.. SUMIF(Bankroll!Q14:Q6000,U143,Bankroll!Y14:Y6000)
I only want the U143 to change to U144, U145, etc. Yet when I copy and paste down the sheet it changes the Q14:Q6000 and the Y14:Y6000 values as well. I've been having to go through and paste the formula one line at a time and then manually change the U143 to the current U cell that I want it to represent in order to keep the rest of the values the same. Is there a faster way than this?
View 2 Replies
View Related
Feb 6, 2014
I have code that "mostly" works great. I'm copying columns from one sheet to another sheet, but in different columns. I have unique code for each copy/paste. The source is mostly raw data. However, there are a few columns that are formula-based, and I'm having a problem. I've attached my code, and it works, but it takes 20 minutes to complete.
I'm very new to macros, but I think(?) I know enough that a data source with 2,000 rows and 30 columns shouldn't take 20 minutes to complete the macro calculations. All of the columns (copy and paste) in the code are pure data. The only exception is the column labeled "AI". Is there are shortcut, other than creating new columns in the source data sheet and pasting these results as values? I put the specific pieces of code that I'm referencing in bold.
View 3 Replies
View Related
Feb 9, 2010
For simplicity sake if I wanted to paste the letter A in to a cell that already had the letter B in it to make the cell read AB how could I achieve this?
View 10 Replies
View Related
Dec 3, 2013
I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:
=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292
What I want to do is extract the file path from the above formula and make it a composite of several cell references.
So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:
_MONTH =11 November
_YEAR =2013
_JOBCARD ='S:PUBLICProductionJob CardsMOLDING
_PATH =_JOBCARD & _YEAR &"" &_MONTH
I tried several versions, I am hoping for something like this:
=('_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$228*2)+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$262+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$292
View 4 Replies
View Related
Dec 15, 2008
I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.
View 9 Replies
View Related
Aug 6, 2012
I have a strange issue. I have column "B" has date texts in it ( don't know if it means value) like 21.09.2012. The cells are in text format.
VB:
Sub ReFormatCell()
Range("B:B").Select
Selection.NumberFormat = "dd.mm.yyyy ddd"
With Range("B:B")
.Formula = .Value
End With
I tried ".Value = .Value" or ".Formula = .Value" or Copy paste or offset...... Non of the codes didn't work to make date format as I desired :/
The order is something like:
1. Format column "B" as text.
2. Offset text value 21.09.2012 to B.
3. Reformat cell as date
4......?!?!
What should I change to display "21.09.2012" as "21.09.2012 Wed" in B:B range?
View 9 Replies
View Related
Feb 13, 2007
I'm working with a line of code I can't seem to get right. I've got this string that will put breaks in, but I've realized it would make my life easier if the code, along with inserting a break, then inserted a formatted gray divider, it would make it easier. Here's what I have:
Sub BreakSections()
'This macro breaks the new data on sheets into their individual groups, inserting a blank line_
'which will need to be filled with a gray dividing line
Dim i As Long, j As Long
Application. ScreenUpdating = False
With Sheets("Contract Manufacturers")
. Cells.UnMerge
i = .UsedRange.Rows.Count
End With
For j = i To 2 Step -1
If Not IsEmpty(Cells(j, 5)) And Cells(j + 1, 5) <> Cells(j, 5) Then Rows(j + 1).Insert
Next
Application.ScreenUpdating = True
End Sub
I've tried then to put this code in after the Insert:
Range(ActiveCell, Range("AJ" & ActiveCell.Row)).Select
and then the formatting code.....
But I end up getting a whole mess of gray rows, displacing a lot of data. Is it possible to do these things together, or I should write another macro that just looks for the blank spots? It seems like it would be easier to do it all at once.
View 4 Replies
View Related
Dec 11, 2013
I'm trying to automate creating certain keyword combinations I need, based off of the values I input into reference cells in columns A - E; the goal is to compile a list of keywords which I will then use to track my rankings in search engines.
I'm looking to only output 500 keywords, so some of the cells in columns A, B, C & E will not contain data (column D will always have a primary Geo-target listed). This results in some of the concatenate formulas I've created outputting partial data (i.e. if there is no data in cell A10, and cell D2 contains the word "Knoxville", then cell I10 will output the data, "Knoxville "). How can I setup conditioning formatting or a formula so that these auto-generated cells appear blank if one of the reference cells has no data within it?The reason why I need the above to work is because I want to setup a formula that automatically counts the # of keyword combinations created by the data entered into any of the reference cells. With the partial combinations being listed, it skews my data. Which leads me to my next question: what is the best formula for counting the # of cells containing a full keyword combination from any of the cells listed in columns G - O (minus the data in the header cells; i.e. G1, H1, etc...)?Lastly, is there a formula I could use that would then aggregate all of the full keyword combinations within the "Complete Keyword List" column (column P)?
View 11 Replies
View Related
Jan 10, 2013
I am running Excel 2007 on Windows Vista Business 32 bit. Recently I have noticed that if I enter a formula into an empty, unsused cell, it is recognized as a formula. If I modify that formula, it is then recognized as text and does not work as a formula. The only way I can get the cell to recognize a formula is to delete the cell and start over. This same scenario does not occur on previously stored workbooks. I have checked all of the flags that I know about, including the Options function.
View 3 Replies
View Related
May 13, 2008
I have a macro running this code to strip out unwanted formulas and formatting.
Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False
Range("CDandC").ClearContents
Range("qdata5,qdata6").Font.ColorIndex = 2
'To delete delivery address lines if 1st line empty
If IsEmpty(Range("deliver_line1")) _
Then Sheets(1).Range("deliver_rows").EntireRow.Delete
'No End If required as only one action as a result of the If
Range("Item_Nos").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("A:E") = Columns("A:E").Value .........................
A spreadsheet based on my template has been sent to me because the macro won't run properly. When I try to run the macro I get a Runtime Error '1004' Method 'Range' of object '_Global' failed on the following line. Columns("A:E") = Columns("A:E").Value.
View 4 Replies
View Related
Jan 9, 2009
I have Excel 2008 for Mac (vers 12.0). On a basic spreadsheet, the first column is for date which I have formatted so I can type in 15/8/9 and it appears as 15-Aug-09.
However, each time I open Excel after having Quit it, I have to reformat the cells in this column again as they revert to converting my date input with something like 39,123.
My other columns are formatted to currency and are always okay.
View 10 Replies
View Related
Mar 2, 2009
I'm trying to copy about 10,000 barcode over to a new spreadsheet, the prob is that when I copy them over they appear as XXXX+12 and then when I try and use them it doesn't like it. I can solve it if I format the column to text - and then double click it. This is ok for a hundred or so but fingers start to hurt after that!
View 14 Replies
View Related
Nov 3, 2009
I have a cell that calculates an etch time, eg 41.88. The cell is formatted to give me whole numbers and quarters only so that the operators input is made easier, in this case 42.
When I use a macro to copy this to another worksheet, although it appears as 42, the actual cell data is 41.88. Anyway I can get the actual cell data to be 42?
View 2 Replies
View Related
Mar 30, 2012
I have written below code
Sheet1.Cells(1,2)=Shett2.Cells(2,1)
and my sheet1 cell has a value which is bolded and after running above code the sheet2 cell is displaying unbolded value
how to copy the format as well
View 4 Replies
View Related
Dec 8, 2012
Is there an excel formula to count the formatted cells for each row?
Here an example;
Sheet2
A
B
C
D
E
F
G
H
I
J
K
1
EXAMPLE
COUNT
2
01 05 31 34
10 18 21 28
23 26 33 36
06 11 37 39
15 19 25 30
16 17 35 38
02 03 08 09
04 12 27 29
13 14 24 40
07 20 22 32
3
[Code] .........
View 4 Replies
View Related
Sep 6, 2013
I have a project where if the user enters 'R' into cell H17 then cells B19:K19 are to be filled with black color. These cells are divided into three fields: B19:E19, F19:H19, and I19:K19.
When I select these three fields and use the conditional formatting tool the only cells that are black are B19:E19. Even though the rule explicitly shows that cells B19:K19 are to be affected. In order to have all three fields black I have to make three rules (one for each field).
The same behavior happens when I use the conditional formatting tool and select the cells from within the tool's dialog.
How can I get all three of the fields formatted with one rule?
View 2 Replies
View Related
Oct 24, 2006
I have a cell that I've formatted to dd/mmm/yy, It used to work, but now it doesn't. When I type in 1/2/03, the formatted cell shows 0-Jan-00, The formula bar shows =1/1/2. I seem to have a formula in the cell, but I can't get rid of it. I know it's most probably somethinmg I've done , but I don't know what. I've tried everything I know. I can't seem to re format the cell , and I can't delete the formatting in it. I'm stuffed.
View 9 Replies
View Related
Nov 16, 2006
this code works fine on my desktop but on 2 of work's pc's it produces a error at the msgbox line
Private Sub Workbook_open()
'http://www.ozgrid.com/forum/showthread.php?p=306542&posted=1#post306542
Dim dDate As Date
dDate = Sheet16. Range("a1").Value
MsgBox "Today is the " & Format(dDate,"dd/mm/yy"), vbOKOnly
End Sub
i have changed the security settings in macro's and one of them was using office 2000, not sure about the 2nd one but i will check next time iam at the office and grab the error codes.
View 2 Replies
View Related
Jan 10, 2007
Is it possible to sum cells that contain numbers converted to text? I used TEXT function to convert them because I needed to format them (to display three decimal digits, if number < than 0.05, three digits otherwise). But now SUM Formula doesn't work.
View 4 Replies
View Related
