I need to create a formula that will fill in automatically the sequential
number FC/010, FC/011, FC/012 on the cell next to the one where I insert
information.

Ex.
Contract number Contract title
TC/010 Supply of uniforms
TC/011 Courier services
TC/012 Supply of cleaning services
Supply of paper
As I type the contract title on cell B the contract number should fill in
automatically on cell A

So far I've only managed to get a logical formula that does the function but
filling sequential numbers without letters, 1,2,3,4
=IF(ISTEXT(B2),ROW()," ")
I've also searched the excel help tool, and I found that it would be
possible through using VBA functions which I have no idea how to do. Far too
advanced for me...
Any easier solution?

--
Greetings from Australia

=IF(ISTEXT(B2),"TC"&ROW(B2)&"/ "&B2,"")


"Neni" wrote:

I need to create a formula that will fill in automatically the sequential
number FC/010, FC/011, FC/012 on the cell next to the one where I insert
information.

Ex.
Contract number Contract title
TC/010 Supply of uniforms
TC/011 Courier services
TC/012 Supply of cleaning services
Supply of paper
As I type the contract title on cell B the contract number should fill in
automatically on cell A

So far I've only managed to get a logical formula that does the function but
filling sequential numbers without letters, 1,2,3,4
=IF(ISTEXT(B2),ROW()," ")
I've also searched the excel help tool, and I found that it would be
possible through using VBA functions which I have no idea how to do. Far too
advanced for me...
Any easier solution?

--
Greetings from Australia

I think this formula does what you want...

=TEXT((ROW(A1)+9)*(B2<""),"TC\/000;;")

Just place it in the "start cell" and copy it down as far as needed.

--
Rick (MVP - Excel)


"Neni" wrote in message
...
I need to create a formula that will fill in automatically the sequential
number FC/010, FC/011, FC/012 on the cell next to the one where I insert
information.

Ex.
Contract number Contract title
TC/010 Supply of uniforms
TC/011 Courier services
TC/012 Supply of cleaning services
Supply of paper
As I type the contract title on cell B the contract number should fill in
automatically on cell A

So far I've only managed to get a logical formula that does the function
but
filling sequential numbers without letters, 1,2,3,4
=IF(ISTEXT(B2),ROW()," ")
I've also searched the excel help tool, and I found that it would be
possible through using VBA functions which I have no idea how to do. Far
too
advanced for me...
Any easier solution?

--
Greetings from Australia

I believe you want consecutive numbers with blanks in Col A if Col B is
blank...

You can do this but with a helper column... Say Col Z
enter 10 in Z1
and this in Z2
=IF(B2<0,Z1+1,Z1)
and copy down

Now enter this in A1
=IF(B1<"","TC/0"&Z1,"")
and copy down...



"Neni" wrote:

I need to create a formula that will fill in automatically the sequential
number FC/010, FC/011, FC/012 on the cell next to the one where I insert
information.

Ex.
Contract number Contract title
TC/010 Supply of uniforms
TC/011 Courier services
TC/012 Supply of cleaning services
Supply of paper
As I type the contract title on cell B the contract number should fill in
automatically on cell A

So far I've only managed to get a logical formula that does the function but
filling sequential numbers without letters, 1,2,3,4
=IF(ISTEXT(B2),ROW()," ")
I've also searched the excel help tool, and I found that it would be
possible through using VBA functions which I have no idea how to do. Far too
advanced for me...
Any easier solution?

--
Greetings from Australia

Hi,

Great it works, thanks very much. But if I don't want a helper column, is it
possible?

Neni,
Greetings from Australia


"Sheeloo" wrote:

I believe you want consecutive numbers with blanks in Col A if Col B is
blank...

You can do this but with a helper column... Say Col Z
enter 10 in Z1
and this in Z2
=IF(B2<0,Z1+1,Z1)
and copy down

Now enter this in A1
=IF(B1<"","TC/0"&Z1,"")
and copy down...



"Neni" wrote:

I need to create a formula that will fill in automatically the sequential
number FC/010, FC/011, FC/012 on the cell next to the one where I insert
information.

Ex.
Contract number Contract title
TC/010 Supply of uniforms
TC/011 Courier services
TC/012 Supply of cleaning services
Supply of paper
As I type the contract title on cell B the contract number should fill in
automatically on cell A

So far I've only managed to get a logical formula that does the function but
filling sequential numbers without letters, 1,2,3,4
=IF(ISTEXT(B2),ROW()," ")
I've also searched the excel help tool, and I found that it would be
possible through using VBA functions which I have no idea how to do. Far too
advanced for me...
Any easier solution?

--
Greetings from Australia

Brillant! it works, thanks ever so much!
Out of information, (sorry not very expert in formulas), what means
TC\/000;;"? What is the \/ symbol?

Neni

--
Greetings from Australia


"Rick Rothstein" wrote:

I think this formula does what you want...

=TEXT((ROW(A1)+9)*(B2<""),"TC\/000;;")

