Formatting problems in excel 2002

  • Thread starter Thread starter RD
  • Start date Start date
R

RD

I have two sheets labelled "Projects" and "Time". In "Time", in cell C6 the
following the formula resides:
=IF(ISBLANK(B6),"",IF(ISERROR(MATCH(B6,Projects!$A$2:$A$300,0)),"no
match",(VLOOKUP(B6,Projects!$A$2:$D$300,3,FALSE)&-VLOOKUP(B6,Projects!$A$1:$
D$300,4,FALSE))))

The time sheet cell B6 is formatted custom #######-###.

In B6 in "Time" I type in a project number. the formula searches "Projects"
and reports back to C6 in "Time". In "Projects" the cells in column 3 are
formatted custom ####### to show, for example, 0234567. Cells in column 4
are formatted custom ### to show, for example, 001. Thus, the end result in
"Time" is to look like 0234567-001. What I now get is 234567-1

Thanks in advance
RD
 
one way:

=IF(ISBLANK(B6),"",IF(ISNA(MATCH(B6,OFFSET(table,,,,1),FALSE)),"no
match",TEXT(VLOOKUP(B6,table,3,FALSE),"0000000") & "-" &
TEXT(VLOOKUP(B6,table,4,FALSE), "000")))


Where table is defined as Projects!$A$1:$D$300
 
Thanks to all, the answer came as:
IF(ISBLANK(B30),"",IF(ISERROR(MATCH(B30,Projects!$A$2:$A$300,0)),"no
match",(TEXT(VLOOKUP(B30,Projects!$A$2:$D$300,3,FALSE),"0000000")&"-"&TEXT(V
LOOKUP(B30,Projects!$A$1:$D$300,4,FALSE),"000"))))

RD
 
Back
Top