#### Cell Selection?

I have a colum of numbers

198
0
0
198
2

What I need, is to be able to select 3. the 198's are not going to be used
in the next part of my equation. That seems simple enough, however all the
numbers could be usable (not 198) and I need to use just the first three. Any
ideas?
 0
Utf
1/19/2006 5:16:02 AM
excel.misc 78881 articles. 5 followers.

2 Replies
1021 Views

Similar Articles

[PageSpeed] 39

One interp / way, using non-array formulas

Assuming source numbers in A1 down

Put

In B1: =IF(COUNT(\$C\$1:C1)>3,"",C1)

In C1:
=IF(ISERROR(SMALL(D:D,ROW(A1))),"",
INDEX(A:A,MATCH(SMALL(D:D,ROW(A1)),D:D,0)))

In D1: =IF(A1="","",IF(A1=198,"",ROW()))

Select B1:D1, copy down as far as required
to cover the max expected extent of data in col A
(can copy ahead of existing data in col A)

Col B will return the required results,
i.e. the first 3 numbers other than 198 in col A
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"M.A.Tyler" <Great Lakes State> wrote in message
news:0A836BCE-5AB3-480D-BB35-14FD147073F2@microsoft.com...
> I have a colum of numbers
>
> 198
> 0
> 0
> 198
> 2
>
> What I need, is to be able to select 3. the 198's are not going to be used
> in the next part of my equation. That seems simple enough, however all the
> numbers could be usable (not 198) and I need to use just the first three.
Any
> ideas?

 0
demechanik (4694)
1/19/2006 6:57:26 AM
Clarification:

> Col B will return the required results,
> i.e. the first 3 numbers other than 198 in col A

