Lookup selective from another sheet

Assume I have one sheet as below. How can I create a new sheet and display 
only those entrys that are greater than a entered value.

i.e main sheet

abc  2
def   5
ghi    6
jkl    5
fgh  3
krk  4

on second sheet, if 4 is entered only entrys >4 are shown. i.e

def   5
ghi    6
jkl    5
krk  4



0
Forrest1 (1)
10/15/2005 6:09:58 PM
excel 39879 articles. 2 followers. Follow

2 Replies
764 Views

Similar Articles

[PageSpeed] 30

Try something like this:

C1=IF(VLOOKUP(A1,Sheet2!A$1:B$4,2,FALSE)>Sheet1!B1,VLOOKUP(Sheet2!A$1:B$4,2,FALSE),NA())

Your main sheet is A1-b6 and Sheet 2 is A1:B4

"Jim" <Jim Forrest@hotmail.com> wrote in message 
news:WBb4f.34203$U9.12412@fe3.news.blueyonder.co.uk...
> Assume I have one sheet as below. How can I create a new sheet and display 
> only those entrys that are greater than a entered value.
>
> i.e main sheet
>
> abc  2
> def   5
> ghi    6
> jkl    5
> fgh  3
> krk  4
>
> on second sheet, if 4 is entered only entrys >4 are shown. i.e
>
> def   5
> ghi    6
> jkl    5
> krk  4
>
>
> 


0
reply6075 (299)
10/15/2005 6:28:18 PM
Another play to try ..

Assume this in Sheet1, in A1:B6
> abc  2
> def   5
> ghi    6
> jkl    5
> fgh  3
> krk  4

Put in D1: =IF(Sheet2!A1="","",Sheet2!A1)
Put in C1: =IF(B1="","",IF(B1>=$D$1,ROW(),""))
Copy C1 down to say, C10, to cover the max expected data in col B

In Sheet2
------
The input cell for the value is A1
Enter in A1: 4

Put in A2:
=IF(ISERROR(SMALL(Sheet1!$C:$C,ROWS($A$1:A1))),"",
INDEX(Sheet1!A:A,MATCH(
SMALL(Sheet1!$C:$C,ROWS($A$1:A1)),Sheet1!$C:$C,0)))

Copy A2 across to B2, fill down to B11
(cover the same range size as done in col C in Sheet1)

The desired results based on the number entered in A1
will be returned from Sheet1 within A2:B11,
all neatly bunched at the top, viz. you'd get:

> def   5
> ghi   6
> jkl    5
> krk  4
(Blank rows below)

Note that the assumed criteria placed in Sheet1's col C
is to select only entries in col B >= 4,
and not .. "only entrys >4" as posted

Adapt to suit ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Jim" <Jim Forrest@hotmail.com> wrote in message
news:WBb4f.34203$U9.12412@fe3.news.blueyonder.co.uk...
> Assume I have one sheet as below. How can I create a new sheet and display
> only those entrys that are greater than a entered value.
>
> i.e main sheet
>
> abc  2
> def   5
> ghi    6
> jkl    5
> fgh  3
> krk  4
>
> on second sheet, if 4 is entered only entrys >4 are shown. i.e
>
> def   5
> ghi    6
> jkl    5
> krk  4
>
>
>


0
demechanik (4694)
10/16/2005 10:14:50 AM
Reply:

Similar Artilces:

No record selection pleaseeee ...
Hello: I have a form with 2 sub forms in/on it. For whatever reason, on both sub-forms, 1 record on each is active or current ( the right triangle ). Is there any way to not have a current record selected or active? I guess there has to be a current record or BOF flag, but some of us are strange people and I was just wondering if it is possible not to have it. Thanks In news:eTb3edQ4HHA.5424@TK2MSFTNGP02.phx.gbl, John DOE <someone@somewhere.com> wrote: > Hello: > > I have a form with 2 sub forms in/on it. For whatever reason, on both > sub-forms, 1 record on eac...

Table relationships and lookups
Hi guys, I may be a little over my head, I've had some experience in creating simple access db's. however this one will be extremely complicated as far as I can tell. Some backround info - i've got an excel spreadsheet currently that i would like to convert to Access. The spreadsheet does multiple lookups and calucations. This is for a Soccer club that i run to maintain roster information, dollars, scheduling and stats. I'm currently working on the scheduling pience. Here's what I have so far. tables. Club - Lists the teams in the club, home field name and ...

