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
613 Views

Similar Articles

[PageSpeed] 44

"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:

Click in cell w/ formula and get colors in referenced cells
Hi, When you click in a cell with a formula, Excel will then put color around the cells that are referenced. Somehow I turned that feature off. Now I do not get colors in th other cells. Where is that option to turn it back on???? I've looke and looked but I just can't find that option. Thanks for the help -- albea ----------------------------------------------------------------------- albean's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2875 View this thread: http://www.excelforum.com/showthread.php?threadid=48436 ...

Avoid protected cell warning on BeforeDoubleClick WS event
I am creating an event scheduling worksheet. A grid is generated with days of the month x-axis and users y-axis. Where an event occurs this is logged by a hidden event ID in the appropriate day cell. In order to prevent this ID from being overwritten I protect the worksheet. What I am trying to do is have a worksheet DoubleClick event which either. 1) Captures the event ID in the underlying cell, and opens a custom form for editing the event, or 2) Recognises that no event exists and opens a custom form for logging a new event. What I have tried is to unprotect the workshee...

Miscellaneous words appear in Excel 2002 cells
I am working on an Excell 2002 workbook and when I put my cursor in a cell , spurious words get entered into the cell e.g. "and thee the and ...." is one example. I have cable modem and it feels like "someone" has taken control of my PC - by the way I have also noticed this hapening in MS Word but is not as bad as Excel - can some one help. thanks KK Have you enabled speech tools? Click Tools / Speech and examine your options. /i. "KK" <anonymous@discussions.microsoft.com> wrote in message news:061d01c39d1c$36fc85f0$a601280a@phx.gbl... > I am w...

First row in Selection range (first index of a cell)
Hello, I have a problem with selecting first cel in selection Range or return an index of the first cell in Selection Cell. I have something like this: .................... Range1.Select "and here I want to Select the first range in selection Range1" ............. I there any special function of finding first cell in selection range or returning an index of the first cell?? Thanks for answet Marcin Maybe range1(1).select or range1.cells(1).select or range1.cells(1,1).select mar_male@wp.pl wrote: > > Hello, > I have a problem with selecting first cel in selection...

How do i sort contacts using more than one catagory ?
I am trying to create lists which have contacts sorted by more than one catagory. So including contacts that are linked to two or more specific catagories. Can this be done, if so how? thanks "samong" <samong@discussions.microsoft.com> wrote in message news:01E99022-AB21-487B-9365-2E66BB0C3F1B@microsoft.com... >I am trying to create lists which have contacts sorted by more than one > catagory. So including contacts that are linked to two or more specific > catagories. Can this be done, if so how? The Category field is non-sortable because it is a multi...

Sorting #11
How do you sort a list with more than three sort keys? First sort on least significant key(s) Then sort on most significant keys HTH -- AP "Fran" <fpeale@comcast.net> a �crit dans le message de news: %23pS%239BXeGHA.1856@TK2MSFTNGP03.phx.gbl... > How do you sort a list with more than three sort keys? > You need a helper column. In this should be a formula concatenating all the fields to be sorted. =A1&B1&C1 ... This will sort only in ascending order. You will need to include the value of dates (not the actual dates) - &VALUE(A1) or &...

Sum of a cell in all worksheets?
I usually just use the search here and quickly find what I need, but can't seem to get it work this morning I am trying to get a sum of a certain cell in all the sheets in workbook. For example of what I mean, Sum(all worksheets, d64). Ca someone possibly help me out Thanks Michae Hi Michael, Look up 3D in your Excel Help. Surely you do not want to include the same sheet. if sheet2 is your second sheet tab and "sheet 24" is the last tab. =SUM(sheet2:'sheet 24'!C14) --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: htt...

Insert a graphic in a cell
I'd like to be able to enbed a logo in a cell. Not possible. Graphic objects reside on the drawing layer "above" the cells. Cells can contain formulae or values only. Workaround. Insert your graphic, and size it so that it exactly covers the cell. Right-click it, choosing Format Picture. In the Properties tab of the Format Picture dialog, choose the Move and Size with cells radio button. In article <F253C07B-E71A-445E-B612-0189187A09D9@microsoft.com>, Pete_Escher fan <Pete_Escher fan@discussions.microsoft.com> wrote: > I'd like to be able to enbed a...

