• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula for equalling a cell

R

rjwalters

New Member
So I thought I was getting a grasp on this formula stuff, or maybe just not enough coffe, but I have a formula that I want to equal a cell value, but if the cell value = #N/A then it needs to return a G. This is what I have and cant figure out why it wont work.


=D2 IF(D2="#N/A","G", " ")


So I am wanting it to retain cell D2 value unless D2 is #N/A, then it should return a G.

What am I missing.


I am putting the formula in D3.
 
Hi rjwalters,


"#N/A" is not a text.. its actually a Error Value.. You cant catch it by comparing with a TEXT (#N/A).. Although excel has lots of function to catch ISNA (for #N/A error), ISERR, ISERROR (for any error), ISREF(for #REF! error) but you can use only
Code: 
=IFERROR(D2,"G")


It will take care for both True & False part..


Regards,

Deb
 
Hi rjwalters,


Thanks for feedback... By The Way.. You dont have to use another cell (D3) to check if D2is #N/A. You can add the same in your D2 Formula..


Code: 
=IFERROR("Your old Formula in D2","G")


Regards,

Deb
 
Ok I am back, now I want to add two formulas together, I want to add =VLOOKUP(A2,QEV.20130122.xlsx!$A$2:$F$6390,2,FALSE) and =IFERROR(D2,"G")


What I am looking to do is use the Vlookup, but if the Vlookup returns the #N/A ,then it ads a G.


This will all go in the same cell.
 
So I managed to get this to work but it keeps opening the save box. I used the microsoft recommended fix. Why?
 
Hi rjwalters,


I am confused.. that [QEV.20130122xlsx] is name of any TAB or Workbook..


Please check if file or sheet is there..

Regards,

Deb
 
RJWalters


The format of a link to a range in another workbook is

=[BookName.xlsm]SheetName!Range

eg: =[MyWorkbook.xlsm]Sheet1!$A$1:$B$2

or if the Workbook or Worksheets name has a space

eg: ='[My Workbook.xlsm]Sheet 1'!$A$1:$B$2


The format of a link to a range in another worksheet is

=SheetName!$A$1:$B$2


so your formula: =IFERROR(VLOOKUP(A3,QEV.20130122xlsx!$A$2:$F$6390,2,FALSE)*E3,"G")

is either misformed or has an error

It appears to be missing the Sheetname ?


can you clarify
 
Ok, What I did was used the Vlookup table that comes up when I inserted the formula. This is how it pasted when I clicked the cells I wanted in the look up. I will see if I did something wrong, thanks
 
You must log in or register to reply here.
Back
Top