cell looses name after sorting

Can someone help me with the following problem in Excel 2000:

in a table I have attached serveral cells with unique cell names, the
values in these cells are used in other sheets. 

the problem is that when I sort the table, the cell names stay in the
original rowposition; they are not sorted! while their values are. So
Cell names get different values, and other calculations on my other
sheets get messed up!

How can I make the cell names relative instead of absolute?

thankx in advance,
Jim


---
Message posted from http://www.ExcelForum.com/

0
1/19/2004 10:54:33 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
665 Views

Similar Articles

[PageSpeed] 11

"jimfx >" <<jimfx.109zcv@excelforum-nospam.com> wrote in message
news:jimfx.109zcv@excelforum-nospam.com...
> Can someone help me with the following problem in Excel 2000:
>
> in a table I have attached serveral cells with unique cell names, the
> values in these cells are used in other sheets.
>
> the problem is that when I sort the table, the cell names stay in the
> original rowposition; they are not sorted! while their values are. So
> Cell names get different values, and other calculations on my other
> sheets get messed up!
>
> How can I make the cell names relative instead of absolute?
>
> thankx in advance,
> Jim

You should understand that sorting does not sort cells - it sorts data. The
cells remain fixed, whilst the data is moved into different cells to
accomplish the sort. Therefore, whether you reference cells with their cell
addresses or by name, after sorting the references will return different
data.

If that's not what you want, the simplest solution is not to sort! The
alternative is to write your referencing formulas so that they look up the
appropriate data, using functions such as MATCH, INDEX, VLOOKUP, etc.


0
Paul
1/19/2004 12:21:47 PM
Jim
As Paul stated, sorting moves the values not the cells.  You might be able to use one of these options

1)  Define the value directly, instead of in a cell.  If you want "Fred" to equal 24, then in Insert>Name>Define, enter Names in workbook: Fred and Refers to: =24

2)  Place the named cells in an area that isn't sorted, or on another worksheet, and then refer to the named cells in the list that you are sorting

Good Luck
Mark Graesse
mark_graesser@yahoo.co

    
     ----- jimfx > wrote: ----
    
     Can someone help me with the following problem in Excel 2000
    
     in a table I have attached serveral cells with unique cell names, th
     values in these cells are used in other sheets.
    
     the problem is that when I sort the table, the cell names stay in th
     original rowposition; they are not sorted! while their values are. S
     Cell names get different values, and other calculations on my othe
     sheets get messed up
    
     How can I make the cell names relative instead of absolute
    
     thankx in advance
     Ji
    
    
     --
     Message posted from http://www.ExcelForum.com
    
     
0
anonymous (74722)
1/19/2004 3:16:10 PM
Thanks Paul, Mark,

I am going to focus on function VLOOKUP.

rgts,
Ji

--
Message posted from http://www.ExcelForum.com

0
1/20/2004 12:29:53 PM
Reply:

Similar Artilces:

connecting cells with connector lines
Hello! Is it possible in Excel (2007) to draw a connector line (with or without an arrow ...) between two cells in a worksheet, so that the line-tips will follow their cells even when the latter are moved ? Thanks Michael On Feb 25, 7:00=A0am, Michaelprem123 <michaelprem...@gmail.com> wrote: > Hello! > > Is it possible in Excel (2007) to draw a connector line (with or > without an arrow ...) between two cells in a worksheet, so that the > line-tips will follow their cells even when the latter are moved ? > > Thanks > > Michael The line will automatically a...

Macro named "Page Control"
After many successful printings I received this message when I tried to print a report; Access can't find the macro "Page Control." I never created a macro prior to printing the report many times. How do I correct this? ...

Summing items in a cell
How do you SUM items in a cell. I have a parts list with reference designators like U3-U6. I want the sum of the range of items, in this case it's 4, but can't figure out how to do this. Any help will be appreciated -- dpmoore ------------------------------------------------------------------------ dpmoore's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24920 View this thread: http://www.excelforum.com/showthread.php?threadid=384566 More info and then a formula using find can be used or a udf (macro for custom formula) -- Don Guillett SalesAid Soft...

