Home |
Search |
Today's Posts |
#1
|
|||
|
|||
IF function - need to evaluate cell content in 2 separate files-#N
Hallo
I have two source files feeding into a report page. I need to extract information based on matching cells in each of the files with the same set of reference data. I have this formula which does the job: =IF(ISNA(MATCH(Switches!B4,Computers!$A$2:$A$43,0) ),OFFSET(NonAuditableDevices.xls!$A$1,MATCH(Switch es!C4,NonAuditableDevices.xls!$A$2:$A$20,0),1),OFF SET(Computers!$A$1,MATCH(Switches!B4,Computers!$A$ 2:$A$43,0),6)) But if there are no matches with Switches!B4 in either the Computers or NonAuditableDevices sheets it will return a #NA argument - what I really want to do is leave the cell blank in that case. I thought adding an additional ISNA function somewhere should help but can't seem to get it to work. |
#2
|
|||
|
|||
Don't really follow what you are doing in the formula, but it would be
something along the lines of =IF(OR(ISNA(test1),ISNA(test2)),"",lookup_formula) -- HTH RP (remove nothere from the email address if mailing direct) "PBM" wrote in message ... Hallo I have two source files feeding into a report page. I need to extract information based on matching cells in each of the files with the same set of reference data. I have this formula which does the job: =IF(ISNA(MATCH(Switches!B4,Computers!$A$2:$A$43,0) ),OFFSET(NonAuditableDevic es.xls!$A$1,MATCH(Switches!C4,NonAuditableDevices. xls!$A$2:$A$20,0),1),OFFSE T(Computers!$A$1,MATCH(Switches!B4,Computers!$A$2: $A$43,0),6)) But if there are no matches with Switches!B4 in either the Computers or NonAuditableDevices sheets it will return a #NA argument - what I really want to do is leave the cell blank in that case. I thought adding an additional ISNA function somewhere should help but can't seem to get it to work. |
#3
|
|||
|
|||
You could use another ISNA but that would double your formula size. This
could make the formula difficult to read and would also increase file size since the false condition would need to be that same formula again. You could use a helper column which would hold the following formula =IF(ISERROR(D4),"",D4) your main formula would be in column D which you could hide. Matt "PBM" wrote: Hallo I have two source files feeding into a report page. I need to extract information based on matching cells in each of the files with the same set of reference data. I have this formula which does the job: =IF(ISNA(MATCH(Switches!B4,Computers!$A$2:$A$43,0) ),OFFSET(NonAuditableDevices.xls!$A$1,MATCH(Switch es!C4,NonAuditableDevices.xls!$A$2:$A$20,0),1),OFF SET(Computers!$A$1,MATCH(Switches!B4,Computers!$A$ 2:$A$43,0),6)) But if there are no matches with Switches!B4 in either the Computers or NonAuditableDevices sheets it will return a #NA argument - what I really want to do is leave the cell blank in that case. I thought adding an additional ISNA function somewhere should help but can't seem to get it to work. |
#4
|
|||
|
|||
Thanks for the quick responses, both look like they will sort my problem.
I've got to go out now bit wiull be trying them both tomorrow. I really appreciate the help Peter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use Cell Content to Build Link | Excel Worksheet Functions | |||
How do I find the contents of a cell using the "ADDRESS" function. | Excel Worksheet Functions | |||
Get current cell with VBA function | Excel Worksheet Functions | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Transferring cell content between workbooks using cell references | Excel Discussion (Misc queries) |