The above is provided of course, that the source col A does contain at least
3 numbers other than 198, otherwise col B would just return accordingly
whatever's there, viz.: the first 2 numbers, the first number, or blank: ""
(where there's no number in col A other than 198)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

 0
demechanik (4694)
1/19/2006 7:11:35 AM

Similar Artilces:

Creating a group of cells. Need Help Please.
Havn't used excel in a while and I need to create a group of cell corresponding to an input of a min and a max. Here are the details. On one sheet I have a box where you enter th min and a box where you enter the max. In another sheet I want column starting at A2 to output (MIN,A2+1000,A3+1000,....MAX) ho would I do this -- Thundersix ----------------------------------------------------------------------- Thundersixx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3055 View this thread: http://www.excelforum.com/showthread.php?threadid=50207 Name the...

Excel 2003 extract repeating cells
I have successfully sorted my data to show repeating entries in relation to two specific columns. I can't seem to figure out how to select these repeating entries (without doing it manually, of course) and putting them either into their own column(s) or an entirely different spreadsheet altogether. The goal is to save time in managing THOUSANDS of documents in this manner so that my colleagues can easily pick up repeating entries and take according action. Doing it manually is very time-inefficient. Nit Wit, You don't really describe enough of your layout or what you actually mean...

Combining cells to create a formula
Hi, I have two cells that I want to combine to have a working formula B1 = sum B2 = d3 b3 = =b1&"("&b2&")" D3 = 10 The result in b3 is : sum(d3) How do I get this to result in the actual value in D3. I know I can simply write =D3, but the actual reason for combining the two cells is more complicated than explained here. Thanks Hi I think you are perhaps looking for the Indirect function =INDIRECT("D"&ROW(3:3)) would return the value contained in D3 As you copy down, the formula would alter to 4, 5 etc. represent D4, D5 etc. -- Regards R...

changing the cell information #2
Hi I have files I tried to change the cell information to another cel that have to be the blank for a row that contains director name in cel A and for the movie titles I have to have director names instead of th title names.Please help me, the data in cell B sholuld have to be, A 1 D:B.Jane 2 T:Back to the future 3 T:Aliens 4 T:X-files 5 D:Adam Mathew 6 T:Core 7 T:Titanic B 1 (empty) 2 D:B.Jane 3 D:B.Jane 4 D:B.Jane 5 (empty) 6 D:Adam Mathew 7 D:Adam Mathew thanks -- Message posted from http://www.ExcelForum.com ...

Every cell is highlighted
This is a new one for me. Afer opening up Excel '98 and go to a cell, every cell is highlighted when I move the mouse. I can not put any information in any cell. The only way to quit Excel is control, alt, delete and then it gives me "can not quit excel". It does though after a while. Wherever I move the mouse, the cells are highlighted. What gives? Thanks f8 key -- Don Guillett SalesAid Software donaldb@281.com "5hulses" <5hulses@discussions.microsoft.com> wrote in message news:3B8F2E7E-8957-4B26-B64B-AEC88B6099BE@microsoft.com... > This is a new...

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/ "jimfx >" <<jimfx.109zcv@exc...

How can I wrap text across merged cells?
I'm using Excel 2000. I have a set of merged cells A5- E5. I have several lines of text in the cells and I want them to wrap across the merged cells and it is not working. All I get is the first line of text showing and the rest is cut off. It works if the cells are not merged, but I really need to do it in my merged cells. Is there a way to this other than manually resizing the height of the row? Instead of merging cells, have you tried the "Center Across Selection" option ? The appearence is just about identical to what you would get using merged cells, although I d...

Custom cell formatting
I need to create a custom format for a series of cells that will begin like this. I can't figure out what the code character is fora volitile potentially alpha character. Can anyone help me?? -Monica, Dallas 000000 000001 000002 .... 000009 00000A 00000B 000010 000011 ...... Monica something like this might work for you but you'll need to put all the leading digits in for the entry with the alpha character Regards Trevor "MDavison" <davison@fr.com> wrote in message news:#SD0tUzTEHA.1652@TK2MSFTNGP09.phx.gbl... > I need to create a custom format for a series ...

Hi, To refer to a cell with a variable row number, we can just code it as, for example, Dim i As Interger i=234 Range("A" & i).Select To refer to a cell with a variable column index, it seems not that easy because the column index must be explicitly specified in a cell address. So, if I want to go to the j th column on the 2nd row or j columns to the right of cell AA3, is there a quick and easy way to do it? Thank you in advance. David You may be able to use R1C1 terminology, but you'll need to get someone else to help from here. "cyberdude" <honc...

excel, worksheet, set print area, position selection on printed pa
Office 07, Excel. Print area is set, print preview displays selection, always on far left of page. I want to choose the position on the page, eg centered or right aligned etc. Help please. Hi, To get your printout to center on the page chose Page Layout, click the Page Setup quick launch button (bottom right corner of the Page Setup group). Choose the Margins tab, click Horizontal. There is no command to right align a printout, but you can change the left margin to force the printout to the left. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Eve Al...

How do I get text to copy from one cell to another ?
Type = in the target cell. MouseClick the cell containing text. Pres Enter key -- Brian ----------------------------------------------------------------------- BrianB's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5 View this thread: http://www.excelforum.com/showthread.php?threadid=27034 ...

Locking cell color while allowing data changes in cell
In excel 2000, I created an attendance worksheet for my classes.(Alphabetized names down left vertical column. Dates across top of horizontal row.) I added a different color to all cells in every other row to make for easier reading of each student's name and absences. Every other row stays with a white background. My question: I wondered if it was possible to lock row colors while allowing data to change on top of them. If a new student is added to my class in alphabetical order, the alternating color pattern is often lost. It is a pain to rechange row and cell colors. Any shortcut ...

Highlighted Cell
Hi all If I highlight A1 is there a way to have the cell fill colour yellow, So I can see where I am better, when I move to say A8 etc. A1 go's back to its normal colour and the new cell is yellow. Thanks in advance Dave See if this helps: http://www.cpearson.com/excel/RowLiner.htm -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Dave" <tuco@tuco.karoo.co.uk> wrote in messag...

Cannot Print a selection of ToDo's
I have a user who is having an issue when attempting to print a selection of his Todo list. The print dialog will appear but as soon as he clicks either print or print preview the dialog dissapears and no jobs are sent to the print que. He is able to print other documents to this printer and the issue occurs with all other printers installed (including a PDF printer). Computer is running Vista business 32 bit w/ Vista SP2. Office 2007 Small Business with Office SP2. ...

Changing the Selection Fields in a Combo Box
Hi, I've created a Combo Box using the Controls Wizard. In creating the control, I specified that my unique value to be stored in the table is an ID. When the user views the selection options in the Combo Box, I'd like to have the ID field hidden and only see the text values, while still storing the ID in the appropriate column. This is probably a second question. Also, when the user views a form that has been populated, I'd like them to see the actual textual values on the form, as just the stored ID value. Is that possible to do with the one Combo Box or is that...

Formatting Cells in Excel 97
Hi Guys, Sorry if I sound real stupid but is there anyway that we can control the column formatting in Excel 97 like let's say column A = GENERAL(6), column B = GENERAL(4), column C = TEXT(18) etc? Appreciate any form of advice, thanks!! I replied in the programming group. Are you seeking a programming answer? It's usually best to only post to one group, and include the remark "Please tell me if I should ask this in another group." On Thu, 28 Aug 2003 00:56:47 -0700, "Daryl" <daryl.ho@tnt.com> wrote: >Hi Guys, > >Sorry if I sound real stupid b...

Getting Cell Value from the Concatenate formula
I built a concatenate formula that returns the following result: =Jul! \$D27 I am looking for the cell contents of Sheet: July Column D Row 27. I tried to use offset, but I am stumped. Can I add something to the front of the concatenate to not only build the reference to the cell, but also return the value instead of the =Jul!\$D27 ? Thanks John =indirect(yourformulahere) Don't include the equal sign in your formula. And match the name correctly (Jul or July???). Depending on the name of the worksheet, you may need to have a string that looks like: 'Sheet 99'!d27 =indirec...

HOW DO I COPY FORMULA WITHOUT CHANGING CELL REFERENCE
My cell c1 contains the formula b1-a1.when i copy this formula to cells d1 and e1 the cells d1 and e1 have the following formula : d1=c1-b1 e1=d1-c1 but i want the following d1 should be b2-a2 and e2 should be b3-a3 how do i do this? One way... In C1 enter & copy across: =INDEX(\$A:\$A,COLUMN()-COLUMN(\$C\$1)+1)-INDEX(\$B:\$B,COLUMN()-COLUMN(\$C\$1)+1) anantth wrote: > My cell c1 contains the formula b1-a1.when i copy this formula to cells d1 > and e1 the cells d1 and e1 have the following formula : > d1=c1-b1 > e1=d1-c1 > > but i want the following > d1 should be b2-...

selecting multiple choices from a drop down list
I have a drop down list in a cell and I want to be able to selec multiple entries from that list. Does anyone know how I go this -- Message posted from http://www.ExcelForum.com If this is a data validation list, you could use code to compile a list of selected items. For example, if the data validation is in column C, the following code will store the selected items in the same row in column D: '============================= Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Application.EnableEvents = False If Target.Count > 1 Then Exit Sub On Error Resume N...

ComboBox passing values from selected
In my form (Items_frm) I have a combo box (Itemcbx) that displays 4 columns (Item, Makebuy, Revision, IsActive) but is bound to the first column. However, I would like to pass the other values from the remaining columns to other fields in my form after the selection. Is this possible? Can someone provide a sample code? You can use the Column() attribute. Me.Itemcbx.Column(1) etc. Note that the index for combo box columns is zero-based so 0 is the first, 1 is the second and so on... Steve "Angel G" wrote: > In my form (Items_frm) I have a combo box (Itemcbx) that displ...

List Box Point-to-Select (MouseMove)
I have a list box that shows files from my hard drive. I'd really like to have it select the item being pointed to as I move my mouse pointer over it. I have coded this and it works fine unless the list is scrolled. I need a way to offset my list index to account for how far down the list has been scrolled. I can't find a list box property that will allow me to determine where a scrolled list is positioned. Something giving me the list index of the first visible row would do the trick. I haven't been able to find anything so far. I've tried some VB code that uses the Send...

find match then change cell value
In column A I have cells filled with text and in column B I have cells filled with numbers. I need to check if the number in cell C1 equals any of the numbers in column B. If a match is found then I need to change the text in column A to CBO. e.g. Column A Column B Column C aep 5 7 apa 0 gci 59 xto 5000 xle 7 oih 253 ed 8 Since the cell C1 = 7 equals the 7 from column B, I need to change the data in column A from xle to cbo. Is this possible...

Insert new row as cell contents change
Insert new row as cell contents change. After importing data I have a spread sheet with a column that contains a series of alpha numeric characters. At various random intervals in this column the contents change. EG rows 1 to 4 could contain ABC, then rows 5 to 15 could become 222. I am looking for a method to insert a blank row automatically between the rows were the contents change. Many Thanks Geo George If you are familiar with VBA the code below will do what you want. Preselect the column of data first Sub InsertRowAfterValueChange() Dim myCell As Range Dim sCurrVal As String ...

HIGHLIGHTING CELLS #3
Hi I have a spread sheet in which I want to highlight certain cells automattically. ie B4 = 39 C4 = 38 how do I get Excel to highlight the figure in C4 if it is less than B4, someone said it was something to do with exceptions but I cannot find any details on that. Hope someone can help Dave You can use conditional formatting... 1) Select C4 2) Format > Conditional Formatting > Formula Is 3) Enter the following formula: =(C4<B4) Note that if you don't want C4 highlighted when it's empty, use the following formula instead... =(C4<>"")*(C4<B4) ...

Show a blank result in a cell when there is no value in the "Lookup" cell
I apologize if this question has been asked, but I have been unable to find an answer searching the topics. I'm using the following formula in cell C3: =INDEX(LastName,MATCH(A3,EmpID,0)) When I type in an employee ID in A3, his/her last name shows in C3. However, when there is no value in A3, C3 shows error "#N/A". Is there a way to show a blank cell in C3 until a value is entered into A3? Thanks in advance! Mike On Aug 5, 10:45 am, "Michael Slater" <mslater...@comcast.net> wrote: > I'm using the following formula in cell C3: > =INDEX(LastNam...