Help needed with creating a Formula in Excel 2007 please ?

[Gerry1234567]

Hi, I have 3 columns of data.
Column x = a list of 130 magazines.
Column y = a list of the 45 media owners that own the 130 magazines.
Column z = a list of the 26 countries that the media owners are located in.
Ideally, I would like Excel 2007 to automatically complete Columns y and z
for me with the correct media owner and country when I enter the magazine
name in column x.
I have tried the IF function but I can only seem to be able to automate the
process for 1 magazine.
Can anyone help please ? !!
Many thanks in advance for a solution.

 

[Sheeloo]

Your problem is not clear...

How will you know who is the owner or the name of the country for a given
magazine?

Do you have Magazine name in Col X, Owner in Y and country in Z?
If yes then why are you entering the name in Col X again?

Looks like you need to use VLOOKUP...

 

[Gerry1234567]

Hi Sheeloo,
Many thanks for your feedback.
I know which magazine belongs to which media owner and which country.
I want to tell excel which mag belongs to which mag and which country so it
can auto complete columns Y (media owner) and Z( country) for me when I
enter the mag name in column X
I hope that is that more clear now.
However, I am not bright enough to write a formula to include in my workbook
to automate that process !
Many thanks. Gerry

 

[Sheeloo]

Sorry, still confused.

Try to give an example...

You must have a list somewhere listing each magazine, its owner and its
country somewhere so that Excel can pick it up when you enter the name

For example if you have the above data in Sheet2 and if you enter a name in
A1 of sheet1 then entering the following formula in B1 will give you the owner
=VLOOKUP(A1,Sheet2!A:C,2,False)

and following in C1 will give the country
=VLOOKUP(A1,Sheet2!A:C,3,False)

You can copy down the formula after entering names in Col A of sheet1

 

[Sandy Crowley]

Gerry,

I've done this process similar to what you are describing.

First: Create three lists of the Magazines (alphabetical), two columns over,
Media Owners and two columns over enter the corresponding Country to the
correct owner. I like to name the ranges by selecting all the magazine list
and Naming the range Magazines. Do the same for Owners and Country

Then in column Y use a formula like this
=IF($A2="Vogue",LOOKUP(A2,Magazines,Owners),IF($A2="Elle",LOOKUP(A2,Magazines,Owners),IF($A2="Golf
Digest",LOOKUP(A2,Magazines,Owners),IF($A2="MS",LOOKUP(A2,Magazines,Owners),IF($A2="Swimming",LOOKUP(A2,Magazines,Owners))))))

In Column Z use this
=IF($A2="Vogue",LOOKUP(B2,Owners,Country),IF($A2="Elle",LOOKUP(B2,Owners,Country),IF($A2="Golf
Digest",LOOKUP(B2,Owners,Country),IF($A2="MS",LOOKUP(Magazines,Owners,Country),IF($A2="Swimming",LOOKUP(Magazines,Owners,Country))))))

You should have your results!

 

[Gerry1234567]

Hi Sheeloo,
many thanks.
Yes, in fact I have 3 seperate lists for the 3 different categories and in
each column (x, y and z), I have those lists as drop down box choices..
Does that help clarify please ?
( the lists are held on the same worksheet but way over to the right in the
BB's !)
Thank you. Gerry

 

[Gerry1234567]

Hi Sandy,
Many thanks !
I will try this for sure.
One quick question though....why 3 lists of the magazines ?
Thanks again.
Gerry

 

[Sandy Crowley]

Sorry, Gerry.

The three lists I refer to are Magazines, Owners, Country. sorry about the
confusion.

 

[Gerry1234567]

Thank you Sandy, I am going to give this a try !

Sorry, Gerry.

The three lists I refer to are Magazines, Owners, Country. sorry about the
confusion.

 

[Sandy Crowley]

Great! Let me know if it works out for you.

 

[Herbert Seidenberg]

You can't have 130 IFs in a formula.
Maximum is 7.
To get you going, try this data entry method
without formulas or typing:
http://www.savefile.com/files/1923025

 

[Sandy Crowley]

Herbert, you're right.

Excel 2007 does increase the nesting limit to 64 levels but that won't help
in this situation.

For older versions of Excel you can also concatenate the IF statements see
solution (this link is legit and does not require you to join)
http://www.eggheadcafe.com/software/aspnet/30195658/nested-if-statements--ma.aspx

Here is another great workaround as well:
http://www.cpearson.com/excel/nested.htm