#N/A Error unexplained


John C

Okay, here is a question. I have a single spreadsheet that I am using for a
mailmerge. However, I am getting an #N/A error that I cannot explain. I have
Tbl_Entry (defined as $A$2:$K$100)
Exists (defined as $A$2:$A$100)
A2: =IF(B2="","",INT((ROW()+1)/3)+MOD(ROW()-2,3)/10+0.1) (copied down to A100)
This will give results of, for example, in A2:A8: 1.1, 1.2, 1.3, 2.1, 2.2,
2.3, 3.1 and so forth, and up to 99 entries.
There will be up to 3 records per sheet on the mailmerge. I am trying to
gather the mailmerge data records in AA2:BD34. 33 rows * 3 records per row is
equal to up to 99 entries.
My formula is as follows

I am getting #N/A errors in cells AU2:BD2
which is where it should display the value for record 1.3
I do just a straight lookup, and it works just fine, rows 3 and beyond in
AU:BD work just fine. But for whatever reason, the 1.3 just will not work.

I realize that there may be a better solution, and would appreciate the
thought behind it, but even still, would like to know why xl is giving errors
where I don't think there should be errors.

Thanks in advance.



Duke Carey

I used Excel's evaluate formula tool and watched for the NA for this formula
in cell AA2. It happens in this chunk of the formula - the 2d IF statement


Investigating further, it's the lookup value that is the issue. When I
wrapped it in a ROUND() function, all was well.


That's not to say that something else might cause problems elsewhere or in
one of the other cells.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question