Help needed with creating a Formula in Excel 2007 please ?

G

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.
 
S

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...
 
G

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
 
S

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
 
S

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!
 
G

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
 
G

Gerry1234567

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

Sandy Crowley

Sorry, Gerry.

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

Gerry1234567

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

Sandy Crowley said:
Sorry, Gerry.

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Simple COUNTIF function help 3
Comparative Scatterplot?? 1
formula help please 1
Formula 1
Equation...help needed! 1
Complex cost allocation formula 1
advance level of sorting data? by Ting 1
I need Help 0

Top