• 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.

Values keep changing to #N/A

RobSA

RobSA

Member
Hi Folks,

I am working in a work sheet which uses the formulae CONCATENATE.

The formulae works and presents the correct value required however as I continue to work with the worksheet, the value changes to #N/A and this impacts others cells in the work sheet.

What could be the reason for this.

I have attached and image that the formulae can be read as well as the numbers that are affected.

I look forward to your replies.
 

Attachments

  • NA.GIF
    NA.GIF
    93.8 KB · Views: 7
There is no apparent reason to me. In which cell do you put data? Have you tried with recalculating sheet?
 
Hi Faseeh,

Thanks for the prompt reply.

The data comes from the "LOG" worksheet.

CIF you scroll down the "REPORT" page you will see a similar situation and I thinkn it is as a result of the errors or N/A problem we are discussing.
 
You are welcome,

Kindly correct me if i have misunderstood problem.

The N/A error in the area you are pointing is due to vlookup not finding the value in Log!A4:BF300 (data), i tried with control F but was unable to found the first entry 017-B that gives error for Due Date cell. Can you cross check it?
 
Hi Faseeh,
I have had a look and in using the Find option, I changed the option to look for values as opposed to looking for formulas or comments. The number 017-B is then found.

What do you advise I do to the valuein order for the file to read correctly?
 
I feel brain dead in trying to resolve this problem.

The following formula returns the value of #N/A

=IF(C114="","",VLOOKUP(C114,DATA,3,FALSE))
 
Dear Faseeh,

Thanks for reconnection.

The cell in question is in the REPORT worksheet D71
 

Attachments

  • RH_DTS_LOG_IN_M.xlsb
    819.9 KB · Views: 4
Dear Rob,

I think you are confusing things, you have defined a named range "Data" and that refers to the sheet named "Log". the Data has been defined as range
Code: 
=LOG!$A$4:$BF$503
.

When used in a VLOOKP() in your sheet Report like this =IF(C71="","",VLOOKUP(C71,DATA,3,FALSE)), it will look for the left most column of the "Data" i.e. column A of sheet Log and will fetch the result from the third column, now unfortunately, there is no such match there, giving you N/A Error.

Will you please cross check that. :)
 
What i have understood from your description of the sheet is that you are actually interested in Cell I20 or Cell J20 of the Log report.Untitled.png
 
Dear Faseeh,

Thank you for the reply.

The "LOG" is the name of the worksheet and I have selected a range called "DATA"

The VLOOKUP is based on the report page and its intention is to first read the value in C71 which should be equal to 017_B. IT will then look for the range called "DATA" and find a similar number , 017_B. This number is based in the "REPORT" worksheet in the range "DATA" in column BF.

The problem occurs here (I think)

Once the value is matched it will look for the value in that rw where 017_B is found and look for the third column in the range called "DATA". That number will be in Column C on the same row as 017_B. That is it should provide for a date under the column headed Rev.0.

The problem Excel is experiencing is that it cannot find the value in column BF. If you use the FIND option you will be able to see what I mean. If you select additional options and change the option to VALUE then only does it find the values set in column BF.

I have found that if I remove the CONCATENATE formula in that column and just type in the number it can find the number but still the VLOOKUP presents the problem.

I trust this provides clarity.

Thanks again for your help
 
Please see attached file, should that worked for you..
 

Attachments

  • RH_DTS_LOG_IN_M Working (23.11.2015).xlsb
    813.5 KB · Views: 2
Dear Faseeh,

It works like a dream.

I still however have a problem with #N/A in the same worksheet.

The cells in H17:H43 and P17:43 revert to #N/A for some unknown reason.

I can correct it by placing my cursor at the end of the formula in each cell and then pressing enter. However I cant do this every time I work with the spreadsheet.

Especially when I have to do this for each row in these ranges.

I would appreciate your help.
 
Thanks for the reply - It is a challenge this end - so I am not sure.

All the numbers show normally like you have shown and then while I am working it reverts to #N/A as explained.

Thanks for looking at it for me.
 
You are welcome,

can you tell on which sheets/cells do you work that lead to this N/A error in H17:H43 and P17:43? May be i can try. There must be some part of the sheet you would be working on while such problem occur.
 
Hi Faseeh, IT is noticed when I am working on the REPORT page, I have not been able to pin point the cause yet.
 
There are a few different ways this can happen:

1. The lookup table might be formatted differently from the SearchFor value. For example there might be values that look like numbers but their containing cells are formatted as text

2. There might be spaces around the value in the lookup table

The solutions
1. First convert the SearchFor value to a string and then to a number to see if formatting is your issue.

To a string

=vlookup ( SearchFor & “” , WhereToSearch , WhichColumn , NearOrExact )

To a number

=vlookup ( SearchFor + 0, WhereToSearch , WhichColumn , NearOrExact )

So in the first instance you’re adding no letters to the string (inverted commas with nothing between them) to force excel to recognise SearchFor as a string and in the second instance you’re adding zero to force it to be recognised as a number. Test each and if one works, that’s your formula to drag down if you need to.

2. You can find out if this is the issue by clicking into both cells to see that they aren’t identical, so for example the value you’re looking up is 1234 but the lookup table has 1234 with a space after it (“1234 “)

If you don’t need any spaces you can simply do a find and replace – find ” ” (space) and replace with nothing

Or you can “trim” the values in the column by, in an empty column out to the right, typing in, if the value is in cell A2 for example, =TRIM(A2). Copy this formula all the way down and then copy and paste the new column (paste special > values) into the old one. The trim function removes rogue spaces from either side of the string

Source: www.excelvlookuphelp.com
 
You must log in or register to reply here.
Back
Top