 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / June 2007lookup formula
Hi, I have an issue bringing across data into a table. In worksheet 1 I have my data, in worksheet 2, I have one table that needs to be populated by only some of the data in worksheet 1. The problem is that I have multiple sets of criteria that could meet each cell in the table. For example, the first column in my table is simply a name. But, Not only do i need my table to show how many times the name appears in worksheet 1 in a different row, but I also need it to seperate out each individual name. Example: In worksheet 1 I may have something like this: Column 1 Column 2 John Yes Paul Yes Stephen No John Yes Jason Yes Paul No John No Bob Maybe I need my table in worksheet 2 to look something like this (the criteria for returning the value is Yes or Maybe): Column 1 John Paul John Jason Bob Any ideas? Is this a one time need or will worksheet 2 need to dynamically change whenever worksheet 1 values change from a maybe or no to a yes? If it's a one time need then I'd simply have worksheet 2 contain =IF(Sheet1!B1="yes",Sheet1!A1,IF(Sheet1!B1="maybe",Sheet1!A1,"")) in any rows with names on worksheet1, so Worksheet1 Fred yes Bert no John yes Paul yes Sarah yes John maybe gives Worksheet2 Fred John Paul Sarah John You can then highlight, copy those cells and paste special values so you lose the formulae and just get the names listed hmmm, possibly Can I do it with out showing any gaps in worksheet 2? > Is this a one time need or will worksheet 2 need to dynamically change > whenever worksheet 1 values change from a maybe or no to a yes? [quoted text clipped - 21 lines] > You can then highlight, copy those cells and paste special values so > you lose the formulae and just get the names listed Try to make a pivot table on a new sheet, you could show only those names which are marked with Yes or Maybe, and also you could count the name appearances. > Hi, > [quoted text clipped - 36 lines] > > Any ideas? =IF(ISERR(SMALL(IF((status="yes")+(status="maybe"),ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))),"",INDEX(names,SMALL(IF((status="yes")+(status="maybe"),ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1)))) ctrl+shift+enter, not just enter copy down > Hi, > [quoted text clipped - 36 lines] > > Any ideas? Ok i would like to try this formula but am getting a bit confused. Do i replace "status" with the column? What do i Replace "name" with? Sorry, i've never used ROW or INDIRECT formulas before so not sure how they work. > =IF(ISERR(SMALL(IF((status="yes")+(status="maybe"),ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))),"",INDEX(names,SMALL(IF((status="yes")+(status="maybe"),ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1)))) > [quoted text clipped - 41 lines] > > > > Any ideas? "status" is a define name in a range (ex: B2:B100 contain "yes", "no", "maybe") "names" is a define name in a range (ex: A2:A100 contain a list of names) > Ok i would like to try this formula but am getting a bit confused. > [quoted text clipped - 50 lines] > > > > > > Any ideas? holy crap it worked. Thank you so much. =IF(ISERR(SMALL(IF(('Data Entry'!$B$8:$B$200=Lists!$B$10)+('Data Entry'!$B$8:$B$200=Lists!$B$11),ROW(INDIRECT("1:"&ROWS('Data Entry'!$A$8:$A$200)))),ROWS($1:1))),"",INDEX('Data Entry'!$A$8:$A$200,SMALL(IF((status=Lists!$B$10)+(status=Lists!$B$11),ROW(INDIRECT("1:"&ROWS('Data Entry'!$A$8:$A$200)))),ROWS($1:1)))) > "status" is a define name in a range (ex: B2:B100 contain "yes", "no", "maybe") > "names" is a define name in a range (ex: A2:A100 contain a list of names) [quoted text clipped - 53 lines] > > > > > > > > Any ideas? Are the only possible entries Yes, No or Maybe (and no empty cells)? If so, you can do this more efficiently (smaller formula, faster calculating, non-volatile) if you just test for entries that are not "no". =IF(ROWS($1:1)<=COUNTIF(status,"<>no"),INDEX(name,SMALL(IF(status<>"no",ROW(name)-MIN(ROW(name))+1),ROWS($1:1))),"") I keep trying to "convince" TM this method is much better (smaller formula, faster calculating, non-volatile) but "he" doesn't want to listen! <g> Biff > holy crap it worked. > [quoted text clipped - 71 lines] >> > > > >> > > > Any ideas? I forgot: That formula is also an array formula entered with CTRL,SHIFT,ENTER (not just Enter). Biff > Are the only possible entries Yes, No or Maybe (and no empty cells)? > [quoted text clipped - 85 lines] >>> > > > >>> > > > Any ideas? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.