Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unexplained color shift | Excel Discussion (Misc queries) | |||
Unexplained File sinze Increase | Excel Discussion (Misc queries) | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
Creating links - Unexplained behaviour | New Users to Excel | |||
File sizes unexplained bloating | Excel Discussion (Misc queries) |