Could someone please explain how to sort columns that contain formulas?
I have 17 rows in columns A:Z that I need to sort by column C in ascending order.
Column C is a list of code letters, i.e. E, U,C,etc.
Some columns contain formulas that refer to cells within the range, & others
to cells outside the range. In every formula, all referenced cells are absolute.
Sorting by column C makes the formulas change; I thought making them
absolute references would prevent that.
Similar Excel Video Tutorials
Sort, Sorting 10 Examples
- See these 10 Sorting Examples:
1. Sort 1 column with buttons
2. Add an order column to you can return to original sort order
3. Sort 2 ...
The Table should have only one header row.
Do not leave any blank cells in the header row.
Do not select a column or a row in a List before sorting; instead, select only a single cell. Clicking the Sort icon automatically sorts the entire List/Table, and the data will be sorted according to the selected cells field.
When sorting data beware of formulas in the cells. Sorting data linked by formulas to other cells, or to cells in other sheets, can distort the calculations. Be meticulous when sorting a List/Table containing linked formulas and defined Names (which are defined with absolute references by default), or with formulas that have absolute references.
Insert an additional column with ascending numbers (that is, 1, 2, 3, and so on) before sorting the data (do not use a formula). If a List includes a column with consecutive dates, use this column as the first sorting column.
I'm in need of some help on sorting columns of formulas and results.
In the attached example, after a value is selected in A2 (sorry, the validation doesn't work in the sample), columns B2 through F39 are populated through various formulas. Only some formulas are affected, and it's only those I really want to sort. I have a worksheet event which sorts by column B (the first pertinent piece of information). This routine has problems. My sort appears to sort both formulas AND results, so I had to do it in Descending order to get my Regions/PACs to float to the top. As you can see in the example, even that doesn't work consistently, as sometimes B2 is occupied by a formula instead of a result.
IF a value appears in column E, I would like to sort by that column first, then by B.
The trouble is I really don't want the formulas to be part of the sorts, simply the results of the formulas. Is there a way to sort only those, short of converting to values?
Finally, I'd appreciate some help with the formula in column F. If the column E value is below a certain percentage (TBD), the result in column F should be 0.
Thanks in advance for any help.
I think I am at the right place. I have read the posts for the past 3 months and my issue appears similar to those posted here.
I will attach a test sheet which involves the simple sorting of a column of numbers derived from a formula which is contained in the respective cell.
When I select the column (with the header), and select Data>>Sort>>Scores>>descending order, nothing happens; the column remains aligned according to the alphabetical list of the arguments/formulas in the cells.
How can I sort on the column containing the numbers derived from formulas?
I thought I uploaded test3.xls
I am making a spreadsheet for several users, with columns A and B for user input and columns C thru E containing formulas. I am pretty sure people are going to want to enter data out of order, and try cutting and pasting to get it in the right order. The problem is that if, for example, a user cuts data out of B2 and pastes it into B3 (to make room), C2's formula will update to refer to B3, and C3's will have an invalid reference. The problem is pretty much the same if they Insert, Cells, Shift cells down.
I tried locking and protecting, but formulas in the locked cells update anyway. I tried using absolute cell references, but that doesn't work either (=$B$2 becomes $B$3). I with there were some sort of "cut special" command that would tell Excel that the old data is wrong for the cell and too look only at new data. Paste Special doesn't seem to work after copying a cell that is referenced in formulas (news to me but probably not to the veterans!)
Right now I am thinking my only option is to put a note on the sheet telling users not to use the cut and Insert commands, which raises the question, how to interpolate new data. I am wondering if I should just have them append it and then sort (which will work only if the correct order is ascending or descending?) Or, I could tell them to fill down or copy data instead of moving it and then typing over the original row.
Is there a way to protect vulnerable formulas from cut-and-paste or Insert Cells alterations?
I've got a range of cells with similar formulas in - just different cells are referenced
If I want to add a new element to all the formulas, then this spreadsheet has been badly designed such that I can't just change it in one cell and drag it to all the others.
There are 2 or 3 references that I need to stay fixed as I copy the formula from cell D10 into cells E11, F12, G13 and so on. After I've done that I need those references to vary as I copy the D10 formula into E10, F10, G10 and so on.
Each of these references are to cells on a worksheet called Data e.g. Data!Z1, Data!AA16, Data!AD138
So I thought I'd look for a VBA solution
I first thought about using the InStr method and telling it to search for "Data!". But then when I want to insert $ around the references, I don't know what position the $ should go in because I don't know whether the column letter is 1 or 2 characters long and whether the row number is 1 2 or 3 digits long.
So I Googled for other solutions and saw VBA code that lets you set all references in a formula to being absolute references. But I was wondering is there a bit of code that lets you convert just a selection of references to being absolute?
Or any other solutions?
Thanks in advance for any ideas
Hopefully this is a simple one...
Cells(LastRow + 1, c).Formula = "=SUM(" & Cells(7, c).Address & ":" & Cells(LastRow, c).Address & ")"
That line of code produces a formula in the target cell along the lines of "=SUM($A$7:$A$10)"
The problem is that later in my code I run a horizontal sort and afterwards all of the formulas are wrong because the absolute column references don't change with the sort (i.e. column A is now Column C, yet the formulas in Column C are all still "=SUM($A$7:$A$10)"
Is there a way around this using my existing code, or do I need to utilize R1C1 or something like that? If so can someone assist with the line above?
I have a list of names in Column B. This is followed by 8 columns of formulas that calculate information from different sheets of the workbook related to the name in column B. Column K through IV are used to put in numerical data that is also used in the calculations that the formulas in C thru J perform.
I need to be able to sort the names in column B AND columns K thru IV, since those numbers are "tied" to the name. But I don't want the formulas in C thru J to be sorted. I thought the formulas would automatically re-reference themselves since they do when they are copied and pasted... but they don't. So they end up referencing the "old" locations for their calculations instead of changing their references to match their new locations. I temporarily solved the problem by using code that re-copies and re-pastes the formulas beginning at the first cell after the sort, and that makes them reference the correct data... but it's clumsy.
Is there a way to skip over columns C:J and have B sort with K thru IV?
I set up a very large workbook. I have linked the formulas of one sheet to
another, however, I need the columns to be rows and the rows to be columns.
I have linked the values, but I need to make all the cells in the sheet
absolute before I can transpose them. There are over 20,000 cells and I
really don't want to hit the F4 key 20,000 times in the sheet. A replace
would not work for this, as the data is too big. I have tried a copy and
then paste special- paste link, but it only makes the reference absolute when
one cell is copied at a time. Any ideas on making many cell references
absolute at once?
I have this case,
I have two columns that have data changing constantly. one column is numeric and the other one is text which always is a,b,c,d in order. it has only 4 rows. example
I need to sort this autmatically with formulas to show them in decending order ie:
I've mananaged to sort the numeric columns using the large formula, but I can't get it to sort the text part.
thanks in advance
I have a worksheet with columns of data. On another worksheet which summarizes the data I have formulas referring to the columns of data.
If I delete rows or sort rows on the data sheet the formulas change to reflect those deletions or sorts.
How do I in the formulas refer to a range in the data worksheet such that when the data is sorted or when data rows are deleted the formulas still refer to the same range of cells without changing.
After deleting the top 10 rows of data:
=COUNTIF('General Delivery'!$T$2:$T$4991,'Data Analysis'!B3)
After then cuttting & then inserting rows 12-22 into row 2
=COUNTIF('General Delivery'!$T$13:$T$4991,'Data Analysis'!B3)
I'd like to find a way such that the delete or cut/insert operations do not change the orginal formula's cell references.
Any help will be much appreciated.
To change the range for # of formulas at 1 time.
If i have 10 formulas - vlookups, match etc...
So they would all be similar to
= vlookup(A1, D1:G10, 4, false)
But I have 10 formulas that refer to the D1:G10 range.
This could vary, most importantly on the # of columns, so next week thsi could look like H1:N10 for example.
I believe if it was simply a matter of adding a column into the spreadsheet, and it went from D1:G10, it would automatically go to D1:H10. But if I have it look at a different range such as H1:N10.. is there a way to update that range for all columns?
I was thinking perhaps I should put in Cell B1 and C1 values for the columns. So if I could write the formulas so that it references B1 and C1 - then i would just change B1 and C1 and it would update all the formulas with the right range.
I construct speadsheets using formulas that contain both relative and mixed cell references. When I have completed a worksheet, I need to change all of the formulas to have all absolute cell references. I must do this in order to be able to filter and sort properly.
How do I change all formulas at one time to absolute without having to select each individual cell one at a time and toggle using the F4 key to change the reference?
i am wondering if i can do the following with a macro.
i have 10 workbooks in a directory and i need them to be copied to a master workbook. This should be done once a month.
the format is like this:
The sum formulas are always the same, but the formulas in cells A3, A5, B3 all refer to other worksheets and the formula in cell B5 has absolute reference to other sheets.
i have tried copying the whole range A1:C5 to the master workbook, for all workbooks (one table at range A1, the second at A7 etc), but i find that the cells that refer to other wbks change their content.
i tried converting all formulas to absolute references, but then i dont get the correct result for the formulas with sum().
do you have any suggestion on how i should solve this problem? I'd rather not change the original 10 sheets, because they are alrready being used by the users and i would prefer not to take them back and change them.
the workbboks name changes each month, so i dont think i could just link all the cells to the wbks, except if i do it using a macro.
any ideas would be most welcomed!
Hello everyone. I have just joined and this is my first post.
I come to you to seek a huge help regarding an excel file I'm trying to create.
To simplify my explanation of the project, I will explain the situation as follows.
I'm using a total of 20 Rows and 2 Columns. Each row has Column A for Description and Column B for Score. There are total 10 subjects with 10 noneditable rows, and 10 editable rows.
I'd like to protect column A and B for rows 1 to 10 and unprotect only Column B for rows 11 to 20. When any cells from Column B, rows 11 to 20 updates, I'd like to automatically sort columns A+B for ONLY rows 1 to 10.
Therefore, rows 1 to 10 need to be sorted based on the scores on Column B, and since rows 11 to 20 are just input fields, they are never to be sorted.
Column B for Rows 1-10 will be formulas and are based on Column B values from rows 11-20.
When a value in any of the rows 11-20 of Column B is updated, Range A1:B10 will automatically sort from highest score to lowest score (Column B).
Since Column B for rows 1 to 10 contains formulas and not actual values, would it throw off the order of things when sorting formulas?
I'd really appreciate the help if anyone can help me with the macro coding to perform this task. Thanks so much in advance!
Here is just a visual of what the spreadsheet will look like
The Bold are editable fields. and I want to use auto sort A1:B10 based on updated values of B11 to B20.
I have also attached the basic excel file that situates my concern.
In Cells A2:A1001 there are customer full names - first and last name and any middle names (example: John James Smith)
In Cells B2:B1001 there are the membership numbers of the customers
In Cells C2:C1001 I have the following equations which extract the last name of the customers so I can sort by last name:
=IF(A2"",RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))),"")
I have a Worksheet_Change event that monitors when the membership number is entered and then sorts all the names by the surname (column C).
However, it is sorting all the names in alphabetical order but moving them to the bottom of the range ending at Row 1001.
I think it's because the sort command is sorting the actual formulas rather than the results of the formulas.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Not Intersect(Target, Columns("B")) Is Nothing Then
Range("A2:C1001").Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Application.ScreenUpdating = True
Is there any way to fix the sorting code so that it sorts the actual results of the formulas rather than the formulas themselves.
I have my reasons for having the customer's full name in one cell rather than splitting them into columns for First, Middle and Last Names.... that being that I have the name column set up as a named range which I then refer to from another sheet to create a dropdown Data Validation selection cell.
Thanks in advance,
I need some help sorting data. I have three columns that have formulas from 2 different sheets that are in columns A,B,C. the results of the formulas could be blank and I need to sort the data in descending order. when i do this the blank or " " data is listed first because it has formulas in the cells. What I need to do is sort the data in descending order and place the blanks at the bottom. basically, i am trying to get the top ten values in a range. i know i could use =max(C:C) but i need the top ten and I am not sure how to get a result with the top ten using =max(C:C). your help is greatly appreciated.
I am writing some formulas referencing another table in my workbook.
When using structured table references in my formulas like this table_name [header]... compared to actual cell range names like H:H or H1:H1000..
How do I make these structured table references absolute?
When I'm copying these formulas across columns in my worksheet they are moving the table headers relative to the new column position. In other words I want to use structured table references the same way an absolute cell range like this $H:$H would function.
Any advice would be appreciated!
I have a workbook with 2 spreadsheets. The first spreadsheet has a
cell which contains the last day of the month. The 2nd spreadsheet
has some heading rows, a row of formulas across the top (Row 5), a
blank row and then monthly table of data values starting in row 7
with the last day of the month in the first column. The most recent
month is in row 7. The formulas across the top are VLOOKUPs which
go out and pickup the last day of the month from the first
spreadsheet using an absolute cell reference $A$1. The range of the
VLOOKUP has mixed cell references (e.g., $A$7:$Z50). It returns the
corresponding cell for the requested date. When I add a new month in
the 7th row, I'm selecting the 7th row and then inserting a new row.
When I do this, my absolute cell references in the formulas are
changing from $A$7:$Z50 to $A$8:$Z51. I thought absolute cells
weren't supposed to change. I expect and want the ending row number
to change but not the starting one. What am I doing wrong and why
is the cell reference changing?
I have a row with very simple formulas in each of 5 adjacent cells. The formulas are of the type =b1, =c1 and so on.
What I want to do is to make all of the formulas absolute references - =$b$1, =$c$1 and so on.
How can I write a macro to do this automatically?
In other words, I want the macro to start at the first cell and change the references to absolute, go to the next cell to the right and change the references to absolute and so on.
The macro recorder doesn't do this correctly.
Can anybody help me out? I'm very new at Excel macros so the simpler the better.
I'm probably dating myself but you could do this very easily in Lotus. I'm hoping there is a similarly straightfoward approach in Excel.
I have a difficult issue that I am trying to resolve and since I am new at excel2003 I cannot find the answer on my own.
I have a row of cells (B9:P9 for example) where each cell contains a formula for some calculation. The values calculated in each cell from B9 to P9 are not in order. I would like to create another row (lets say B15:P15) that will display the values in B9:P9 but in Descending sorting order. The sorting needs to happen as soon as values in B9:P9 are present. I tried to copy the rows B9:P9 to B15:P15 and when I copy and paste, the formulas get copied too. I now try to sort (B15:P15) with the sort function from the Data menu "Left to right" but it will not sort since (B15:P15) have formulas. Can you tell me how I can do this? Thank you so much for your help.
I've got data in Columns H and I -- all of this data is just alike in type and format (each cell has only a single digit number -- 1, 2, 3, 4, or 5) and both columns are just the same size.
Over in Column B are cells with COUNTIF formulas, where the referenced "range" is a portion of Column H (the "criteria" of the formula is in Column G). Fine, works great.
Now I need another worksheet just like this, but with the COUNTIF formulas referencing the same rows in Column I (and same criteria in Column G).
So I thought I'd do this: copy Sheet 1 (rename it to 2) and then delete Column H, because doing that deletion would mean that Column I would simply shift left and become Column H, and the results of the formulas in column B would update, based on the "new" data in Column H.
But this doesn't happen. When I delete Column H in the copied workhsheet, I get the REF# error in the Column B formula cells.
I don't understand. Why won't Column B's formulas just work on the changed data in the "new" Column H. It seems like in the past when I delete rows or columns like this, formulas just update.
using Excel 2003, XP
A colleague of mine made a spreadsheet which has a whole array of formulas in, however he did not use absolute references ($) in the formulas and now I need to change the worksheet which will make the formulas incorrect - is there a quick way to apply the $ sign's to a whole block of different formulas instead of going into each one individually?
I am trying to sort 20 cells from highest to lowest. The only problem is 2 of the values are formulas, the other 18 are hard-coded values. Of the 2 formulas, one is the mean and the other is the median of the other 18 values. When I sort them, the cell references for the aforementioned formula cells change and therefore the values of the mean and median change. How do I sort the range without making the formula cells change?
I have a considerable number of cells containing formulas exceeding 255 characters. I need to change all cell references in these formulas to absolute. Apart from manually changing each using F4 is there a way to convert all at once?
Thanks in advance
I know how to use F4 to toggle between relative and absolute references in a formula when you click on the single reference. But it would be helpful if I could select a whole range of cells containing formulas and make all the references within the selected cells absolute.
Is there a way? When I search I only get info on the basics of relative vs. absolute and nothing more advanced like this.