2 different fonts in the same cell?
Hello! If I want (Times New Roman) "This is a red Wingdings 3 triangle ([change font] Wingdings 3: 123 [back to Times New Roman]").", I can do that in a cell. But if I refer to that cell (=A1) in B1, the Wingdings font doesn't come over and it winds up all TImes New Roman and looks like "This a red Wingdings 3 triangle ({)." How can I "=A1" for a cell that has two fonts in it and have the two fonts show up?? Thanks! VR/ Lost You can't have that in a FORMULA. You would have to change it to text first. -- Don Guillett Microsoft MVP Excel Sales...

How to tell Excel to insert cells and shift down from Access
Hi all, I have Access 2003. I am trying to tell Excel to insert cells and shift cells down from Access, with this line of code: oWksh.Cells.Insert Shift:=xlDown But Access does not like that line. It gave me run-time error 1004. I know if has to do with the part Shift:=xlDown Can you suggestion what I can do? Thank you in advance, Ben "Ben" <Ben@NoSpam.com> wrote in message news:eSZuzcDpKHA.1892@TK2MSFTNGP02.phx.gbl... > Hi all, > > I have Access 2003. I am trying to tell Excel to insert cells and shift > cells down from Access,...

EXCEL Cell Formatting: Custom or Conditional
At work I have Excel 2002 and I have some cells that I import data in from the mainframe. I have a table where there is a column for GENDER (MALE or FEMALE) that I would like to see shown as "Male" or "Female" (mixed case). Is there a way to change all upper case to mixed case using either CUSTOM formatting or CONDITIONAL formatting? I would prefer not using a macro if there is something simpler. I know that =PROPER() produces the correct result, but I don't see how I can use this unless I'm referencing another cell. I would like to be able to just change what fa...

Import excel named range into Access error 3011
Hi, I've read a few posts on this topic, but none addresses my particular problem. I use Office 2003 on an XP SP3 machine. I am using transferspreadsheet to import a named range in an excel file into Access. This has worked beautifully until suddenly it stopped working. I find now that if I extract the address of the named range from within the Access VBA code, it looks like this: =#REF!$A$1:$Z$166 rather than this, which is what it should be: =Data!$A$1:$Z$166 When I open the spreadsheet and get the address of the named range in the immediate window, it also has the #REF error....

CFtpFileFind::FindNextFile() can't get the correct file name.
Once I want to get file from HP-UX by FTP. I use MFC CFtpFileFind to find the files under a specified directory in HP-UX, but the return value of CFtpFileFind::GetFileName () is not the correct file name, it includes time (sometimes and date) ahead, such as: 19:30 Mydemofile001.txt 19:35 Mydemofile002.txt but the real file name existed are only Mydemofile001.txt and Mydemofile002.txt. Is there any problem in the low level encapsulated by MFC for FTP list files? Could anyone give me some advice? Thank you. ...

using indirect to return a named range?
I'm having a mental block on how to do this. I have a boatload of named ranges in my workbook. I have one worksheet that has two data validation cells and a graph. I want the two data validation cells to be used to select the named range to use to populate the graph series. My named ranges pull from a large variety of locations (not orderly) so I put the named range names in a table format for easier reference; here is an example with named ranges by month and location- the user selects a month and location, and the graph should use the named range with the name in the ...

I want a formula to ignore text values in cell references
If text is input into some cells referenced by a formula I want the formula to ignore whatever text it finds and just calculate the result of the numbers in cells. How do i do this? Presently the formula displays #VALUE! where there is text in one or more of the cells referenced. Use ISNUMBER(): In place of A1 use =IF(ISNUMBER(A1),A1,"") -- Gary''s Student "Russellrupert" wrote: > If text is input into some cells referenced by a formula I want the formula > to ignore whatever text it finds and just calculate the result of the numbers > in cells...

Look up column name, match rows (a/cnumber) & summing up....its confusing!
Hi All, Here is what I'v been trying to do but..! I'v 2 sets of data (data1 & data2) Column name may be sane/different in each data set and same applies to acct_no.. I want to prepare a report that combine product & accounts data show accumulatd result on another sheet/file. Data 1 - upto Jan 2006 Acct. No Total Prod - A Prod - B Prod-C 1001 51 10 30 11 1002 47 15 20 12 1003 80 20 15 45 1004 64 25 16 23 Total 242 70 81 91 Data 2 - Feb 2006 Acct. No Total Prod - A Prod - B 1002 7 5 2 1004 16 10 6 1009 9 3 6 Total 32 18 14 Report required Acct upto Jan'06 Prod - ...

Official name of the File Selection Part of CFileDialog
I want to know the official name of the file selection portion of the CFileDialog. I also want to know how this is implemented, is there a special kind of ListBox that can do multiple columns? "Peter Olcott" <NoSpam@SeeScreen.com> wrote in message news:Kw%3h.10768$IC.9812@newsfe21.lga... >I want to know the official name of the file selection portion of the >CFileDialog. I also want to know how this is implemented, is there a >special kind of ListBox that can do multiple columns? List-View http://msdn.microsoft.com/library/en-us/shellcc/platform/commctls/list...

Sorting by Color #2
Hello, Is it possible to sort in excel by color for entire row. Thanks, Manish hi, Manish ! > Is it possible to sort in excel by color for entire row. IF you mean the 'real/complete' entire row... some changes [minor I hope] has to be made.. this is 'the long way'... [trying you to provide 'only' a specific range including titles which are not sorted] the following macro will use the 'next' column for sorting numbered_colors... hth, hector. Sub Color_Sorting() Application.ScreenUpdating = False Dim myRange As String, n_Cols As Byte, n_Rows As Long...

in Excel: How do I change "selected cells" highlight color?
When I select cells in Excel the shading is so light (blue) that I can barely tell which cells are selected from those that are not. Can I change the background color of cells that are selected? Thanks. Assuming Excel 2007 this is a known problem and as yet no patch for it. I don't have Excel 2007 but I have read that playing with your Contrast can help some. Gord Dibben MS Excel MVP On Tue, 10 Jun 2008 12:51:00 -0700, flameretired <flameretired@discussions.microsoft.com> wrote: >When I select cells in Excel the shading is so light (blue) that I can barely >tell whic...

How to make A1 the active cell in all visible worksheets ?
This is a multi-part message in MIME format. ------=_NextPart_000_0028_01CA8164.76257740 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable ......so that when i activate the sheet A1 is the cell in the upper left = corner of my screen. Thanx ------=_NextPart_000_0028_01CA8164.76257740 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META content=3D"text/html; ...

Conditional Formatting dependent on whether cell is populated.
I want to conditionally format cell G2 dependent upon whether there is a value in cell C2 AND G2 Therefore, if cell C2 has a value in it and cell G2 doesn't then cell G2 color = Amber if cell C2 has a value in it and cell G2 has a value then cell G2 color = Green Any helpful hints or useful suggestions? Thanks Tel Conditional Format of G2, condition 1: =AND(COUNTA(C2)=1,ISBLANK(G2)) format Amber condition 2: =COUNTA(C2,G2)=2 format Green -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tel" wrote: &...

Sorting Alpha Numeric
I have a list of alpha numerics I wish to sort but they are coming up as the Excel 'default'. I prefer the desired result. Any thoughts? Thanks Original Sorted Desired D10 D07 D07 D09 D09 D09 D07 D10 D10 D11 D105 D11 D12 D11 D12 D20 D117 D20 D117 D12 D105 D105 D20 D117 your data isn't sorting properly because it is type text, which always sorts alphabetically left->right (so it won't distinguish between a 1 in the tens or hundreds place). I have two suggestions: 1. If...

