We help IT Professionals succeed at work.

Excel, select proper data

dlan75
dlan75 asked
on
Medium Priority
235 Views
Last Modified: 2012-05-12
Hi all,

Ok here is what I want to do.
On colum A I enter a name (from a predfined list and the values are in another excel page) and as soon as it is selected, I want to show up in colum B the numer that correspond to that name (the values are in that other excel page next to the list of names).
hope that it is clear enough.

thx in advance
Comment
Watch Question

Commented:
You can use a Data Validation to select the data and a COUNTIF to perform the count of matches on the list.

Please take a look at the attached file.

jppinto
Countif-Example.xlsx
CERTIFIED EXPERT

Author

Commented:
it is nearly that :-) but not exaclty.
Let me try to make it clearer.

Sheet 2 column A I have names, column B I have ages
on sheet 1 if I select one of the names that is on sheet 2, I want to see the corresponding age on column B
(not so easy to explain :))
CERTIFIED EXPERT
Commented:
I may have misunderstood the question but I think you need a lookup like the attached

Let me know if you want explanation or different data sizes

reg

names.xls
CERTIFIED EXPERT

Author

Commented:
Ok that's it ! Now I need to duplicate the formula to several cells without changing the namelist cells ! (I use the little cross then drag down but it changes also the namelist cell name so it doesn't work)
Commented:
Just change the type of formula to a VLOOKUP, like this:

=VLOOKUP(A1,Sheet2!A1:B3,2,FALSE)

jppinto
Countif-Example.xlsx
CERTIFIED EXPERT

Author

Commented:
that works except when I need to duplicate the formula for the following lines
CERTIFIED EXPERT

Author

Commented:
ok found it :-) just needed to add a $ sign in order to fix the data. Thank you guys

Commented:
That's right!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.