Invoice lookup by paid check number
I frequently have vendors call me asking for information about what invoices were paid by a check number. Is ther an easy way to look this information up? -- Rodger You could go to Inquiry>Purchasing>Transactions by Document, put your check number in the 'from' and 'to' fields. Once the document is displayed in the scrolling window zoom back on the 'Unapplied Amount' field. Viola! the documents paid by the selected payment are listed. Unfortunately there isn't a print icon on this inquiry window, but I think it's the information you wanted. &quo...

Language selection does not take
I use Windows XP, MS Office 2003 Pro and for the Proof languages MUISETUP.EXE. I install English and German. When I go to Outlook and puporsely spell something incorectly in German (after highlighting the paragraph) then select Language as German. then go back to Tools and Spell Checking the window below goes by default always to English and when I look for German, it cannot even find it. Lots of French, then Spanish, no German. YET - that is what I installed and what I selected under Languages when I go to Tools. I cannot understand it because before I had a computer crash and had t...

excel extract data from one sheet to another
a b c Name Vault Rank Blake, Jasmine 7.900 1 Fattouche, Chloe 7.600 3 Fattouche, Nicole 7.700 2 Pratt, Erika 6.100 5 Salem, Alexandra 6.300 4 Mills, Sara 7.700 2 Ranked compares each via =SUMPRODUCT(--(C5<$C$5:$C$148),1/COUNTIF($C$5:$C$148,$C$5:$C$148&""))+1 This data is on one sheet and already calculated the rank. Need to pull the top three and put on seprate work sheet. Including ties. Name Vault Rank Blake, Jasmine 7.900 1 Fattouche, Nicole 7.700 2 Mills,Sara ...