deleting names
Is there a way to delete a person name/e-mail address from the to: bar? When i start to put a persons name in the to: box there are different names that i don't want anymore. where or how can i get rid of them. i am using Outlook 2003 You're talking about the auto-fill? Simply scroll to the entry you don't wish to have anymore and delete it. You can either right-click on the entry and choose delete or scroll to the entry and press the delete key on your keyboard. "cleaners" <cleaners@discussions.microsoft.com> wrote in message news:0718EB4B-B027-40...

Grouping Rows of Data and Sorting Help Needed?
I have been given a spreadsheet that is 12 columns wide and is up to 7 rows deep. It looks something like this: Column 1Column 2-----Column 3........Column 4.....Column 11---Column 12 ------------------------------------------------------------------------------------------------------ Part1------XXXXXX--------XXXXXXXX---------XXXXXXXX....XXXXXXXXX---Item 1 -------------XXXXXX--------XXXXXXXX---------XXXXXXXX....XXXXXXXXX----Item 3 -------------XXXXXX--------XXXXXXXX---------XXXXXXXX....XXXXXXXXX----Item 5 (Blank Space) Part2------XXXXXX--------XXXXXXXX---------XXXXXXXX....XXXXXXXXX----Item ...

eliminating blank cells
I have two sheets in a workbook. Say sheet January and Summary. Sheet January looks like this DATE SN Amount TAX OVD CBS 12.01.2007 483 3464 1559 701 1184 13.01.2007 484 3907 1758 791 -627 14.01.2007 485 241 108 49 -75 17.01.2007 9/A12 1623 730 329 104 19.01.2007 11/A2 329 839 292 88 Now I want to gather these information in sheet "Summary" eliminating the blank rows, so Summary looks like this: DATE SN Amount TAX OVD CBS 12.01.2007 483 3464 1559 701 1184 13.01.2007 484 3907 1758 791 -627 14.01.2007 485 241 108 49 -75 17.01.2007 9/A12 1623 730 329 10...

