If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
filter out all the #n/a...
I have done a vlookup in col(b), col(c) and col(d)
i want to summarise column b,c,d in column E e.g if col b2 = #na, c2=#na, d2=300 then i want e2=300 if col b3 = 100, c2=#na, d2=#na then i want e2=100 how would i do this?? some sort of OR function? also is there a way to highlight/filter all the rows where b2=#na, c2=#na, d2=#na ? this will tell me which rows that are not found in any of my vlookups. --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
filter out all the #n/a...
If you are getting #n/a as a result of the vlookup statements, try
putting in the following formula. This will produce "Not found" instead of #n/a where the vlookup is unsuccessful =if(isna(vlookupstatement)=true,"Not Found",vlookupstatement) Your sums will then only read the number sand not the text of not found. If you want to highlight all rows where not found is try formula below in column e =if (and(b2="Not Found",c2="Not Found",d2="Not Found"),"Unsuccessful",sum(b2:d2)) Hope this is what you are looking for Romanian Your post: I have done a vlookup in col(b), col(c) and col(d) i want to summarise column b,c,d in column E e.g if col b2 = #na, c2=#na, d2=300 then i want e2=300 if col b3 = 100, c2=#na, d2=#na then i want e2=100 how would i do this?? some sort of OR function? also is there a way to highlight/filter all the rows where b2=#na, c2=#na, d2=#na ? this will tell me which rows that are not found in any of my vlookups. --- Message posted from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
filter out all the #n/a...
that works...
i have another scenario... what it the data in column b,c,d is text... i want column e to contain the non blank data from either column b,c or d e.g if b1= blank, c1=blank, d1= "Peter Smith" - then e1="Peter Smith" if b2= "Roger", c2=blank, d2= blank - then e2="Roger" thx in advance --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Bug: print preview in form datasheet with server filter on crashes access (ADP A2002 SP3) | Eric Cárdenas [MSFT] | Using Forms | 4 | June 24th, 2004 08:38 AM |
Filter Button | Antonio | General Discussion | 4 | June 16th, 2004 05:21 AM |
Determining whther the user is setting or removing the filter... | Brad Pears | Setting Up & Running Reports | 1 | June 9th, 2004 04:29 PM |
Please help! Displaying only rows with empty cells in a numeric field using Advanced Filter. Thanks | JOCA | New Users | 1 | June 5th, 2004 02:37 AM |