Linking data in columns

**redmalva** · 08-10-2009, 12:35 PM

Hi,

I have two columns of data. One column lists a toy item and the next column lists whether it was made in Canada or not with a Y or N.

See below:

Toy CDN
plane N
bear Y
train Y
plane N
bear Y
bear Y

Is there a way that every time "bear" is entered, the CDN column automatically fills with "Y"?

**Paul** · 08-10-2009, 12:51 PM

How many different toys do you have? If it's just a few, you could use a function to do this, e.g.

=IF(OR(A1="Bear",A1="Train"),"Y","N")

This says that if A1 is Bear or Train, put Y in the current cell, otherwise put N for all other entries. If that's not the logic you need, or you have multiple toys possible, then a quick macro might be better.

**jmhultin** · 08-10-2009, 01:14 PM

a lookup table would also work. Say you have 20 toys, list those in column E, row 1 through row 20. In column F, list the appropriate Y or N.

In column B, row 2, put the formula:

=vlookup(B1,$E$1:$F$20,2,FALSE)

then copy that down column B

**redmalva** · 08-10-2009, 02:11 PM

Thanks for the tips. I have lots of different toys and keep on adding new ones all the time. I think I will have to create a new sheet in the workbook and create the table with CDN indicators and use the vlookup function.

One more question though, say I were to implement that. Iin the main sheet, where I enter the toys, is there a way that ensure that the title entered matches my separate titles sheet?

Thanks in advance.

**jmhultin** · 08-10-2009, 02:56 PM

If it doesn't match, you'll get an "#N/A" where the lookup formula is. If you are doing data entry into that column A, your entry should self-complete after the first few characters are typed, if you have already entered that toy at least once in that column.