Correctly copy cells with a macro
I have a form that after it is filled out I would like to transfer the information (with a macro button) into an ongoing list on another page. Can I program a macro to paste the information on the worksheets next available line since this number will change everytime I use it as the list grows larger with every entry? Hi Amy See this page http://www.rondebruin.nl/copy1.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Amy" <Amy@discussions.microsoft.com> wrote in message news:469D477A-C72D-47F8-970F-45256CFA5EE2@microsoft.com... >I have a form that after it i...

in sheet search query to search sheet for a given cell.
I am setting up a spreadsheet to be used as a floor plan for a call center. the floor plan has over 500 stations and I am trying to create a query witin the spreadsheet that will allow a user to enter a station number and it will display the location on the floor plan. I do not want the user to be able to use the find function on the tool bars...I just want a cell that a location can be entered and then the location will be highlighted on the sheet. Any thoughts would be appreciated. You can use conditional formatting to highlight the station. For example, if the user types the statio...

sorting text & numbers
I have a large list that I want sorted like this: Ward 1 Ward 2 & 3 Ward 4 Ward 5 Wards 6-8 Ward 9 Ward 10 When I sort it, it says: Ward 1 Ward 10 Ward 2 & 3 Ward 4 Ward 5 Wards 6-8 Ward 9 If I just take out the words Ward and Wards, it makes some of the numbers into dates, and does something else to some other cells that returns a big number. Anything I can do? -- billjr ------------------------------------------------------------------------ billjr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25927 View this thread: http://www.excelforum.com/sh...

Excel for Mac sort question
Is there an add-in or easy way to sort by seven columns rather than by just three columns? Nope. You could sort 3 times in reverse order of importance. Or maybe you could record a macro when you do the 3 sorts and then just rerun that macro when you need the same sort. I don't use Mac's. Can you still record macros in your version of excel? garagecapital wrote: > > Is there an add-in or easy way to sort by seven columns rather than by > just three columns? -- Dave Peterson Hi, Here are 3 solutions if you don't want to do use a macro: 1. Sort the three least i...

How do I sort a list automatically?
I have a range of indeterminate length consisting of 3 columns (name, team, goals - it's a top-scorer list.) What I want to do is have Excel automatically sort the full range based on the Goals column whenever data is added to the list (ie new scorer entered) or edited (ie existing scorer gets another goal.) to keep the list always in order of most goals scored. Anyone got any ideas? Defoes, try this This assumes that your first row is a header row, and that you put data in columns A & B and then in C, it will sort by column C when data is put, or changed, in column C. Right ...