sorting formulas

Hi,

Ive made a spreadsheet which all the cells accept titles contain look
up formula's. I want to be able to do sorts on the columns but it
doesnt seem to work. I think its because excel recognises the content
of the cells as a formula, but i want it just to see the result of the
look up and sort that.

Is there anyway to solve this problem?


Thanks to all who help.

Matt


-- 
m4tt
------------------------------------------------------------------------
m4tt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25835
View this thread: http://www.excelforum.com/showthread.php?threadid=399142

0
8/25/2005 4:15:12 PM
excel 39879 articles. 2 followers. Follow

2 Replies
478 Views

Similar Articles

[PageSpeed] 36

m4tt,

I'm not sure what you need.  Are the cells containing a VLOOKUP in the table 
being sorted?  If so, they get effectively copied (that's how sorting 
"moves" cells) to their new row when the table is sorted.  It may be that 
your LOOKUP functions need absolute references to the table they're looking 
in.  Or you may need to convert them to data (copy, then paste-special right 
over them), in which case the title cannot change for that row, since the 
VLOOKUP isn't there any more.  I think an example of a couple of the rows of 
your table would be useful.  And some of the table in which the VLOOKUPs are 
looking.
--
Earl Kiosterud
www.smokeylake.com

"m4tt" <m4tt.1ubxub_1124989503.6635@excelforum-nospam.com> wrote in message 
news:m4tt.1ubxub_1124989503.6635@excelforum-nospam.com...
>
> Hi,
>
> Ive made a spreadsheet which all the cells accept titles contain look
> up formula's. I want to be able to do sorts on the columns but it
> doesnt seem to work. I think its because excel recognises the content
> of the cells as a formula, but i want it just to see the result of the
> look up and sort that.
>
> Is there anyway to solve this problem?
>
>
> Thanks to all who help.
>
> Matt
>
>
> -- 
> m4tt
> ------------------------------------------------------------------------
> m4tt's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=25835
> View this thread: http://www.excelforum.com/showthread.php?threadid=399142
> 


0
someone798 (944)
8/25/2005 9:27:04 PM
Hi Matt,

Without examples can't tell what your problem is,  but if you have formulas
referring to other rows on the same worksheet you could have a problem.

For instance you would not want to use a formula like
 E57:    =C57+E56
instead you would want to use
 E57:    =C57+OFFSET(E57,-1,0)

applies to inserting / deleting rows   as well as sorting rows.
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"Earl Kiosterud" <someone@nowhere.com> wrote in message news:u1j7$ubqFHA.2076@TK2MSFTNGP14.phx.gbl...
> m4tt,
>
> I'm not sure what you need.  Are the cells containing a VLOOKUP in the table
> being sorted?  If so, they get effectively copied (that's how sorting
> "moves" cells) to their new row when the table is sorted.  It may be that
> your LOOKUP functions need absolute references to the table they're looking
> in.  Or you may need to convert them to data (copy, then paste-special right
> over them), in which case the title cannot change for that row, since the
> VLOOKUP isn't there any more.  I think an example of a couple of the rows of
> your table would be useful.  And some of the table in which the VLOOKUPs are
> looking.
> --
> Earl Kiosterud
> www.smokeylake.com
>
> "m4tt" <m4tt.1ubxub_1124989503.6635@excelforum-nospam.com> wrote in message
> news:m4tt.1ubxub_1124989503.6635@excelforum-nospam.com...
> >
> > Hi,
> >
> > Ive made a spreadsheet which all the cells accept titles contain look
> > up formula's. I want to be able to do sorts on the columns but it
> > doesnt seem to work. I think its because excel recognises the content
> > of the cells as a formula, but i want it just to see the result of the
> > look up and sort that.
> >
> > Is there anyway to solve this problem?
> >
> >
> > Thanks to all who help.
> >
> > Matt
> >
> >
> > -- 
> > m4tt
> > ------------------------------------------------------------------------
> > m4tt's Profile:
> > http://www.excelforum.com/member.php?action=getinfo&userid=25835
> > View this thread: http://www.excelforum.com/showthread.php?threadid=399142
> >
>
>


0
8/25/2005 11:40:38 PM
Reply:

Similar Artilces:

