#### how to sort cells with sheet reference

```i an unable to sort the data in cells containing a reference to anothe
sheet.  it seems as though excel is treating this reference as absolut
and not relative.  is there any way to do this, such that thes
equations adjust to the sorting?  any thoughts would be appreciated.
thanks

--
vba_neophyt
-----------------------------------------------------------------------
vba_neophyte's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2418

```
 0
6/10/2005 1:27:13 PM
excel 39879 articles. 2 followers.

1 Replies
567 Views

Similar Articles

[PageSpeed] 20

```one way would be to copy and paste specail values only - although this
would require updating and refreshing each time.

Another way would be to make use of the rank function. you could then
list the results of this using index!

eg.

>data:
number	Rank	Position	Index
123	2	1	43
43	1	2	123
1234	3	3	1234

>Formulae:
number	Rank	Position	Index
123	=RANK(A2,\$A\$2:\$A\$4,1)	1	=INDEX(\$A\$2:\$A\$4,B2,1,1)
43	=RANK(A3,\$A\$2:\$A\$4,1)	2	=INDEX(\$A\$2:\$A\$4,B3,1,1)
1234	=RANK(A4,\$A\$2:\$A\$4,1)	3	=INDEX(\$A\$2:\$A\$4,B4,1,1)

```
 0
6/10/2005 3:02:13 PM

Similar Artilces:

next cell
if i have cell a1 selected in visual basic code what is the line of code to select the cell below it......ie add one to the row number? im sure this is simple!! cheers robert --- Message posted from http://www.ExcelForum.com/ one way: ActiveCell.Offset(1, 0).Select In article <robert_woodie.11228s@excelforum-nospam.com>, robert_woodie <<robert_woodie.11228s@excelforum-nospam.com>> wrote: > if i have cell a1 selected in visual basic code what is the line of code > to select the cell below it......ie add one to the row number? > > im sure this is simple...

Drop Down List that has formatting of cell range Font ect.
What I am looking to do is create a drop down list that when it is clicked on the options reflect the formatting of the entries in the cell range not just the wording. For example if cell C5 is in Red font and cell C6 is in blue font then the drop down would be in red and blue font also. It would be even better to use the cell fill color of C5 and C6. I am setting up a form that I would like to be able to just glance at and see what task needs to be done next and color seemed to be the easiest way. Thanks Tom You could do some conditional formatting of the cell with the drop down ...

Listbox NOT in cell
I have a row of cells (row 2) that is aligned vertical and is narrow. I want to have a listbox for data dropdown but because you have to check "In cell dropdown" the list is unreadable because I only see 2 characters. Unchecking the box doens't give me a dropdown icon. Is there a work-around so I can see the whole list? Thanks to all who reply. -- Ray Tweedale All-around-nice-guy ...

Identify Cells
Hi, How can I place an identifier in a cell to identify members of each group, so when the spreadsheet is printed, the reader can see what cells are members of each group. For example, if A1 and B7 are members of group 1, I would like to place the number 1 in the upper left corner of each cell, in this case A1 and B7. This would be similar to the upper right corner marking when a cell contains a comment. Thank you, Nick AFAIK not possible. Suggest color-coding. HTH Jason Atlanta, GA >-----Original Message----- >Hi, > >How can I place an identifier in a cell to identify m...

Format Cells #11
In one particular worksheet If I right click on a cell and click on "Format Cells..." the format cell window does not show up. The worksheet is not protected and there is no conditional formatting. If I create a new worksheet, rt. click on a cell and select format cell.. the popup works fine. what is a-miss with this spread sheet? ...

Conditional Cell Protection
In Excel, is there anyway to protect a cell only if it meets a certain condition? This can be achieved through a macro, however, you would need to store the password in the macro to unlock the sheet in order to make the changes - assuming you have locked the sheet. You would need to write the code in the VBA object for the sheet in question, then using the macro: Private Sub Worksheet_Change(ByVal Target As Range) 'find if condition is met for the cells in question then lock the cell ' assuming the cells in question are in B1:B5 Activesheet.Unprotect Password:="myPassword...

Still need help with a code reference problem
I have a problem (in fact several) with a workbook. Under certain conditions (absolutely reproducable) the sheet seems to reference a code block that does not exist - I have a pic of the window that appears here; http://www.jimsthings.com/excel/Fill_Rota.png the workbook that produces the error is here; http://www.jimsthings.com/excel/8_test.xls the sheet affected is the open sheet (Timesheet) - if I click on any of the cells A:44 to A:60 (approx) I get the error window shown in the picture above. Several people have helped by saying that this seems to be at least part...

