automatically inserting text

[Guest]

I want to insert text into a cell and reference it to text in another cell.

eg A1=123abc then A2 will= bill
A2=456def Then B2 will=john
I have about 20 of the same sets of varying data can anyone suggest any ideas?

 

[Guest]

Sorry evryone the top row of my example should read

A1=abc123 then B1 will=bill

 

[Alan]

In B1,
=IF(A1="abc123","bill","")
The double qoutes at the end of the formula will return an empty cell but
you can put anything you like in there, like an alternative,
=IF(A1="abc123","bill","harry")
or an error message,
=IF(A1="abc123","bill","Error!")
or if you leave them out completely but retain the comma it will return 0
=IF(A1="abc123","bill",)
without the comma it will return FALSE
=IF(A1="abc123","bill")
Regards,
Alan.

 

[Gord Dibben]

Have a table with two columns on another sheet.

One column with 123abc, 456def etc. and the other with bill, john etc.

Use a VLOOKUP formula in Sheet1 Column B to pull from column A

Assume list columns are on Sheet2 in Columns A and B

In Sheet1 B1 enter =VLOOKUP(A1,Sheet2!$A$1:$B$20,2,FALSE)

Copy/drag down column B to B20

Start entering names in A1 and down.

Or use Autocorrect and enter the names in column B without any formula.


Gord Dibben MS Excel MVP