M
Marty
Sorry, this may be a long one:
I have workbook that contains sheets that one enters data
via either data validation lists or entering data
manually. One of the sheets "Material List" contains
lists of parts which have an index number and associated
parts and descriptions, all in different columns. This
sheet is linked to a master "parts list" in a separate
spreadsheet. You change the master list and the "Material
List" in the workbook is updated. No problems here at all.
I added a new part number to the master list and
the "Material List" sheet in the work book was updated.
That data was then "picked" by equations in a sheet
called "Frame" and in appropriate cells was to display
the information. In the "FRAME" there are hundreds of
similar calucations with the same match index info cell
to cell except the cell index number.
For Example:
In cell B70 the result of the formula is say E103
In cell C the formula is: =IF(B70="","",INDEX
(Material_List, MATCH(B70,Item,0), MATCH("Part
Number",Material_List_Headings,0))) and from Material
List display "bolt"
In cell B71 the result of the formula is say E25
In cell C the formula is: =IF(B71="","",INDEX
(Material_List, MATCH(B71,Item,0), MATCH("Part
Number",Material_List_Headings,0))) and from Material
List display "nut"
In cell B72 the result of the formula is say W15
In cell C the formula is: =IF(B72="","",INDEX
(Material_List, MATCH(B72,Item,0), MATCH("Part
Number",Material_List_Headings,0))) and from Material
List display "spring"
This goes on and on and is repeated in sheet after sheet.
WITH NO PROBLEMS
However, on ONE sheet only any index number above E106
doesn't work and brings up a #N/A in column C.
For example:
In cell B80 the result of the formula is say E145
In cell C the formula is: =IF(B80="","",INDEX
(Material_List, MATCH(B80,Item,0), MATCH("Part
Number",Material_List_Headings,0))) and from Material
List display "#N/A"
Numbers in column B like W145 work fine yet numbers like
E145 returns a #N/A in col C. I copied and pasted the
info in col B and C of the "FRAME" sheet directly into
other sheets and they work fine. I pasted the info into a
new blank sheet and it worked fine. I even eliminated the
formula and typed in cell B80 ="E145". In cell be E145
is displayed and in cell C #N/A is displayed. I did this
in other sheets and the display was the correct
one "plate" as shown in both the Master list and
the "material List of the workbook. Thinking perhaps the
name of the sheet had something to do with it -changing
the name did nothing. No matter where I placed the info
on that FRAME sheet resulted in an error. Doing this in
other sheets worked fine. I changed the E145 to Z145 it
wouldn't pick it up. Yet numbers like H145 which where
already entered work fine. I tried other letters and
would not work in the Frame" sheets yet did it in the
others. I am at a loss!!
I have workbook that contains sheets that one enters data
via either data validation lists or entering data
manually. One of the sheets "Material List" contains
lists of parts which have an index number and associated
parts and descriptions, all in different columns. This
sheet is linked to a master "parts list" in a separate
spreadsheet. You change the master list and the "Material
List" in the workbook is updated. No problems here at all.
I added a new part number to the master list and
the "Material List" sheet in the work book was updated.
That data was then "picked" by equations in a sheet
called "Frame" and in appropriate cells was to display
the information. In the "FRAME" there are hundreds of
similar calucations with the same match index info cell
to cell except the cell index number.
For Example:
In cell B70 the result of the formula is say E103
In cell C the formula is: =IF(B70="","",INDEX
(Material_List, MATCH(B70,Item,0), MATCH("Part
Number",Material_List_Headings,0))) and from Material
List display "bolt"
In cell B71 the result of the formula is say E25
In cell C the formula is: =IF(B71="","",INDEX
(Material_List, MATCH(B71,Item,0), MATCH("Part
Number",Material_List_Headings,0))) and from Material
List display "nut"
In cell B72 the result of the formula is say W15
In cell C the formula is: =IF(B72="","",INDEX
(Material_List, MATCH(B72,Item,0), MATCH("Part
Number",Material_List_Headings,0))) and from Material
List display "spring"
This goes on and on and is repeated in sheet after sheet.
WITH NO PROBLEMS
However, on ONE sheet only any index number above E106
doesn't work and brings up a #N/A in column C.
For example:
In cell B80 the result of the formula is say E145
In cell C the formula is: =IF(B80="","",INDEX
(Material_List, MATCH(B80,Item,0), MATCH("Part
Number",Material_List_Headings,0))) and from Material
List display "#N/A"
Numbers in column B like W145 work fine yet numbers like
E145 returns a #N/A in col C. I copied and pasted the
info in col B and C of the "FRAME" sheet directly into
other sheets and they work fine. I pasted the info into a
new blank sheet and it worked fine. I even eliminated the
formula and typed in cell B80 ="E145". In cell be E145
is displayed and in cell C #N/A is displayed. I did this
in other sheets and the display was the correct
one "plate" as shown in both the Master list and
the "material List of the workbook. Thinking perhaps the
name of the sheet had something to do with it -changing
the name did nothing. No matter where I placed the info
on that FRAME sheet resulted in an error. Doing this in
other sheets worked fine. I changed the E145 to Z145 it
wouldn't pick it up. Yet numbers like H145 which where
already entered work fine. I tried other letters and
would not work in the Frame" sheets yet did it in the
others. I am at a loss!!