increment by "1" a cell containing text & number

F

fishinglittlepine

New Member
Joined
Apr 18, 2011
Messages
31
I have a spreadsheet that I have 4 different suppliers in columns and items in about 2,000 rows. I need to create a code at the start of each row for each item, i.e WF01 - 500, WA01 - 500, LC01 - 1000.

I filter the lists to only show the WF or WA or LC items of supply and want to automatically increase the code WF01 to WF02 etc.

I understand that I can do this by simply dragging the bottom right corner down over them if they are not filtered, but I can't do it when they are filtered as the row numbers are 1, 4, 11, non consecutive.

It also won't accept =A1+1 dragged down as it doesn't recognise the text.

Help

Vince
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
Hi,

Why not sort the column first and then apply the filters?? This way you would have all the "WF"s together and just drag down your new code number.

Regards,
Ian
 
Upvote 0
The WFs, WAs, LCs are part of the number that I am attempting to create in the column once they are filtered.

I am filtering by an "x" that is under the supplier name, then trying to create the code WF01/WA01/LC01 etc in column A.
Code
<TABLE style="WIDTH: 624pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=829><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 244pt; mso-width-source: userset; mso-width-alt: 11885" width=325><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4864" width=133><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 59pt; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl68 height=20 width=78>WF01</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 244pt; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl67 width=325>ABC engine</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 59pt; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl70 width=78>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 92pt; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl70 width=122>x</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 100pt; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl70 width=133></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 70pt; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl70 width=93></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl68 height=20>LC01</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 244pt; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl67 width=325>Engine maintenance platform prefabricated</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl70>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl70></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl70></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl70>x</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl68 height=20>WA01</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 244pt; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl67 width=325>Mechanical advisor 1 (man-month)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl70>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl70></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl70>x</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl70></TD></TR></TBODY></TABLE>

So I filter by the "x" under each column then want to create the code WF01 to WF500 in column A, WA01 to WA500 or WA01, or LC01 etc.

Can I filter the "x" under teh selected supplier, type in the first code WF01, and whilst the list is still filtered populate the rest of the column A cells with an incrementing value from WF01 to WF500 automatically, or by dragging down, or by a formula that will increment the number 01 to 500 and include the text as well?

Thanks for your reply, but it's not quite what I need.

Vince
 
Upvote 0
Looks like you are avoiding code
This is my interprutation only

try something like this
Code: 
Sub DoCodes()
    Dim WFn%, LOn%, WAn%, rr%
    Dim Startr%, Endr%
    Startr = 2: Endr = 20
 ' what ever your start and end rows are
    For rr = Startr To Endr
        'looks like you have suppliers in columns   ( using 4,5,6 as example)
        If Cells(rr, 4) <> "" Then  ' whatever column wf is in
            WFn = WFn + 1
            Cells(rr, 1) = "WF" & Right("000" & WFn, 3)    ' to have all  3 digit codes
        End If
        If Cells(rr, 5) <> "" Then
            LOn = LOn + 1
            Cells(rr, 1) = "LO" & Right("000" & LOn, 3)    ' to have all  3 digit codes
        End If
        If Cells(rr, 6) <> "" Then
            WAn = WAn + 1
            Cells(rr, 1) = "WA" & Right("000" & WAn, 3)    ' to have all  3 digit codes
        End If
    Next rr
End Sub
 
Last edited:
Upvote 0
fishinglittlepine said:
The WFs, WAs, LCs are part of the number that I am attempting to create in the column once they are filtered.

I am filtering by an "x" that is under the supplier name, then trying to create the code WF01/WA01/LC01 etc in column A.
Code
<TABLE style="WIDTH: 624pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=829 border=0><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 244pt; mso-width-source: userset; mso-width-alt: 11885" width=325><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4864" width=133><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: windowtext 1pt solid; WIDTH: 59pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" width=78 height=20>WF01</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 244pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" width=325>ABC engine</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 59pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" width=78>8</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 92pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" width=122>x</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 100pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" width=133></TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 70pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" width=93></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: windowtext 1pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=20>LC01</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 244pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" width=325>Engine maintenance platform prefabricated</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">8</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none"></TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none"></TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">x</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: windowtext 1pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=20>WA01</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 244pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" width=325>Mechanical advisor 1 (man-month)</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">5</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none"></TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">x</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none"></TD></TR></TBODY></TABLE>

So I filter by the "x" under each column then want to create the code WF01 to WF500 in column A, WA01 to WA500 or WA01, or LC01 etc.