Insert blank row after change in cell value not working.
I have used the Gord's VB code posted on here to insert a blank row after each change in cell reference without problem several times. I now have a worksheet that will not run it correctly. It now inserts a blank row if the cells text colour changes, although when I paste these different cells into another worksheet using edit special paste = values the cells justify either left or right depending upon their colour. The different colours come from different worksheets so I assume it must be something to do with their formatting but the properties for each one appears the sam...

Sheet name changing dilemma
So I have a workbook with 9 sheets of data and 9 more sheets of charts and graphs that feed off of that data. The nine sheets of data rotate each week. Week 1 of data rolls off and a new week 9 of data rolls on. I'm working on do this via a macro. Here's the problem: the two options I know of are to copy and paste all of the data from each sheet to next sheet (i.e. copy week 2 data and paste on week 1 sheet, copy week 3 and pasted on week 2, etc.) OR I could have the macro just rename the sheets. For obvious reasons that would be the preferred method. However, it screws...

possible to equisize charts (6) in chart sheet?
My chart sheet has six charts on it; arranged in two columns of three charts each. The "snap to grid" feature is nice yet I am having difficulty getting all six of the charts to be the same size/dimension. Is there a shortcut for doing this or is it necessary to simply "eyeball" the heights and widths of the charts? I am hoping there is a way to automatically "tile" the charts. Thank you. Andrew - I usually use a regular worksheet for this, a blank one without all those numbers cluttering it up. When you drag and stretch charts on a worksheet, holding...

Visio shape reference
I've just started using Visio. I've been looking but I can't discern if there is a standardized listing of shape meanings. For example, if the trapazoid means a process. I've tried different strings to see if I can find something that references what the shapes mean but apparently I'm not wording it properly. I've tried looking on the MS website but again there doesn't seem to be a reference. Please help. Denyse try here... http://deming.eng.clemson.edu/pub/tutorials/qctools/flowm.htm#Overview al "Denyse" <Denyse@discussions.microsoft.com> wro...

If with reference
Hi, I have workbook as follow: A1 is "Employee" or "Spouse" or "Dependent" B1 is age of A1 I wish in C1 : if A1 is "Employee" or "Spouse", result will be Yes But, if A1 is "dependent", then should refer to the B1 column, which is, if age is less then 20, result also Yes, otherwise No. Could you advise me? Regards, Pran Try in C1: =IF(COUNTA(A1:B1)<2,"",IF(OR(A1={"Employee","Spouse"}),"Yes",IF(AND(A1="dependent",B1<20),"Yes","No"))) Copy down Ok? Cl...

want show result in spread sheet, in the form
Hi all, I have a form with a combo box , listed with 5 items. when each item is selected it will execute a query and result will in spread sheet window like how normally appears when a query is executed. but I want to add some thing like spread sheet, in the form at the bottom. when a query is executed the result should appear in the bottom attached spread sheet. pls help me here in 2 things. 1. where and what should i add at the bottom which seems like spread sheet and how to add it. 2. how to make possible the result will appear on it. thanks in advance.... ...

Need help on exporting an Excel Sheet into Access 2000
I budget for a Project in an Excel sheet as illustrated below. The months below are usually a 2 year period i.e. 24 months, though it could be over 24 months depending upon a Project. I then need to input this in an Access database, where I do a comparison with the Actual cost. The table &#8220;TblBudget&#8221; in Access is made of 4 fields, namely: (1) CostElement (2) CostCenter (3) Month (4) Amount\$. At the moment this method is very cumbersome. I have to manually input the data one by one rather than a direct input. The reason being as my spreadsheet is in a tabulated format. ...

Pasting text into one cell (Excel wants to dump in multiple cells)?
<lol> I can't believe I've never asked this one either? Opportunity never came up ... Every once in a while I need to paste paragraphs of text into an Excel cell (i.e., comments from one source into a log). And Excel naturally takes the data and puts each paragraph into its own cell even if in same column. Is there a way for Excel to ignore all the hard returns as prompts for a new cell and instead to treat them as ordinary returns so that I get the comments in paragraph- form into one cell? I hope my question is clear (?). <g> Thank you! Hi Before you paste...

