Excel & look up tables

[transparent]

Look up tables?

I have a sheet which I would like to populate automatically from a couple of look up tables.

Are lookup tables possible?

How do I approach this?

 

[AnotherHiggins]

Have a look at Excel's help file at the entries for "Lookup", "VLookup" or "HLookup".

Post back with any questions.

[tt]_____
[blue]-John[/blue][/tt]

"Patriotism is your conviction that this country is superior to all other countries because you were born in it."
[tab]-George Bernard Shaw

Help us help you. Please read FAQ181-2886 before posting.

 

[transparent]

Right it looks like VLookup is what I after.

Essentially

I have a table like this@

Name Paper ImpactFactor + lots of other columns

rick sun 10
John sun 11
frank Mirror 7
Ted Telegraph 9


I then have a table of papers:

Paper Impact

Sun 10
Mirror 7
Telegraph 9

When I edit the impact factor of the paper called 'Sun'
I want my first table to be updated.

I'm guessing this will be a combination of VLookup and Macros?

 

[AnotherHiggins]

In your example, Sun is rated 10 for Rick and 11 for John. Would a single paper name ever be different as this suggests?

If not, then in your fist table you could just use something like
[COLOR=blue white]=vlookup(b2,Sheet2!$A$1:$B$10,2,0)[/color]
where your list of papers and impacts are listed on Sheet2 in cells A1:B10.

[tt]_____
[blue]-John[/blue][/tt]

"Patriotism is your conviction that this country is superior to all other countries because you were born in it."
[tab]-George Bernard Shaw

Help us help you. Please read FAQ181-2886 before posting.

 

[transparent]

My mistake - the values should be the same!!

Cheers... You are a star.

Okay, in my first table, is it possible for the user to be able to select the values in the Paper column from a drop down populated from the paper lookup table i.e

The user enters a new row in the first table for somebody called Paul and then uses a dropdown to select a paper from the drop down. The impact factor is then automatically updated for Paul?

 

[AnotherHiggins]

Have a look at Data > Validation. To be able to hide your source table in another sheet, you'll have to create a Named Range.

In the example above, you would highlight Sheet2 column A (the entire column) then go to Insert > Name > Define. Type in a name for the range, perhaps "PaperNames", then click OK.

Now, back on Sheet1, Select column B (or whatever column the paper names will live in). Go to Data > Validation, change the Allow to List and in Source, type in [COLOR=blue white]=PaperNames[/color] (with the equals sign).

Viola!

[tt]_____
[blue]-John[/blue][/tt]

"Patriotism is your conviction that this country is superior to all other countries because you were born in it."
[tab]-George Bernard Shaw

Help us help you. Please read FAQ181-2886 before posting.

 

[transparent]

I'm getting the error, List must be a deliminated list!!

Am I being dumb (yes!!)

 

[transparent]

Re:

=vlookup(b2,Sheet2!$A$1:$B$10,2,0)

Is it possible to make this work for the whole column?

 

[AnotherHiggins]

What exactly is the error message you get? And what are you trying to do when you get it?

A google search for "List must be a deliminated list" turns up zero responses (as does a search for "List must be a delimited list") which suggests that is not the error message.

Also, just to make sure we're on the same page - you are going to use Data Validation in column B then a VLookup in column C?

[tt]_____
[blue]-John[/blue][/tt]

"Patriotism is your conviction that this country is superior to all other countries because you were born in it."
[tab]-George Bernard Shaw

Help us help you. Please read FAQ181-2886 before posting.

 

[transparent]

Sorted the delimination issue.

Right....

In my first sheet I now have:

SHEET 1

A B C
Name Paper Impact Factor
rick sun
John sun
frank Mirror
Ted Telegraph

The Paper columns values can be selected using a dropdown - populated from the table:

SHEET 2

Paper Impact

Sun 10
Mirror 7
Telegraph 9

I have defined the paper and impact column in sheet 2 as "PaperImpactFactors".

In Sheet 1, I now want to populate the Impact Factor column (C) based on the value held in Column B (Paper)

I have selected column C and added the formula:

=VLOOKUP(B,"PaperImpactFactors",2,FALSE)

I was expecting this to automatically populate the Impact Factors column (C) - however the column just contains #NAME?

What am I doing wrong?

 

[AnotherHiggins]

=VLOOKUP([red]B[/red],"PaperImpactFactors",2,FALSE)

B is not a valid cell reference. Try B2 (or whatever row you are on). The formula will automatically adjust as you fill the formula down.

[tt]_____
[blue]-John[/blue][/tt]

"Patriotism is your conviction that this country is superior to all other countries because you were born in it."
[tab]-George Bernard Shaw

Help us help you. Please read FAQ181-2886 before posting.