How do I exclude multiple low values from a formula?
I am trying to create a worksheet in excel to calculate my students' grades. I need to exclude the two lowest marks from the string of values. I know that using MIN I can remove the single lowest value, but how do I remove multiple low values? ex. (3+4+5+6+7)-(3+4) 3,4,5,6,7 are the grades and the formula subtracts the two lowest grades of the series. =sum(your range) - min(your range) - small(your range,2) give you the sum ex lowest two divede by count(your range) - 2 to get average if desire -- Message posted from http://www.ExcelForum.com ...

formula based on continuous relative cells
I want formula where the value is the sum of the first cell i select plus every 6th cell after it in the row and also minus every 5th cell in that row. ...

"Millionaire Retires and Gives His Multi-Million Dollar Business Formula To You"
Millionaire Retires and Gives His Multi-Million Dollar Business Formula To You. You'll even get a copy of his million dollar website by visiting www.resell-rights-make-money-online.com Only the first 52 people to visit the website will be accepted. --- MAF Anti-Spam ID: 20060719221729Z2h0PvD7 Posted Via Usenet.com Premium Usenet Newsgroup Services ---------------------------------------------------------- ** SPEED ** RETENTION ** COMPLETION ** ANONYMITY ** ---------------------------------------------------------- http://www.usenet.com ...

Sort birthdays by month
Is there a way to sort a date field by month instead of year? It seems to always sort by year whenever I change the format or try other things.. TIA, Jackson ...

Sort number as text
I have an excel 2000 file in column I, I has cost center numbers, the range of numbers are as follows. 000, 050, 555, 850. I have made the cell text cells so they will keep the preceding 0's. However when I sort them, they come out in the following order. 555, 850, 000, 050. Hi To display numbers with preceeding 0's you don't need to change cell format to text. Format the cell as Custom "000" instead. When you need to convert numbers to strings somehow, use formula =TEXT(CellReference,"000") in some adjacent column. NB! You can't use converted value...

help on sorting data
Hi, I have a soccer grid result. So I would like the column corresponding to the points of the teams sorted descending. But I want the other column to follow. And I want the first column (rank) to remain still and not affected by the sorting operation. How can I do that ? thx Just select the columns you want to sort, and then sort. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Sam" <samuel.berthelot@voila.fr> wrote in message news:1113938180.931864.48830@o13g2000cwo.googlegroups.com... > Hi, > I have a soccer ...

sort text from the right-to-left
Hi, I need to sort several text data by their suffix, I mean, the characters from the right-to-left order. Any help? Santistas, I think you need to give some examples of the data. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Santistas" <santistas@hotmail.com> wrote in message news:ezfDw3NkEHA.3712@TK2MSFTNGP15.phx.gbl... > Hi, > > I need to sort several text data by their suffix, I mean, the characters > from the right-to-left order. > > > > Any help? > > > I think you n...

What instead of an array formula part 2
Hi all Ive posted this question before (orignal post below) but i did not describe my problem correctly so i will try again. Our reporting system will generate a "RAW" Excel file, the excel document i have created uses the formula below to add up figures and give a total for a particular mix of categories. e.g. of the RAW data headings Company Type DataType Branch Division Dept Sub Dept Amount this data is then "pivoted" and filtered with the array formula The rows are determined by the type col The Cols are determined by a combination of Type...

how do i write a formula and keep in in formula form, so it DOESN.
i have to take a computer test and one of the questions is to write a formula i've previously used but write it so the actual formula is shown on the sheet and when its printed so how do i do i write the formula because everytime i do it it calculates but i need it to stay in formula form with the = sign too? Hi! Precede it with an apostrophie: '=SUMIF(A1:A100,B1) Biff >-----Original Message----- >i have to take a computer test and one of the questions is to write a formula >i've previously used but write it so the actual formula is shown on the sheet >and w...

I wrapped a two line cell. I sorted but the wrapped cell didn't g.
I am putting a list of names on excell. Some have to go on two lines in one cell. I wrapped that cell. But when I sort data from ascending to descending the wrapped cell does not go with that info. Any suggestions When I format a cell with Wrap Text, and sort the list, the formatting stays with the cell. Are the values typed in your list, or are they links to other cells? Mike's cells wrote: > I am putting a list of names on excell. Some have to go on two lines in one > cell. I wrapped that cell. But when I sort data from ascending to > descending the wrapped cell does...