Can I filter the "x" under teh selected supplier, type in the first code WF01, and whilst the list is still filtered populate the rest of the column A cells with an incrementing value from WF01 to WF500 automatically, or by dragging down, or by a formula that will increment the number 01 to 500 and include the text as well?

Thanks for your reply, but it's not quite what I need.

Vince
Click to expand...
Which columns are associated to which code?

Are these correct:

WF = column D
WA = column E
LC = column F
 
Upvote 0
<TABLE style="WIDTH: 624pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=829><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 244pt; mso-width-source: userset; mso-width-alt: 11885" width=325><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4864" width=133><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 59pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=78>Harry,

Yes, in this order.


</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 244pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=325>





</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74 width=78></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 92pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl85 width=122>WA</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 100pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl85 width=133>WF</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 70pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl85 width=93>LC</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; HEIGHT: 15pt; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" height=20>Column1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 244pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" class=xl66 width=325>Column2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" class=xl74>Column3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" class=xl74>Column4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" class=xl74>Column5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" class=xl74>Column6</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl69 height=20>WF01</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 244pt; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl68 width=325>ABC engine</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl76>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl76>x</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl76> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl76> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl69 height=20>LC01</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 244pt; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl68 width=325>Engine maintenance platform prefabricated</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl76>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl76> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl76> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl76>x</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl69 height=20>WA01</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 244pt; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl68 width=325>Mechanical advisor 1 (man-month)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl76>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl76> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl76>x</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl76> </TD></TR></TBODY></TABLE>


Cheers

Vince
 
Upvote 0
Since you are in Aus. and a fisher folk then some more help from Toowoomba Qld is needed.

The code is less complex to use than the type of formula I can dream up to do the job and should work without sorting filtering etc.

Put in a command button, or anything that will call a subroutine.
Paste the code .. fix start row end row . run it
AFTER SAVING YOUR DATA ..( Have a go ya mug)
Code: 
Private Sub CommandButton1_Click()
    DoCodes
End Sub

Sub DoCodes()
    Dim WFn%, LCn%, WAn%, rr%
    Dim Startr%, Endr%
    Startr = 2: Endr = 20
 ' what ever your start and end rows are.. fix these
    For rr = Startr To Endr
        'looks like you have suppliers in columns   ( using 4,5,6 as example)
      If Cells(rr, 4) <> "" Then
            WAn = WAn + 1
            Cells(rr, 1) = "WA" & Right("000" & WAn, 3)    ' to have all  3 digit codes
        End If
     If Cells(rr, 5) <> "" Then  ' whatever column wf is in
            WFn = WFn + 1
            Cells(rr, 1) = "WF" & Right("000" & WFn, 3)    ' to have all  3 digit codes
        End If
        If Cells(rr, 6) <> "" Then
            LCn = LCn + 1
            Cells(rr, 1) = "LC" & Right("000" & LCn, 3)    ' to have all  3 digit codes
        End If
         Next rr
End Sub
 
Upvote 0
fishinglittlepine said:
<TABLE style="WIDTH: 624pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=829 border=0><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 244pt; mso-width-source: userset; mso-width-alt: 11885" width=325><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4864" width=133><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 59pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=78 height=20>Harry,

Yes, in this order.



</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 244pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=325>






</TD><TD class=xl74 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 59pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=78></TD><TD class=xl85 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 92pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=122>WA</TD><TD class=xl85 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 100pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=133>WF</TD><TD class=xl85 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 70pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=93>LC</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #f0f0f0; COLOR: white; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" height=20>Column1</TD><TD class=xl66 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #f0f0f0; WIDTH: 244pt; COLOR: white; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=325>Column2</TD><TD class=xl74 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #f0f0f0; COLOR: white; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none">Column3</TD><TD class=xl74 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #f0f0f0; COLOR: white; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none">Column4</TD><TD class=xl74 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #f0f0f0; COLOR: white; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none">Column5</TD><TD class=xl74 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #f0f0f0; COLOR: white; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none">Column6</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: windowtext 1pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=20>WF01</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 244pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" width=325>ABC engine</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">8</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">x</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none"></TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: windowtext 1pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=20>LC01</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 244pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" width=325>Engine maintenance platform prefabricated</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">8</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none"></TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none"></TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">x</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: windowtext 1pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=20>WA01</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 244pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" width=325>Mechanical advisor 1 (man-month)</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">5</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none"></TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">x</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none"></TD></TR></TBODY></TABLE>


Cheers

Vince
Click to expand...
Here's a small sample file that demonstrates this.

zzzFishing.xls 20kb

http://cjoint.com/?AIweyQKMj5Q

The formula is a bit on the long side but it works.
 
Upvote 0
Harry,

I had a go yu mug!!