name appearing in the outgoing mail
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop I would like to change the way the name in each of my accounts shows up in sent emails. I have changed the account settings but the old name still shows up. Any suggestions? <br> Thanks. On 2010-05-04 16:45:26 -0400, Bila@officeformac.com said: > I would like to change the way the name in each of my accounts shows up > in sent emails. I have changed the account settings but the old name > still shows up. Any suggestions? Well if you have indeed changed the name prop...

How can I compare the column names from 2 tables and output them?
I have a requirement to place an alert if the field does not exist in my table, tblStaging. Can any one guide me to reframe this query with error handling messages? I know it is not good practice to use select * but I need to do this as my columns\field names change each time. INSERT INTO tblStaging SELECT * FROM tbl_XL,tblDetails; Thank you -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1 Access is a relational database. If your "table design" has the fields in your table(s) changing frequently,...

How to reference workbook name in hyperlink formula?
In order to simplify the maintenance of workbook formulas I need to reference workbook name in hyperlink formula =[myWorkbok.xls]mySheet!$E4 in this way =[A1]mySheet!$E4 given the file name stored in cell A1 Cell A1 : myWorkbook How do I write the formula to make it work? Will it work even if the referenced workbook is closed? Regards Frank Krogh The function you'd want to use is =indirect(). But =indirect() won't work if the sending file is closed. Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm ...

can one cell contain more than one independent number
A cell can contain several numbers, separated by space characters or line breaks (Alt+Enter). However, if you want to perform calculations on the numbers, it's best to keep them in separate cells. John K wrote: -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html there's also the option a using matrix formula (list of data) for example you may enter ={1;2;3} in a single cell or selection 'and validate wih CTRL+SHIFT+ENTER but the manipulation of this kinda of formula need to read more about it... ...

Ignore Blank Cells
I update a workbook weekly in separate worksheets. My master worksheet links to the appropriate cells for the updated data. These are simple percentage numbers (not forumlas, etc.) and only need to be a one-on-one link. The problem I have is that if one of the cells is blank, it returns a 0 and I need to to stay blank. Any ideas? There are about 57,000 cells I am working with so I really don't want to have to update manually all blanks. Hi SEF, As long as you want to hide all zero values on a sheet you can use in excel 2003 From the Tools menu select Options On tab V...

Conditional formatting / blank cells
Hello, I need help with a Conditional Format. This is my worksheet. Row 4 A B C D E F G H I $200 $210 I want a conditional format in G4 that states if G4 is greater than or equal to F4 the fill colour is green. If G4 is less than F4, the fill colour is red. If G4 is blank, the fill colour is white. I've tried numerous combinations, but cannot seem to get this to work. Thanks torkattack. Test for the blank first. -- David Biddulph "torkattack" <torkattack@discussions.microsoft.com> wrote ...

sorting by last char
Hello, I have a table where in a column there is something like: 1a, 1b, 1c, 2a, 2b, 2c and so on I wish to sort by the last char to look like: 1a 2a 1b 2b 1c 2c I tried Tools/Options/Custom List using *a,*b,*c to no avail. Any ideas? Thanks You can extract that last character into a helper column using: =3DRIGHT(A1) and copying down as needed. Then you should include the helper column within the data range when you sort, and sort on the helper first, followed by your other column. Delete the helper column when you've finished with it. Hope this helps. Pete On Sep 18, 12:52=A0am,...

Sort search results by date
It would be nice if i could sort my search results in this forum by date. I would like to see my most recent questions at the top. -- Sheri Salomone THANKS! ---------------- 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 suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroup...

Clipart will not scale with array of cells
I want to print out an array of cells, some of which contain clipart. When I try to scale up the array to fill the printed page, the clipart scales differently (the clipart objects move down on the page from their normal position within the cells). The artwork size does not change, nor its spacing; it's as though the clipart is reacting to a larger top margin. ...