READ THIS UPDATED POSTING: Problem using fill with formulas
(Previous posting contained an error in the formula.) I have a column. I want to fill in the formula from B1 all the way down to B200. I select B1 and drag my mouse down to B200 and select Fill Down. The formula is inserted in every cell. However, I have: =VLOOKUP(A4,[yahoo.xls]Sheet1!$A1:$H200,2,FALSE) =VLOOKUP(A5,[yahoo.xls]Sheet1!$A2:$H201,2,FALSE) when I want the second instance to be: =VLOOKUP(A5,[yahoo.xls]Sheet1!$A1:$H200,2,FALSE) In other words, I want the first cell reference (A) to change, but not the cell references in the yahoo.xls worksheet. Because they are changing, I need ...

Listview/Treeview
Hi, I am trying to do a listview or treeview in Access 2003 that allows the users to sort the entries using drag and drop. It should work the way Internet Explorer handles its favorites or Windows handles drag and drop in its Start Menu. You can drag one entry in between others and the drop target is represented by a line. Is there a way to replicate the behaviour of sorting via drag and drop in Access 2003? All I can do now (die to some help in the german group) is to drag one element on top of another element but that makes sorting a mess. I need a way to drag one element BETWEEN ...

changing formula
=IF(M6>0.9,IF(M6<9,1.06,VLOOKUP(M6,$P$1:$Q$16,2))) You could use the Find-Next button in that dialog to step over those that you don't want to change. Not perfect, but probably quicker than manually. Other than that it is VBA. -- HTH Bob Phillips "Alien" <eyl@edcomember.net]> wrote in message news:ncg7jv8q1ssncblq6tcaq3rh1b4s0l7iko@4ax.com... > Thanks, But I only want to change the values in one cell. I did that > and it made 1070 replacements when I only wanted it to make about 15 > > > > On Fri, 8 Aug 2003 13:23:48 +0100, "Bob Phillips&q...

How can you set a cells data using a formula?
I would like to set a cells data using a formula... is this possible? For example is there a function to set cell A1 to have the value "Bob" =set(a1,"bob") =A1="Bob" -- Regards, Peo Sjoblom "Moloth" <Moloth@discussions.microsoft.com> wrote in message news:677E8F28-06E8-462F-919F-52EE5A1EAC9C@microsoft.com... >I would like to set a cells data using a formula... is this possible? > > For example is there a function to set cell A1 to have the value "Bob" > > =set(a1,"bob") Note that a function can only r...

Control formula calculation
I have created a "form" where a user enters 2 separate numbers. I wan each of these numbers to input to a running total and the total t subtract from another number... this is what I have come up wit (understand it is a circular ref ..) 1 2 User input 2 User input 2 Prior input 0Prior input 0 Total input 2 Available 36 Remaining 3 -- Message posted from http://www.ExcelForum.com ...

New at Formulas
I'm not really sure how to explain this, but I'll do my best. Cell A1 = 10 - Buy Cell B1 = 20 - Current Cell C1 =SUM(B1-A1) <-- which would be 10 - UnProfit Cell D1 =SUM(B1-(C1*10%)) <-- which would be 19 (10% of 10 is 1, 20 minus 1 is 19) - Trailing Stop Cell E1 =IF(B1<=D1,"YES") <-- if 20 <= 19 show YES, which it wouldn't because it's not. This is fine and works, but what I want to be able to do is store the amount in D1 somewhere so that, if I change B1 to equal 15, the formula in E1 is actually working this out on the previous amount in D...

