Swap Columns In Formulas Around
May 12, 2013
I have column AD where there are something like 2,500+ formulas. I would like to change the columns used in the formulas around. By that I mean the first formula is...
Code:
=SUM(AA6-X6)
...and I would like it to be...
Code:
=SUM(X6-AA6)
Now the thing is, the formulas are not continuous, there are breaks where there is a formula to sum say the previous dozen or so for example, then it skips a couple of rows and I have more formulas and then the sum say for the previous dozen for example and so on.
I just want those cells changed with the particular formulas as above.
View 1 Replies
ADVERTISEMENT
Apr 19, 2007
Swap the data between two columns at a time. Here are multiple columns to be swaped in one worksheet and I have many worksheets to work with.
View 9 Replies
View Related
Aug 10, 2007
I have a huge xls of names setup as Last Name, First Name in a single column.
Any formula or somehow to change the entire column to read First Name Last Name. No comma.
Example -
Smith, Joe
Brown, Barry
-------------------------
Output desired -
Joe Smith
Barry Brown
View 8 Replies
View Related
May 16, 2008
I am attaching a sample sheet as a reference.
in fact the sheet could contain hundreds if not thousands of rows.
Look at column Player 1, the last 3 characters are AoS.
I want to find any rows where Player 1 has those three characters ( AoS ) and swap them with the contents of the Player 2.
BUT if that swap happens then the following swaps must occur as well:
Contents of Column "Side 1" swaps with Contents of Colum "Side 2"
Contents of Column "Player 1 Pts" swaps with Contents of Column "Player 2 Pts"
Ultimately I want this extended so that is the letters AoS appear in any of the following columns ( Player 4, Player 6, Player 8, Player 10)
the following swaps happens:
Contents of Column "Player 3" swaps with Contents of Column "Player 4"
Contents of Column "Side 3" swaps with Contents of Column "Side 4"
Contents of Column "Player 3 Pts" swaps with Contents of Column "Player 4 Pts"
Contents of Column "Player 5" swaps with Contents of Column "Player 6"
Contents of Column "Side 5" swaps with Contents of Column "Side 6"
Contents of Column "Player 5 Pts" swaps with Contents of Column "Player 6 Pts"
View 4 Replies
View Related
Aug 1, 2006
I want to swap values of two cells. can anyone help me on that? Eg. Cell A1
contains the value 20 & cell A2 contains the value 100. I want 100 on A1 &
20 on A2.
View 9 Replies
View Related
Jul 30, 2013
I have let say column A and I have the date and name John 1913 all the way down to John 1940.
But what I need is a way to turn it around so A1 starts with John 1940 down to 1913.? If I drag down it counts up but I want it to count down if that makes sense.?
View 6 Replies
View Related
May 21, 2008
Have 5 columns (A1-E1) with different bond data: (respectively)
Coupon, Maturity, Settlement, Price, Yield (asset swap is F1)
Depending on the type of swap I am performing i want the ASWAP (user-created) function to reference different values within the row.
For example: Swap 1 = settlement, maturity, coupon, price, curve
(curve is a user input single number 1-3)
Swap 2 = settlement, maturity, yield, price, curve (yield in place of coup)
Swap 3 = settlement, maturity, yield, 100, curve (100 for par redemption)
etc. I won't type out all the iterations, not necessary.
The way that I want the ASWAP to recognize which Swap# to use is by inputting a number into a cell beside the Aswap column that I can input 1, 2, 3, etc., and then recalculate to get the desired outcome. Want to keep this a static reference as well as the price at par (each owning their own single cell, not a fill down the columns because there are too many).
So assuming G1 is the Swap# input, and H1 is 100...
How do i write a formula that is capable of reading the input in G1 and outputting the proper swap calc.
My gut feeling was along the lines of nested IF's with the corresponding formula... but i won't mess around in futility on here.
Let me know if this is too confusing without me putting up an example.
View 9 Replies
View Related
Jul 19, 2007
I would like to insert a form control (up and down arrows) into a specfied range of cells in a column - ideally the first column. When the form control up or down arrow of the assigned row is clicked it moves that row up or down essentially swapping positions with the row directly above or below. I've seen this functionality in custom excel applications but my searches for code on this topic have been fruitless.
View 5 Replies
View Related
Nov 29, 2007
Right now I have 2 ranges say A and B. Range A is in a worksheet on row 7 and range B is on the same worksheet row 8. What I want to do is make it so that range B gets pasted in for range A and vice versa so that range A in now in row 8 and range B is in row 7.
View 2 Replies
View Related
Jan 31, 2013
Swapping the contents of 2 rows, my code does the operation but it is very slow. Here is my code
Sub swap()
Dim aTemp() As Variant
Dim swap As Boolean
swap = False
Dim row As Integer
row = 19
[Code] .......
View 2 Replies
View Related
Aug 8, 2008
I am trying to chart a range of dates, for example. I have some boats that are on contract between dates a and b. I would liketo plot several boats with their contract date range. I have tried using a bar chart, but all the lines start at 0. I got it working using an Excel 2007 Stock chart, however, it is in the wrong orientation. I would like to Rotate the Chart 90 degrees, swap the axis, or find a better method all together. I cannot post the spreadsheet as its confidential.
View 2 Replies
View Related
Dec 4, 2012
My pivot table shows Towns as the Row headings and Month as the column headings. When I do a Chart, I get Towns on the X axis and Months as Y. I want the chart to show all my May data in a stacked column, then all my June data in another stacked column, etc. If I pick "Select Source Data" the options are greyed out. What's a mother to do?
View 1 Replies
View Related
Sep 11, 2007
I have an interactive chart displaying stacked area charts for 2006 and 2007
I currently have 2006 in front of 2007, but as the user chooses 1 of 20 units, it will look better to move 2007 in front of 2006
View 4 Replies
View Related
Feb 12, 2014
I am using the code below to "swap" the selected cell values in a column with the ones to the right of the selection. It works fine on unfiltered sheet, but if I apply an Autofilter, it copies both visible and hidden cells, and I only want to swap visible cells. What to modify?
[Code] .....
View 6 Replies
View Related
Jan 15, 2008
Is it possible to apply a formula with a relative reference to an entire column? I have a very long, constantly updated datasheet for pivot tables and this would be a most helpful thing to know.
View 9 Replies
View Related
Apr 11, 2009
I have data from F1 to AG1...I am unable to create a formulae & drag in excel in a column like
=F1
=G1
=H1.....If i drag after H1...it is reflecting as
=F4
=G4
=H4.
View 3 Replies
View Related
Nov 6, 2008
I would now like to as remove the first column and add another column a and b. A would say "Service From Date" and column B would say "Service To Date" In addition I would like to add a column between "5202 Meals Provided" and "5221 S.A. Facility Lodging" a column that reads "7025 Number of Days Care" and that tallies like the rest of them.
View 14 Replies
View Related
Dec 9, 2008
I need to editing a VBA script and can't assist right now. Here is the VBA script as it exists today:
View 3 Replies
View Related
Apr 6, 2012
I'm working on a project that spans three sheets. A2:A151 of Sheet 1 and 2 is a list of names. The names may repeat and they probably won't fill all 150 rows. What I'd like to do with formulas is combine Sheet 1 and 2 into a single list in Column A of Sheet 3 without repeating names or showing blanks. I'm fine with multiple formulas and hiding columns, whatever needs to be done to make it work. Is this possible?
View 2 Replies
View Related
Dec 3, 2013
I have 2 sheets in my excel document. The first sheet (sheet1) has student data (grade they got on a test) from C5:C35 all in 1 column. On the 'second' sheet (sheet3), I have made a profile for each student (there are 30 of them, spread out 1 per printable page). I want to take Sheet1!C5 result and put it in sheet3!D7. The formula in sheet3!D7 would just be =sheet1!C5. I want to 'drag' that formula to L7, then to T7 then to AB7 etc etc (they are all 8 columns apart), obviously not affecting any of the cells between them.
There are 12 pieces of data in sheet1 that I want to auto update in sheet3, for 30 students, so copy pasting, and adjusting the formula would take a long time.
View 7 Replies
View Related
Jan 21, 2014
I need to 'fill down' a series of formulas in several columns - but the number of rows I need to fill down will vary dependent on my raw data.
See attached file.
Worksheet 1 - contains the raw data which is copied and pasted in from an external source. In this example, there are 20 records.
Worksheet 2 - brings in the raw data from worksheet 1 and does some calculations. The formulas are in the first 30 rows - in case the number of records grow. But what happens if the number of records is, say, 40?
I have a much bigger workbook in reality - so having too many formulas will increase the file size.
Simple example.xlsx
View 3 Replies
View Related
Apr 4, 2014
i have a data entry tab where we put names and button to sort and hide blank rows from other tabs. Problem i want to move down the number entered also. something like extend sorting but not affecting the formula on the other columns.
View 2 Replies
View Related
Jun 4, 2014
in my workbook: in sheet1 i have many cells with formulas which calculates values from sheet2. I also have a macro which is used often that will arrange and prepare data in sheet2, including adding some columns.
The problem is that after these columns are added, the formulas in sheet1 will be changed, even if I used f4 ($) in the formulas.
how can I freeze these formulas? I think a solution is to include the formulas in sheet1 in the macro I'm running for sheet2. But I would like to avoid this.
View 2 Replies
View Related
Feb 7, 2014
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.
View 1 Replies
View Related
Aug 13, 2009
I currently have formulas in a column setup (equations are not actual)...
Cell B1: =IF(A2>A3,"Y","")
Cell C1: =IF(B2>B3,''Y",''")
Cell D1: =IF(C2>C3,"Y","")
....
I want to switch these to one long row from one long column so that they span from Cell A1, A2, etc.
When I special paste or straight paste it obviously alters the formulas because I am also unable to cell lock the current formulas.
I am wondering if there is an easy way to convert my formulas, currently along a column, to a row? Or if there is also a way to cell lock a large amount of cells at once?
View 9 Replies
View Related
Dec 21, 2009
I have some mails in a colum and i would like to put with a formula into a cell.
For example, in column I have:
mail1@hotmail.com
mail2@hotmail.com
mail3@hotmail.com
mail4@hotmail.com
mail5@hotmail.com
mail6@hotmail.com
And into anything cell i would like to put with the coma:
View 9 Replies
View Related
May 13, 2014
I need to list only the unique values in a range found in multiple columns. Some cells are blank. I need to list all the unique values in numeric and/or alphanumeric order in another column within the same worksheet. I would prefer non-array formulas, if possible. See the attached file.
In the attached table, I have 5 columns and 5 rows of values in the range B2:F6. The unique values, in order, are 0, 1, 2, 3, 4, 5. This looks simple and can be done manually if the file is small but I have 150 columns and 150 rows, thus the need for automation.
List of unique values in multiple columns 2.xlsx
View 4 Replies
View Related
Nov 3, 2013
I was wondering if there was an easy way to copy columns (headings) and the formulas to a new sheet. I'm currently working on Sheet 1, need to copy everything onto a new sheet so I can keep things separated by week..
Example, Sheet 1 is Week October 30th, would like my 2nd sheet to have same exact headings and formulas which I would then rename to Week Nov. 1st etc..
Also, is there a way that I can then copy the 4 sheets that would make up a month into a new spreadsheet so I can then start December...
View 5 Replies
View Related
Nov 10, 2011
I need some VBA code that puts in formulas to pull data in various columns within row 2 of another worksheet.
The formulas need to be entered below some other data, which is populated from another prior macro.
The below code works well but as the prior macro can populate a different number of rows on different days, the Row count then also changes and doesn't always refer to row 2 of 'HLDRT before' tab.
ActiveCell.FormulaR1C1 = _
"=IF('HLDRT before'!R[-22]C[-18] = ""A17"",RIGHT('HLDRT before'!R-20C[14],3)&'HLDRT before'!R-20C[9]&'HLDRT before'!R-20]C[-13],"""")"
Is there a way I can tell the above code to always use row 2 from the 'HLDRT before' sheet, while keeping the column number lookups the same?
I think there is a way to do it by removing the [] signs but I can't get it to work. I also then need the macro to copy these down so I'm not sure if using $ will cause problems?
View 4 Replies
View Related
Dec 13, 2011
Writing a macro to insert two columns with IF formulas into a Worksheet. The 2 columns to be added would be after Column G on Sheet 1.
The new Column H would be
"=IF(G1>=Q#,G#/Q#,0)"
And the new Column I would be
"=IF(G#>=Q#,MOD(G#,Q#),G#)",
Where # represents the row number.
View 3 Replies
View Related