Changing the names of fields in tables after creating other object
I just leaned about the naming conventions after I have created my tables, reports and several queries. Can I change the name of fields in my tables (to remove the spaces and give them unique names ie not just last name but childlastname) without destroying the work I have done in queries, reports, and forms. Mary -- Positive Direction for Youth & Families, Inc. (www.pdfyinc.com) Possibly. If you are using a newer version of Access, say 2003 or 2007 AND you have Name Autocorrect, and all it's options, enabled, it MIGHT work. I found it somewhat buggy. Before you...

formula for visible cells
Can I copy a worksheet so that the new worksheet looks exactly like the visible portion of the old worksheet. For example, if I filter and sort, I only want what is left visible to appear in my new worksheet, and I want it to do it automatically without my having to copy and paste. Automatic means VBA code. See Ron de Bruin's site for copying filtered results. http://www.rondebruin.nl/copy5.htm Gord Dibben MS Excel MVP On Tue, 4 May 2010 11:32:01 -0700, jpstormy <jpstormy@discussions.microsoft.com> wrote: >Can I copy a worksheet so that the new workshee...

How do I copy the wording of a cell into a tab?
I have a large workbook with the tab titles available to copy to save typing every single one, is it possible to populate the tabs by a form of copy-paste You want your sheet tab name to reflect the value of a particular cell within that sheet? You want to copy a sheet and rename the copy to the value of a cell within that sheet? Please explain in more detail. The lack of sentence structure makes it difficult to get a clear picture. Gord Dibben MS Excel MVP On Sun, 24 Jan 2010 11:18:01 -0800, Jamie <Jamie@discussions.microsoft.com> wrote: >I have a large wo...

Hold a cell range for an "average formula"
I add a column every week to my work sheet and want the formula to always calculate the current 5 column range. Wendy Akers wrote: > I add a column every week to my work sheet and want the formula to always > calculate the current 5 column range. > > Add a column WHERE? What "formula"? Where is the "current 5 column range"? Hi, Let's say your data is in range C3:H3. In cell K3, enter the following array formula (Ctrl+Shift+Enter) to get the average =AVERAGE(OFFSET(INDIRECT(ADDRESS(3,MAX(ISNUMBER(C3:J3)*COLUMN(C3:J3))+1)),0,...

Pivot Table VBA Reference Book & Blank Cells
Hi Everyone, I have a Pivot Table in Access 2003 and need the blank/null cells on the data axis to display "0". This is easy to do in Excel but I have searched through all of the options in Access and cannot find it. What is the VBA code required to set this option? Can anyone suggest a good reference book or website for coding Pivot Tables with VBA? Many thanks, David ...

count in cell
Does anybody know how I can count the number of characters in a cell, I know how to use the 'count' and 'counta' function to count the number of cells used but not in a cell. Thanks =LEN(A1) -- ---------------------------- Mauro Gamberini "Joe" <someone@microsoft.com> ha scritto nel messaggio news:%23sMRof72EHA.1404@TK2MSFTNGP11.phx.gbl... > Does anybody know how I can count the number of characters in a cell, I > know > > how to use the 'count' and 'counta' function to count the number of cells > > used but not in a c...

Auto copy and insert a defined number of rows as defined in a cell
Hi I don't know much about VBA but could probably work something out. I need to produce carton labels from a spreadsheet using Word to mail merge and the labels need to include 1 of 10, 2 of 10 on them. The number of labels required is dependant on a number which is included for each row of data in the spreadsheet. What I am doing at the moment is copying each row and insert pasting the additional number of rows required. In the new rows I then add 1 of 10 in the first row, 2 of 10 in the second row etc. etc. This then enables me to perform the mailmerge. I have around 300...

Exchange 2003 with wrong domain name
I was hoping someone could give me some advice on an issue we are having. We have an Exchange 2003 and DC(Windows 2003 Server) server behind a Cisco PIX 506e router. Everything works great, however the company that installed the servers for us, named our domain wrong. So our root domain is not correct. However, this has never been a problem until now. Now more and more companies are using some type of reverse lookup to make sure the domain name matches up with the IP address. Well, since our domain name was named wrong, the external address of our firewall, does not match our domain name. ...