Result below, almost but not quite?

<TABLE style="WIDTH: 654pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=869><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 244pt; mso-width-source: userset; mso-width-alt: 11885" width=325><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4864" span=2 width=133><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 59pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=78></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 244pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 width=325></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl76 width=78></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 92pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl86 width=122>WF</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 100pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl86 width=133>WA</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 100pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl86 width=133>LC</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; HEIGHT: 15.75pt; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" height=21>Column1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 244pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" class=xl68 width=325>Column2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" class=xl76>Column3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" class=xl76>Column4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" class=xl76>Column5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" class=xl76>Column6</TD></TR><TR style="HEIGHT: 30.75pt" height=41><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 30.75pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl83 height=41> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 244pt; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl84 width=325> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl79> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl79> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl79> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl79> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl72 height=20> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 244pt; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl73 width=325> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl82> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl82> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl82> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl82> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl71 height=20> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 244pt; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl70 width=325> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl78> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl78> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl78> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl78> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl71 height=20> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 244pt; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl70 width=325> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl78> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl78> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl78> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl78> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl71 height=20>WF001</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 244pt; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl70 width=325>Wärtsilä 12V50DF engine</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl78>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl78>x</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl78> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl78> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl71 height=20>LC001</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 244pt; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl70 width=325> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl78> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl78> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl78> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: red; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl87> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl71 height=20>LC001</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 244pt; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl70 width=325>Engine maintenance platform prefabricated</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl78>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl78> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl78> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl78>x</TD></TR><TR style="HEIGHT: 30pt" height=40><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 30pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl71 height=40>LC001</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 244pt; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl70 width=325>Light fuel oil flow meter for fuel unloading measurement</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: red; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: line-through; text-underline-style: none; text-line-through: single; mso-pattern: #DBE5F1 none" class=xl81><S>1</S></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: red; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: line-through; text-underline-style: none; text-line-through: single; mso-pattern: #DBE5F1 none" class=xl81><S style="VISIBILITY: hidden; mso-ignore: visibility"> </S></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: red; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: line-through; text-underline-style: none; text-line-through: single; mso-pattern: #DBE5F1 none" class=xl81><S style="VISIBILITY: hidden; mso-ignore: visibility"> </S></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: red; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: line-through; text-underline-style: none; text-line-through: single; mso-pattern: #DBE5F1 none" class=xl81><S style="VISIBILITY: hidden; mso-ignore: visibility"> </S></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl71 height=20>WA001</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 244pt; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl70 width=325>Mechanical advisor 1 (man-month)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl78>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl78> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl78>x</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl78> </TD></TR></TBODY></TABLE>


WFs and WAs seem to be working but the LCs are not incremental and occuring in blank cells? I can understand that even a "-" will cause it to be included, but not sure why LCs are including blank cells and not incremental. I can't see anything in LC cells, they appear blank?

Below is the code I copied in.

Sub Code_Numbering()
Dim WFn%, LOn%, WAn%, rr%
Dim Startr%, Endr%
Startr = 13: Endr = 2000
' what ever your start and end rows are
For rr = Startr To Endr
'looks like you have suppliers in columns ( using 4,5,6 as example)
If Cells(rr, 4) <> "" Then ' whatever column wf is in
WFn = WFn + 1
Cells(rr, 1) = "WF" & Right("000" & WFn, 3) ' to have all 3 digit codes
End If
If Cells(rr, 5) <> "" Then
WAn = WAn + 1
Cells(rr, 1) = "WA" & Right("000" & WAn, 3) ' to have all 3 digit codes
End If
If Cells(rr, 6) <> "" Then
LCn = LCn + 1
Cells(rr, 1) = "LC" & Right("000" & LCn, 3) ' to have all 3 digit codes
End If
Next rr
End Sub

And yes, I dream every day of fly fishing on Little Pine and Penstock Lagoon chasing the trout.

Cheers

Vince
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Drag down formula in row an increment value by more than 1
Replies
6
Views
2K
StephenCrump
S
C
  • Solved
Increment cell number between 2 worksheets
Replies
3
Views
340
Joe4
Joe4
A
  • Solved
Automatic Serial Number before Text in a Cell
2 3
Replies
25
Views
1K
Joe4
Joe4
K
  • Solved
Using VBA to select a color coded cell
2
Replies
11
Views
953
Alex Blakenburg
A
D
  • Solved
Using FILTER/UNIQUE on text, numbers
2 3 4
Replies
32
Views
2K
KRice
K

Forum statistics

Threads
1,224,595
Messages
6,179,798
Members
452,943
Latest member
Newbie4296

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top