Just place it in the "start cell" and copy it down as far as needed.

--
Rick (MVP - Excel)


"Neni" wrote in message
...
I need to create a formula that will fill in automatically the sequential
number FC/010, FC/011, FC/012 on the cell next to the one where I insert
information.

Ex.
Contract number Contract title
TC/010 Supply of uniforms
TC/011 Courier services
TC/012 Supply of cleaning services
Supply of paper
As I type the contract title on cell B the contract number should fill in
automatically on cell A

So far I've only managed to get a logical formula that does the function
but
filling sequential numbers without letters, 1,2,3,4
=IF(ISTEXT(B2),ROW()," ")
I've also searched the excel help tool, and I found that it would be
possible through using VBA functions which I have no idea how to do. Far
too
advanced for me...
Any easier solution?

--
Greetings from Australia

Thank you very much for your help. But this formula is concatening the
contract number and title. I need fill in the contract name on cell B1 and
the cell A1 would be filled in automatically with TC/001. New contract name
on B2 and A2 fills in with TC/002 and so on...

Neni
--
Greetings from Australia


"dhstein" wrote:

=IF(ISTEXT(B2),"TC"&ROW(B2)&"/ "&B2,"")


"Neni" wrote:

I need to create a formula that will fill in automatically the sequential
number FC/010, FC/011, FC/012 on the cell next to the one where I insert
information.

Ex.
Contract number Contract title
TC/010 Supply of uniforms
TC/011 Courier services
TC/012 Supply of cleaning services
Supply of paper
As I type the contract title on cell B the contract number should fill in
automatically on cell A

So far I've only managed to get a logical formula that does the function but
filling sequential numbers without letters, 1,2,3,4
=IF(ISTEXT(B2),ROW()," ")
I've also searched the excel help tool, and I found that it would be
possible through using VBA functions which I have no idea how to do. Far too
advanced for me...
Any easier solution?

--
Greetings from Australia

The simples I could come up with is this...
Typr or paste
TC/010 in A1
and Typr or paste in A2
Typr or paste
=IF(B2="","","TC/0"&MID(LOOKUP(2,1/IF($A$1:A1="",$A$1:A1,1),$A$1:A1),5,LEN(LOOKUP(2,1/IF($A$1:A1="",$A$1:A1,1),$A$1:A1))-4)+1)
then press CTRL-SHIFT-ENTER together...

and copy down...

It will give you
TC/010, TC/011,...., TC/099, TC/0100...

Let us know how it goes..

"Neni" wrote:

Hi,

Great it works, thanks very much. But if I don't want a helper column, is it
possible?

Neni,
Greetings from Australia


"Sheeloo" wrote:

I believe you want consecutive numbers with blanks in Col A if Col B is
blank...

You can do this but with a helper column... Say Col Z
enter 10 in Z1
and this in Z2
=IF(B2<0,Z1+1,Z1)
and copy down

Now enter this in A1
=IF(B1<"","TC/0"&Z1,"")
and copy down...



"Neni" wrote:

I need to create a formula that will fill in automatically the sequential
number FC/010, FC/011, FC/012 on the cell next to the one where I insert
information.

Ex.
Contract number Contract title
TC/010 Supply of uniforms
TC/011 Courier services
TC/012 Supply of cleaning services
Supply of paper
As I type the contract title on cell B the contract number should fill in
automatically on cell A

So far I've only managed to get a logical formula that does the function but
filling sequential numbers without letters, 1,2,3,4
=IF(ISTEXT(B2),ROW()," ")
I've also searched the excel help tool, and I found that it would be
possible through using VBA functions which I have no idea how to do. Far too
advanced for me...
Any easier solution?

--
Greetings from Australia

The \/ is not one symbol. It is a backslash \ followed by a forward slash /
The whole "TC\/000;;" is a custom formatting string which the TEXT function
can use to define the format of its output.
--
David Biddulph

Neni wrote:
Brillant! it works, thanks ever so much!
Out of information, (sorry not very expert in formulas), what means
TC\/000;;"? What is the \/ symbol?

Neni


I think this formula does what you want...

=TEXT((ROW(A1)+9)*(B2<""),"TC\/000;;")

Just place it in the "start cell" and copy it down as far as needed.

--
Rick (MVP - Excel)


"Neni" wrote in message
...
I need to create a formula that will fill in automatically the
sequential number FC/010, FC/011, FC/012 on the cell next to the
one where I insert information.

Ex.
Contract number Contract title
TC/010 Supply of uniforms
TC/011 Courier services
TC/012 Supply of cleaning services
Supply of paper
As I type the contract title on cell B the contract number should
fill in automatically on cell A

So far I've only managed to get a logical formula that does the
function but
filling sequential numbers without letters, 1,2,3,4
=IF(ISTEXT(B2),ROW()," ")
I've also searched the excel help tool, and I found that it would be
possible through using VBA functions which I have no idea how to
do. Far too
advanced for me...
Any easier solution?

--
Greetings from Australia