D
Don O
PLEASE HELP
When I enter a 6 digit number beginning with 0 in some cells it is
transposed without the beginning 0('s). In some cells the 0's may
appear in the formula bar but not in the cell and in some cells the
opposite is true. In some 0's may appear in both the formula bar and
the cell and in some the 0 appears in neither. What causes this and,
how can I make it consistent throughout the workbook? I've tried
changing all to text formats, number formats, special formats etc. but
to no avail. My preference is for the 0's to remain in both the
formula bar and the cell.
Background
I'm trying to use the index/match formula to look up or return related
values. The key data is a 6 digit "VIN" number which sometimes begins
with 0. I enter the VIN on sheet1 A1 then other data that relates to
that VIN in B1,C1, etc. I then enter a new VIN on A2 and related data
on B2,C2 etc. down the line. On sheet2, I have an index match formula
to call the related data based on the matching VIN number. My formula
works always except when there is an inconsistency as described above.
I get #N/A error I.E. data not available to the formula or function.
Don O
When I enter a 6 digit number beginning with 0 in some cells it is
transposed without the beginning 0('s). In some cells the 0's may
appear in the formula bar but not in the cell and in some cells the
opposite is true. In some 0's may appear in both the formula bar and
the cell and in some the 0 appears in neither. What causes this and,
how can I make it consistent throughout the workbook? I've tried
changing all to text formats, number formats, special formats etc. but
to no avail. My preference is for the 0's to remain in both the
formula bar and the cell.
Background
I'm trying to use the index/match formula to look up or return related
values. The key data is a 6 digit "VIN" number which sometimes begins
with 0. I enter the VIN on sheet1 A1 then other data that relates to
that VIN in B1,C1, etc. I then enter a new VIN on A2 and related data
on B2,C2 etc. down the line. On sheet2, I have an index match formula
to call the related data based on the matching VIN number. My formula
works always except when there is an inconsistency as described above.
I get #N/A error I.E. data not available to the formula or function.
Don O