Sort calculated field
In a query I have a field that calculates the age. Age: IIf([CloseDt] Is Not Null,"CLOSED",(Date()-([BCBSreceived]))) I need to shows these in Decending, I assume it's a format issue, because it returns: CLOSED CLOSED 88 87 753 71 70 How can I make a calculated field sort????? Sort on this field instead. You can uncheck it so that it doesn't show. Still show your field below. SortOn: CDate(IIf([CloseDt] Is Not Null,#1/1/1800#,[BCBSreceived])) -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Dan @BCBS&q...

formulas to change font
I am new to this - I would like to know how to change the font color conditional to a formula result. for example =if((A1<than 0,"font red", "font blue") I am familiar with some very simple formulas - but I would like to understand how to perform the above. Please don't laugh - but I would like to be directed to the right place to learn how to do it? Yonnie Yonnie, If you want to change font color in cell A1, first change the font color to blue. Type "0" (zero) in cell A1. Along the top of your excel you will see an A with a color under it. Typical...

Help please with formulae!!!
I'm hoping someone can help me... all of a sudden my excel worksheets won't copy formulae from one cell to the next - only the value copies. If I manually insert the formula for a total, any changes within the data range are not reflected in the total. Can you please help?? thank you Jen Hi Jen A sheet behaves like that if it's protected and the formula cells are marked Locked and Hidden. Try unprotecting the sheet. -- HTH. Best wishes Harald Followup to newsgroup only please "Jen" <Jen@discussions.microsoft.com> skrev i melding news:0dad01c425f9$8c8d2040$a4...

Problem with VBA returning the contents of a long formula.
I have run across a problem with getting a formula from VBA. Excel specifications state that a worksheet formula can have a maximum length of 1024 characters including the leading "=". >From the simple test that I did, it looks like VBA will not return formulas that are longer than 1022 characters including the leading "=". And an even stranger result - I found a formula of length 901 characters including the leading "=" which could not be entered on a worksheet? I have included the VBA code that I used to create the examples. Any ideas as to what is going...

Become formula.
A B C D E 1 1 1 1 1 2 3 4 5 6 3 6 10 15 21 4 10 20 35 56 5 15 35 70 126 6 21 56 126 252 7 28 84 210 462 8 36 120 330 792 9 45 165 495 1287 10 55 220 715 2002 ...... "A:A" is serial numbers. "B1"=SUM(A$1:A1), "B2"=SUM(A$1:A2), "B3"=SUM(A$1:A3),... "C1"=SUM(B$1:B1), "C2"=SUM(B$1:B2),... ...... "E10"=SUM(D$1:D10) Drawing "B1" to fill other cells. I want a formula/function, when call it by (X,Y), Formula/Function counting that Cells(X,Y) value. without list all process by Worksheet. Thank. One way .. Assuming ...

Sort by Invoice Number/Payment Number
In the Inquiry -> Purchasing -> Transactions by Vendor window Many of our customers would like the associated INV type and PMT type to sort together, not sort by document number, document date or document type. The invoice # and check # are usually different, as are the invoice date and the check date. Thank you ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the sug...

Sort text cells with diacritics in Excel
Hello, I need to sort text cells with diacritics, like words with acute (Á) and carron (Č). eg. I have words Acam, Ácam, Acom, Ačom... and I need to sort them like this: Acam, Acom, Ačom, Ácam... Please help, thanks. Why would Ačom come before Ácam? Is this type of sort a common need in your profession/industry/location? Are you able to use VBA code (macro)? -- Jim Cone Portland, Oregon USA "semofan" <semofan@discussions.microsoft.com> wrote in message news:8ED04AA5-FCA5-4E5D-BBE0-938C09B20FCE@microsoft.com... Hello, I need to sort t...

Testing for an Integer result in a formula
I'm writing a nested IF statement in which one step needs to test the value of one cell divided by another cell, and determine if it is an integer before it completes the calculation. So, IF cell R10 / I13 = INTEGER, then I'll perform a Future Value calc. IF NOT = I want to do nothing. I tried ISEVEN, but when I turned on the Analysis Took Pak nothing happened. My spreadsheet still did not recognize the ISEVEN. Suggestions please? -- Jerry G Try this: =3DIF(INT(R10/I13)=3DR10/I13,"integer","") Hope this helps. Pete On Dec 4, 3:23=...

sum formula not working, producing incorrect answers Excel 2003
I am a pretty competent user of Excel, but on some occassions, a simple formula like SUM will not work. Whether doing it using the Sum (Sigma) icon or creating the formula in the formula bar (ex. =A12+A13) it is not working. I have tried clearing and deleting the cells and recreating them, it doesn't matter. If I redo the formula with the actual number values in each cell it will work but it is as if it isn't reading the values in the cells properly. I have tried looking at the cell properties and can not find anything obvious. Please help. Thank you. - CG It might be a ...