Impacts Of Sorting On Formulas
Dec 21, 2009
I have just noticed the following condition and wanted to confirm that my observation is accurate and find out if there is any way to work around.
I have three columns of data:
ItemId Description Qty
1 cans 10
2 bottles 0
3 boxes 0
If I have a formula that multiplies the number of cans by 3 (=C1*3), I get the answer of 30.
OBSERVATION
If I sort the data largest to smallest, then my data will look like this:
ItemId Description Qty
3 boxes 0
2 bottles 0
1 cans 10
The formula does not change its reference. My formula output is now zero.
I would have thought the formula would change to: =C3*3
QUESTION
Is this how excel is supposed to act? Can you change this so that he formula reference is updated when you sort?
View 9 Replies
ADVERTISEMENT
Aug 6, 2009
I have this:
Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
Range("A1") = "Fiscal Year"
Range("A2") = "2010"
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).FillDown
Supposed to insert a column to the left of A, title that column "Fiscal Year"; put the text "2010" into A2, and copy "2010" down to the last cell.
All that works, but somehow, everything else from the other columns in row 2 is copied down.
View 9 Replies
View Related
Feb 10, 2014
How to sort on the simple value in a cell without regard to any formula? I have a spreadsheet and the cells reference others, and have addition formulas in them. When I sort it always references back to the original cell and not simply the value that is in the cell. for example: Column H refers back to column A. I want to sort column H by value low to high but I do not want it to look back at A when I sort.
View 5 Replies
View Related
Oct 11, 2006
I have a league table (attached) - that contains some formulas for updating various columns when scores are typed in. I have one piece of the puzzle left to do, and that is to have the table sorted by column 'O' (points column) in descending order. ie, the team with the highest points sits on top of the league.
The problem is, that whenever I sort the league table, the formulas attached to the 'Plyd' column (column H) get screwed up. This always happens and it doesn't matter if I do it through the use of a macro, or if I sort the league table manually by highlighting and then sorting. Always, the selection boxes in my 'COUNT' formulas seem to shift.
View 8 Replies
View Related
Jul 16, 2012
how to return the top 5 results in a set of data.
In a nutshell, I have data that needs to be sorted but some intermediate to advanced (to me) excel formula needs to be used to sort the ranking properly. I've already used index, match and large but it does not suffice.
View 1 Replies
View Related
Jan 28, 2014
Problem we are having with sorting a table.
The table (A1:E10) was created by pulling data from another worksheet using INDEX and ARRAY formulas.
We inserted 6 columns to move the table over to G1:K10 (used column F to space the two tables).
We then duplicated the table: cell A1=G1, B1=H1, ...., E1=K1 and dragged the formulas down. We now have a duplicate table that we want to sort.
We choose CUSTOM SORT and choose 2 levels of sorting ... column A and column C.
PROBLEM: The ARRAY formulation evidently overrides the sort function because the table remains as it was when the sorting command is executed.
Is there a way to bring the data from table G1:K10 over to table A1:E10 in a manner that will allow sorting??
View 9 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
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 16, 2014
Is it possible to hide formulas from the formula bar while still having the formulas active?
View 8 Replies
View Related
Aug 24, 2009
I have a spreadhseet with various functions on it and what I am trying to do is this.
Cell E4 returns a >35 or <35 true or false value
Cell G4 is either blank or has "Yes" text type into it.
What I am trying to do is get cell F4 to return certain arguments.
E4 = >35 and G4 is blank I want it to state "Email Hiring Manager"
E4 = ,35 and G4 is blank I want it to state "Wait"
I have a basic IF formula that returns this
=IF(E4>35,"Email Hiring Manager","Wait")
Then if cell G4 is populated with a Yes the formula needs to overwirte the origonal if with the return arguments of
=IF(G4="Yes","Email Agency","Email Hiring Manager")
If yes then what would be Email Hiring Manager (yes will only be input if E4 is greater than 35) will be overwritten with "Email Agency"
Can this be done with two If formulas or does there need to be 3 or more to count if other IF formulas are actually returning a value?
View 5 Replies
View Related
Dec 12, 2007
If you have a cell with the value ="2*c2+3" NB: (Notice the ""), then to make excel convert the formula in another cell to =2*c2+3 (notice the removal of ""), so that it can calculate the value of the cell instead of showing a textstring?
View 11 Replies
View Related
Dec 11, 2008
I have formulas in a column and they are working unless I edit them to include another function, more cells, whatever, then they display as formulas instead of the result. I've gone to Tools --> Options --> View and the Formulas box is not checked. As well automatic calculation is on not manual.
View 4 Replies
View Related
Mar 6, 2009
how to create the database and the resulting Pivot Table. My Sales Database includes the Sales $ and GP$ for each line. In the Pivot Table I've managed to create a calculated field for GP %, no problem.
i have also managed to figure out how to express each Row as a % of the Pivot Total, also very easy.
What I need to know is how can I sort my Pivot Table High to Low using these calculated fields?
View 9 Replies
View Related
Oct 4, 2003
I don't understand all of it, but I observed that it works only with mp3 with tag id v1, not v2, anyway I also see another code for tag id v2 given by NateO.
But before entering in detail of what I want, is it possible to edit tag id v1 or even v2, with VBA? Because I want to complete my mp3 informations that are missing, or modify the wrong one, in excel, and then with a macro update my mp3 files...
I've found this code on the forum to get my mp3 informations :
Option Explicit
Public Type mp3Info
Header As String * 3
Title As String * 30
Artist As String * 30
Album As String * 30
Year As String * 4
Comment As String * 30
Genre As Byte
End Type
Sub Getmp3Info()
Dim mp3ID As mp3Info
Dim lngRow As Long, lngFile As Long
Dim lngFileCnt As Long
With Application.FileSearch
.NewSearch
.Filename = "*.MP3"
.LookIn = "H:Daniel" 'Change this to the folder you wish to search
.SearchSubFolders = True........................
View 9 Replies
View Related
Jun 21, 2007
I have a sheet with fixed number of rows but variable number of columns. I have to sort this sheet on the basis of column A starting A2. i am using the following code
Range("A1", Range("A1").End(xlToRight).End(xlDown)).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
But it does not sort properly. To illustrate, i have 10 rows, some 4 columns wide and some 5 columns wide. Since the first row is 5 columns wide , all the rows with 5 columns populated get sorted but the rest get left out.
View 2 Replies
View Related
Feb 18, 2007
This is an example of what is being worked on:
(There are 500+ more lines like the ones below, in groups of approximately 17 lines per page of information.)
* C.B.A. Bank - 2nd reference to branch near Mike's store. (21/03/1956)
* Berwick exams results. (06/06/1877)
* Berwick exams (09/05/1877)
* Steps taken for erection of Berwick. (11/07/1874)
* Berwick tenders for work at home. (11/07/1877)
* W. was a storekeeper at Berwick. (25/07/1881)
* Berwick Brass. (27/06/1877)
* Commercial erected for son. (27/06/1878)
I need to do the folowing to it:
(not necessarily in the following order)
- Insert into Excel
- Remove brackets
- Sort chronologically
- Copy back into program I am using again, in same chronological order.
The desired outcome is to be able to throw into Excel all the lines and press a button, (possibly the Sort Ascending one) and have a chronological list of all entries.
View 10 Replies
View Related
May 11, 2007
I have a table which holds scores as well as other data for players. Is it possible to automatically sort a table based on their points score to see who is top of the league?
View 9 Replies
View Related
Nov 6, 2007
Is there a code or some way for me to sort 4 columns together? I want to sort a list of employees and for each employee theres a column with their Lost Business, customer satisfaction, and two more columns. I want to sort all the columns at the same time so that the best employees overall will go to the top of the list and the worst ones will be at the bottom. Whenever I use the sorting feature it does each column independant of the others so everytime I sort a new column it just moves around the last one I sorted.
View 14 Replies
View Related
Jan 12, 2008
I have five columns of data A to E. In each row there can be data in either 2 3,4 or 5 of the columns. What I want to do is essentially condense the data. So in columns, say, G,H,I J,K if there is data in column A then It goes into column g, if not it asks if there is any data in column b and so on. Then for H it does the same but one on. I have attached an example to show what I would like to happen.
View 9 Replies
View Related
Jun 20, 2008
I created a data base that had a hyperlink to a plan of the store so we can find parts easy. With a lot of help for people here on this web site, it works very well.
My only problem now is that I sorted my Database by the hyperlink column. It made it easy to do one hyperlink and pull the fill handle down for the rest of the same hyperlink reference.
When I went to resort the database so that the parts were in alphabetical order. The sort worked well except that the hyperlink for lets say cell F87 was not sorted. Example : - F87 hyper link presort had the reference to Rack 10 self B. Post sort it had the reference to Rack 16 shelf C. But when clicked on it still took you to Rack 10 self B.
View 11 Replies
View Related
Nov 14, 2008
I'm trying to sort this spread sheet so that each email address will have its own column assigned to it. Ie jaz@hello.com will alway be column C and harry@hello.com will always be in D. The problem is I've around 1200 rows of infor and the email addresses are all over the place..
View 5 Replies
View Related
Dec 20, 2008
how can data sorted be more frequency as most frequent on top and as the following:
1
1
1
2
2
3
3
3
3
to
3
3
3....................
i attach file
View 3 Replies
View Related
Aug 9, 2009
I have around 1900 dates that I need to sort into order but I need the data to be displayed as DD/MM in chronological order eg 01/01, 02/01 etc.
At the moment the dates are DD/MM/YYYY in one column. Have tried sorting them into date order but have found that they're sorted by YYYY. Is there a way of sorting them DD/MM instead?????
View 5 Replies
View Related
Aug 26, 2009
I should sort numbers in ascending order from left to right, but the numbers sort only from right to left.
This is what I want:
1.3.2.2
1.4.1
2.1.1.1.1
3.2.3.12
3.3.1
And this is how it goes:
2.1.1.1.1
3.3.1
1.4.1
1.3.2.2
3.2.3.12
There are also numbers that have letter at the end (1.2.3a), and the cell format is text.
View 10 Replies
View Related
Sep 8, 2009
I have an array of data that I need sorted. I did a Custom Sort. However, the data did not all sort properly (some of it did). Any ideas as to why this happened and how I can get it to sort correctly?
View 5 Replies
View Related
Oct 6, 2009
Im not entirely sure about this but it seems like if I have data in columns A - Z and sort in one of them, the data in Columns AA - AZ does not move accordingly rather it stays fixed.
View 4 Replies
View Related
Oct 7, 2009
I have a column showing date DD/MM/YYYY. I want to be able to get a new column stripping out the days so that I can chart changes over a month rather than each day of the last 2 years. I have tried altering the cell format to MM-YYY although this shows the format I want when i run a pivot chart it will still create records for individual days. I then tried copying and pasting the above as text only. This results in the pivot chart sorting A-Z rather than by date.
View 2 Replies
View Related
