| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Wacky Little IF Structure
It never ceases to amaze me the solutions I find in these forums. I find I;m posting about a question a week, and I have gotten some absolutely amazing answers. If one of you who have helped me before happens to come across this post as well, I just wanted to take a moment and say THANX!!!! On to business, though. On the Daily Tracker sheet, my screener lists off every single ticket that he deals with. Column A is the ticket number, B is the reps name, C is the category, D is a CB indicator, E indicates if the ticket is a valid claim, and F for a description. Now, on another work sheet I need it to list off all of the invalid tickets, but only the ticket number, name and description. So what I have at the moment is: In column A, for the ticket number: =IF('Daily Tracker'!E4:E1000="No",'Daily Tracker'!A4:A1000) In column B, for the reps name: =IF('Daily Tracker'!E4:E1000="No",'Daily Tracker'!B4:B1000) In column C, for the description: =IF('Daily Tracker'!E4:E1000="No",'Daily Tracker'!F4:F1000) Now, this works perfectly6, except for one thing. When the ticket on the Daily Tracker sheet is valid, it is putting the word "FALSE" into A, B, and C. So, lets say for example that row 4 and 5 were invalid, 6 was valid and 7 was invalid. What I would end up with is: Ticket # -- Rep's Name -- Description Ticket # -- Rep's Name -- Description FALSE -- FALSE -- FALSE Ticket # -- Rep's Name -- Description What I am looking for is a way to tell it to skip over those and move onto the next so it will not give me those "FALSE" lines. Thanx for all the help!!! -- nevi ------------------------------------------------------------------------ nevi's Profile: http://www.excelforum.com/member.php...o&userid=33238 View this thread: http://www.excelforum.com/showthread...hreadid=555141 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Wacky Little IF Structure
Select the cells on the second sheet for the ticket numbers (all possible),
and in the formula bar enter =IF(ISERROR(SMALL(IF('Daily Tracker'!E4:E1000="No",ROW($A1:$A20),""),ROW($A1:$ A20))),"", INDEX('Daily Tracker'!A4:A1000,SMALL(IF('Daily Tracker'!E4:E1000="No",ROW($A1:$A20),""),ROW($A1:$ A20)))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. In B1, add =VLOOKUP($A1,'Daily Tracker'!$A$4:$F$1000,2,FALSE) C1 =VLOOKUP($A1,'Daily Tracker'!$A$4:$F$1000,6,FALSE) Copy B1:C1 down -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "nevi" wrote in message ... It never ceases to amaze me the solutions I find in these forums. I find I;m posting about a question a week, and I have gotten some absolutely amazing answers. If one of you who have helped me before happens to come across this post as well, I just wanted to take a moment and say THANX!!!! On to business, though. On the Daily Tracker sheet, my screener lists off every single ticket that he deals with. Column A is the ticket number, B is the reps name, C is the category, D is a CB indicator, E indicates if the ticket is a valid claim, and F for a description. Now, on another work sheet I need it to list off all of the invalid tickets, but only the ticket number, name and description. So what I have at the moment is: In column A, for the ticket number: =IF('Daily Tracker'!E4:E1000="No",'Daily Tracker'!A4:A1000) In column B, for the reps name: =IF('Daily Tracker'!E4:E1000="No",'Daily Tracker'!B4:B1000) In column C, for the description: =IF('Daily Tracker'!E4:E1000="No",'Daily Tracker'!F4:F1000) Now, this works perfectly6, except for one thing. When the ticket on the Daily Tracker sheet is valid, it is putting the word "FALSE" into A, B, and C. So, lets say for example that row 4 and 5 were invalid, 6 was valid and 7 was invalid. What I would end up with is: Ticket # -- Rep's Name -- Description Ticket # -- Rep's Name -- Description FALSE -- FALSE -- FALSE Ticket # -- Rep's Name -- Description What I am looking for is a way to tell it to skip over those and move onto the next so it will not give me those "FALSE" lines. Thanx for all the help!!! -- nevi ------------------------------------------------------------------------ nevi's Profile: http://www.excelforum.com/member.php...o&userid=33238 View this thread: http://www.excelforum.com/showthread...hreadid=555141 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Wacky Little IF Structure
sorry that will find only the first "no"
-- paul remove nospam for email addy! "paul" wrote: =IF('Daily Tracker'!E4:E1000="No",'Daily Tracker'!A4:A1000,"") All you nee to do is add an argument what to do if it is false,but you will have a blank line instead Would prolly be better to use an index and match formula to lookup only the invalid tickets =INDEX('Daily Tracker'!A4:E1000,MATCH("no",E4:E1000,0),5) -- paul remove nospam for email addy! "nevi" wrote: It never ceases to amaze me the solutions I find in these forums. I find I;m posting about a question a week, and I have gotten some absolutely amazing answers. If one of you who have helped me before happens to come across this post as well, I just wanted to take a moment and say THANX!!!! On to business, though. On the Daily Tracker sheet, my screener lists off every single ticket that he deals with. Column A is the ticket number, B is the reps name, C is the category, D is a CB indicator, E indicates if the ticket is a valid claim, and F for a description. Now, on another work sheet I need it to list off all of the invalid tickets, but only the ticket number, name and description. So what I have at the moment is: In column A, for the ticket number: =IF('Daily Tracker'!E4:E1000="No",'Daily Tracker'!A4:A1000) In column B, for the reps name: =IF('Daily Tracker'!E4:E1000="No",'Daily Tracker'!B4:B1000) In column C, for the description: =IF('Daily Tracker'!E4:E1000="No",'Daily Tracker'!F4:F1000) Now, this works perfectly6, except for one thing. When the ticket on the Daily Tracker sheet is valid, it is putting the word "FALSE" into A, B, and C. So, lets say for example that row 4 and 5 were invalid, 6 was valid and 7 was invalid. What I would end up with is: Ticket # -- Rep's Name -- Description Ticket # -- Rep's Name -- Description FALSE -- FALSE -- FALSE Ticket # -- Rep's Name -- Description What I am looking for is a way to tell it to skip over those and move onto the next so it will not give me those "FALSE" lines. Thanx for all the help!!! -- nevi ------------------------------------------------------------------------ nevi's Profile: http://www.excelforum.com/member.php...o&userid=33238 View this thread: http://www.excelforum.com/showthread...hreadid=555141 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| can excel links be somehow relative to folder structure? | Excel Discussion (Misc queries) | |||
| Automate a complex decision structure | Excel Worksheet Functions | |||
| Multiple Variables in a Commission Structure...a tough one!!! | Excel Worksheet Functions | |||
| Slow opening directory structure in Excel 2002 | Excel Discussion (Misc queries) | |||
| data structure and my problem | Excel Worksheet Functions | |||
Linear Mode
