#N/A Error unexplained
 

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
xl2003.
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:
=IF(COUNTIF(Exists,ROW()-0.9+0.1*(COLUMN()=37)+0.1*(COLUMN()=47))=1,IF(VL OOKUP(ROW()-0.9+0.1*(COLUMN()=37)+0.1*(COLUMN()=47),Tbl_Entr y,COLUMN()-25-10*(COLUMN()=37)-10*(COLUMN()=47),FALSE)="","",VLOOKUP(ROW()-0.9+0.1*(COLUMN()=37)+0.1*(COLUMN()=47),Tbl_Entr y,COLUMN()-25-10*(COLUMN()=37)-10*(COLUMN()=47),FALSE)),"")

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.


--
John C

#N/A Error unexplained
 

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

VLOOKUP(ROW()-0.9+0.1*(COLUMN()=37)+0.1*(COLUMN()=47),
Tbl_entry,COLUMN()-25-10*(COLUMN()=37)-10*(COLUMN()=47),FALSE)

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

ROUND(ROW()-0.9+0.1*(COLUMN()=37)+0.1*(COLUMN()=47),1)

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

"John C" wrote:

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
xl2003.
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:
=IF(COUNTIF(Exists,ROW()-0.9+0.1*(COLUMN()=37)+0.1*(COLUMN()=47))=1,IF(VL OOKUP(ROW()-0.9+0.1*(COLUMN()=37)+0.1*(COLUMN()=47),Tbl_Entr y,COLUMN()-25-10*(COLUMN()=37)-10*(COLUMN()=47),FALSE)="","",VLOOKUP(ROW()-0.9+0.1*(COLUMN()=37)+0.1*(COLUMN()=47),Tbl_Entr y,COLUMN()-25-10*(COLUMN()=37)-10*(COLUMN()=47),FALSE)),"")

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.


--
John C