Retroactive filter between two sheets?
I have two sheets - one with rows A-I and ID numbers 1-9 (like this: http://img.photobucket.com/albums/v294/greenerton/excel1.jpg ), another with the same ID numbers 1-9, but instead of the lettered row, one with various colors (like this: http://img.photobucket.com/albums/v294/greenerton/excel2.jpg ). I have a filter on the second sheet so I can sort by color to see all the IDs that have that color (IE red has 1,3,4,8), and vice versa with the numbers. How would I set it up so that I could sort the second sheet by the various colors and have Excel simultaneously sort the first sheet in the sa...

Select range for function in a cell
Hi, is it possible to select create a formula in a cell. such as =SUM( and have the cursor be between the () so the user can select th appopriate range to enter? Or would this just be done separately. (Since one would need to know the range and WHERE to put the formul in..ie. what cell) similar to if you hit the autosum button but ther are no numbers above or the left, it simply waits for the user t select a range and then hits enter. THanks! -- Message posted from http://www.ExcelForum.com Hi if I understood you correctly: No -- Regards Frank Kabel Frankfurt, Germany > Hi, is it p...

Lookup
Q103 Q102 Q202 Q302 Q402 Q103 Q203 How can I lookup the Q103 in the row above and then have it pull the number to the right one cell (Q203)? thanks If I understand correctly =INDEX(A2:F2,MATCH(A1,A2:F2,0)+1) where q103 is in a1 and q102-q203 is in a2-f2 Lance >-----Original Message----- >Q103 > > >Q102 Q202 Q302 Q402 Q103 Q203 > > >How can I lookup the Q103 in the row above and then have >it pull the number to the right one cell (Q203)? thanks >. > matt wrote: > Q103 > > > Q102 Q202 Q302 Q402 Q...

Help with LOOKUP function
This function is in a workbook with 2 sheets. It _almost_ works perfectly. These "C" columns in two different sheets '2005-2006'!C:C,'2004-2005'!C:C, contain names of people. The D column in one of the sheets - '2004-2005'!D:D - contains a date associated with the person's name from the C column of 2004-2005 sheet. This formula is in the "D" column of Sheet 2005-2006. =LOOKUP('2005-2006'!C:C,'2004-2005'!C:C,'2004-2005'!D:D) The concept is for the formula to lookup the value (person's name) in column C of 2005-2006 a...

Excel Selective Cell Protection
I am using xl2k AND xl2002 (one at work, the other at home). I want to selectively protect a couple of different cell ranges on five different sheets. The file will be made into a template so I need this code to run everytime the template is used, or everytime the xls file is re-opened. I've tried a method, but I have to jump through hoops if I want the code to remain permanently active. Can someone help? Thanks Select the cells you want *unlocked* then "Format Cells->Protection" and de-select the "Locked" option. By default the option is "Locked". I...

A Lookup function does not work
Hi, This is my first posting. I am using Exel 2000. I have 2 separate spreadsheets that have some similar columns but not all of the data in the similar columns is the same. What I want to do is take column A in spreadsheet#1 and find this same value in Column B in Spreadsheet#2 and then insert into column 3 in spreadsheet #1 a value from a different column in spreasheet #2 that corresponds to the row in which the value was looked up in Column B in spreadsheet#2. What I am doing is comparing 2 different inventory files that have stock codes in columns and quantities in another column, but n...

Selecting a range of cells based on the date.
Hi MS Office Help, I would like to know how to choose a selection of cells based on what month it is. For instance, if the month was january, excel would allow me to select cells A1-F1, which I could then use as a data source for a chart. If the month became march or april, excel would allow me to select cells C1-H1, D1-I1 respectively, and so on. This will allow me to vary my chart automatically as the month changes. Help is greatly appreciated. Hi Solomon, Use the Data - Autofilter -custom feature or Data - filter - text filters - custom depending on your version of...

return cell reference in a table based upon given lookup criteria
Is there a way to return the cell reference, or column/row coordinates, of a cell within an array or table by providing lookup criteria? Perhaps something like this: For a table of value in A1:E10 F1: (the value to find) G1: =ADDRESS(MAX((A1:E10=F1)*ROW(A1:E10)),MAX((A1:E10=F1)*COLUMN(A1:E10))) Note: Commit that array formula by holding down the [Ctrl][Shift] keys and press [Enter]. That formula returns the address of the 1st cell containing the value in F1, or #VALUE! if there is no match. Am I on the right track here? *********** Regards, Ron XL2002, WinXP-Pro "Travis" ...

Advanced Lookups
Is there any way to make an advanced lookup the default lookup? so you don't have to always choose that option when doing a lookup? Thanks for any help. Tracey D Advanced lookups ARE the default unless you've done something to make it now so. There isn't any way to "choose" the option when doing a lookup that I know of unless you have some type of customization (easy to do) that would give the user an option. patrick dev support -- This posting is provided "AS IS" with no warranties, and confers no rights. "Tracey D" <...

Calling employee lookup from button through VBA code
Dear All, Can anyone show me how to call an existing GP employee lookup from a button of a modified form through VBA code. Thanks in advance. -- Developer Hi, If I'm understanding the question - you need to add the lookup button to your project and make sure your project provides that it runs on the modified form. Leslie "Dexdev" wrote: > Dear All, > > Can anyone show me how to call an existing GP employee lookup from a button > of a modified form through VBA code. > > > Thanks in advance. > > -- > Developer Hello Dexdev As per...

Getting Sheets Copied From One Workbook to Another Without ....?
Hello, I have a situation where I want to move 3 sheets from on workbook to another. The Problem is that the sheets appears to carr their File Path with them creating a Problem for my formulas within th destination sheet.. Is there a way to keep the Formulas in tact to represent th destination sheet? The Workbooks have the same Data, but 3 sheets from the source workboo need to be inserted in the destination workbook without paths in th formulas leading back to the source workbook (file). eg. Source workbook sheet1 A1 Reads: =IF(DAY14!B33>0,DAY14!C33,"") When it is copied ...

Vendor Lookup
One doing the vendor lookup - one user sees the 'show details' information upon lookup; other user sees the vendor list and needs to clik on the show details - how do you get the show details window to be the default option you see. Thansk! Check for full stops/periods/dots on the window title bar before or after the window name. It is possible to use VBA or modifier to open the details automatically. David Musgrave [MSFT] Escalation Engineer - Microsoft Dynamics GP Microsoft Dynamics Support - Asia Pacific Microsoft Dynamics (formerly Microsoft Business Solutions) http://www...

Lookup #4
I am trying to label my bank info that I import into Excel. I have a column with the charge amount and a column with the recipient. I think a lookup table would work for this, but I need to have it check for wildcards. For example, the grocery chain is numbered here, so one might be GROCER 5454 and one may be GROCER 8724, so I need the function to look for GROCER*, right? Also, if it does not fit any category, how can I get it to use "Other"? Do I make that one just "*"? Mine is not working very well. Maybe my syntax is wrong. Any ideas? TIA Is there anyway you can st...

Multiple SQL Queries on one sheet #2
I am editing the last SQL query on a sheet that has 5 queries on it. I am adding a column. Excel is insisting on shifting columns for the queries above over, leaving blank columns. I do not have this problem if I start with a blank sheet and add the queries from scratch. Anybody know how to turn off this behaviour? P.S. - Unrelated, but how in the heck do I change the datasource for an existing query? Hi, Q2. Goto control panel, administrative tools, datasources Q1. I think if you right-click your query cell, select Data range properties..., and in that box look at the formatting sec...

Filter message by reverse domain lookup
Hello all, I am kinda new to Exchange and was wondering if there was a function built into Exchange 2000 that would "look" at the source domain of the email message and then perform a "reverse" DNS lookup to verify that the domain actually exists and if it doesn't then have Exchange discard the email without sending an NDR? Is this possible strait out of the box? It seems like I remember iMail gateway having this feature. I am trying to filter out some of the spam that fills up my users inboxes. Thanks in advance. Jeremy jsteger@bellsouth.net (Jeremy Steger) wrote...

Can you change the default lookup from Account to Contact?
Is it possible to set the lookup for a customer field to default on "contacts" rather then "accounts?" We do more business with contacts so it is a pain to have to change this all the time. there is no supported way to do this in the current release -- John O'Donnell Microsoft CRM MVP http://www.microsoft.com/BusinessSolutions/Community/CRMFaqLanding.aspx "Martin Flaherty" <mflaherty@techpg.com> wrote in message news:ucdNM74uEHA.3416@TK2MSFTNGP09.phx.gbl... > Is it possible to set the lookup for a customer field to default on > "contac...

Formula to lookup named reference based on value
I cant' figure out what the exact words that I'm looking for and if I did I would most likely find the answer. I've got a spreadsheet that has a series of numbers in say column A and it has the numbers 1,2,3,4,5 Further on in the spreadsheet I have named values like CA.1, CA.2, CA. 3 What I want to do is in the formula go: =(X1/SUM(X:X,X,X:X)*CA.2) (for the named range CA.2) Is it possible to have something that goes.... =(X1/SUM(X:X,X,X:X)*"CA"&B4) where B4 = 2 Thus the outcome would be CA.2 ????? =(X1/SUM(X:X,X,X:X)*INDIRECT("CA"&B4)) -- Kind...

Locking a sheet??
Is there anyway to lock a sheet so it cannot be selected at all? I will/can even accept hiding the sheet, but would prefer to have it just locked so it cannot be selected. As always, thanks in advance. mcr1 -- mcr1 ------------------------------------------------------------------------ mcr1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15496 View this thread: http://www.excelforum.com/showthread.php?threadid=499448 Just go up to tools->protection->protect sheet. Then protect it with whatever password. Then after that you can go up to Format-&g...

lookup drop down
I am making a invoice sheet. With materials on two or more sheets. Materials are plumbing supplies like 2" ABS 90 and 2" ABS coupling with prices the materials, size, type will all be different. I would like to have 3 or more drop downs to pick material, size, and type to get to price. I have looked at vlookup, validation, and dependent drop downs. But I can't figure out what is the best and how to pull info from more than 2 drop downs Thank Roy Hi Roy, I have a workbook example that pretty much does that. Would of course need to be adjusted to your data layout...

Lookup
i am using excel 2 calculate the amount of bonus that an employee i entitled to. i do not know what function to use (i have been told 2 us v lookup but i do not know how 2 go about it). the exact problem is this: there is a basic wage and on top of that an employee gets paid a bonus this bonus is calculated by how many sales r made. the table of th bonuses r given (below): Commission on Qtrly Sales Sales per Qtr Percentage �200,000 and under 0.50 �300,000 and under 0.75 �400,000 and under ...