Counting Cells with time value
Hi, I wish to count the times that a column (lets say column 'C') shows a time that is before 12:00, but only count this cell if the value of the cell adjacent is a certain number. example :- Coulmns A B C Row 1 3 11:23 Row 2 2 09:00 Row 3 3 14:55 So in the example I want to show how many times the value in column C is before 12:00 when the value in column B = 3 - the answer given would be 1 Hope this is clear, and thanks in advance Anthony one way: =SUMPRODUCT(--(B1:B3=3),--(C1:...

comparing and compiling between sheets
Hello everyone, I have two copies of the same spread sheet. The original and a edited version. The edited version has one of each row that was used on our project. The problem is that whoever edited it removed columns as well. I would like to make a copy of the original and compare that to the edited one by the use of one common column. Then, delete any line that does not have a matching entry in the edited sheet. So for example, in column A (titled part number) of the master I have 100, 101, 102, 103,104 In the edited column A I have the values 100, 102, 103. Is there a way to co...

sum by skiping one cell
I want to sum only even cells from A1 : A1000 I used =a2+a4+a6+a8+…………..+a1000 Used this formula n cell b1 But now I want to solve this problem by some short formula Help me thank you See your post at worksheet.functions. Faisal Yameen wrote: > I want to sum only even cells from A1 : A1000 > I used =a2+a4+a6+a8+…………..+a1000 > Used this formula n cell b1 > But now I want to solve this problem by some short formula > Help me thank you > or even here -- HTH Bob Phillips "Aladin Akyurek" <akyurek@xs4all.nl> wrote in message news:41F3C09D.606080...

Flexible Cell Reference
To explain this I will use the R1C1 notation, but I would need to implement it in A1 reference style. Is there a way to make a "flexible" cell reference such that I could have a value in A1 such as 3 and then have a cell reference somewhere else in the sheet that was something like =R[-2]C[x] where the "x" is referenced to cell A1? So, combining two different reference styles (please forgive me), the formula would look something like =R[-2]C[\$A\$1]. The reason I would like to do this, if it helps explain my question, is that I would like to predict the development ...

copy formatted (red font) cells from one worksheet to another
How do the result displayed and updated in a cell on 'worksheet 1' mirror or copy the red font color (and strike through) of a cell on a seperate worksheet automatically? - 'worksheet 2'? on worksheet 1 it is currently black, but when I cross it off (red font and strike through) - I want it to update this action / result to a master list so that I dont have to update both worksheets. thanks. I assume that you have formatted the cell on sheet1 the way you want i (red font strike though etc). Copy this cell and go to the mastershee and select PASTE SPECIAL. Select FORMA...

Grabbing the last Non-empty cell in a range
Howdy all y'all. I have a spreadsheet that totals the daily production, keeps a running total of production everyday, and also shows the average daily production. The first of these is totaled at the bottom... but the second two should simply reflect the values of the very last cell with data in it. How do I set a cell to display the number in the last non-blank cell of a certain range of cells? I don't even know how to word this question to the assistant, Clippy. Thank you very much for the predicted help. Arlen Try this: =LOOKUP(9.99999999999999E+307,A:A) -- HTH, ...

position() in XPath has wrong reference
Hi, my XML-Document hat this structure <book> <section> <para /> <para /> </section> <section> <para /> <para /> </section> </book>... Now I need to find the third para-element of all para-Elements. In my XSLT-Stylesheet I write <xsl:template match="para"> <xsl:if test="position()= 3" > do something </xsl:if> </xsl:template> This does not work. He finds nothing. When I test position()=1 he finds the first para of the first section and the first pa...

Add a button to a sheet to open a userform
I'm looking for a way to add a button to a spreadsheet that will open up a userform. I've seen it done before, I'm just not sure how do it. Thanks, Chris -- PokerZan ------------------------------------------------------------------------ PokerZan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23480 View this thread: http://www.excelforum.com/showthread.php?threadid=380247 Hello Chris Add a Command button to Excel double click it so it takes you to the vb code & enter UserForm1.Show (between Private Sub CommandButton1_Click() & End Su...

External Reference #2
Hi, When I open a particular spreadsheet, the "Do you want to update External References...?" box appears, however I cannot locate the cells that refer to the external worksheets. I have tried a find on the file names listed in the links box, however there is no result. Is there a way to find the cells that refer to another file in Excel 2000? Thanks, http://www.bmsltd.ie/MVP/MVPPage.asp You need a file called filelink.xla an excel addin which will help you to find and delete links, it can be downloaded from the above. Rick "Iain" <Iain@discussions.microsoft.co...