What formula do I need? Vlookup?

[AJW1100]

Hi.
I have a list of 80 team members names on worksheet 1, each members full name in one cell listed in alphabetical order covering 80 cells in one column.

On worksheet 2 are the competitions we need members to play in, I need a formula so that I can enter initials in the competition list and have Excel automatically enter the full name in that cell. If 'Joe Bloggs' is listed in A1 of worksheet 1 I need to enter 'JB' in any cell over a set range on worksheet 2 to have Excel automatically enter 'Joe Bloggs' into that cell. Or, if I have a Joe Banks enter 'JBA' to have Excel enter 'Joe Banks'. I am sure you will see what I am trying to do here!

Once the names are entered I can then print the sheets for the members to see who is playing. We can have competitions every day of the week and having to enter long names over and over again is rather a pain as well as taking ages to do!!

I have searched the forum but most Q&A are way over my head...

Hope you can help.
Many thanks

Alan.

 

[Jose_Chasez]

An easier approach may be to use a drop down box on worksheet 2...

On worksheet 1, select your list of team members and assign it a name (e.g., TEAM_MEMBERS). You can name this cell range by going into Insert -> Name -> Define; or by simply typing the name into the little white box that is to the left of the formula bar (the box that usually shows you the name of the current cell).

On worksheet 2, click on one of the cells where you want to input/select the team members name. Here you can setup a drop down box by going into Data -> Validation.

In the "Allow" box, select "List"; and in the "Source" box type =TEAM_MEMBERS; and hit OK.

This will give you a drop down box where you can select the team members' name. You can copy this down to give you 80 drop-down boxes or however many you need.

Hi.
I have a list of 80 team members names on worksheet 1, each members full name in one cell listed in alphabetical order covering 80 cells in one column.

On worksheet 2 are the competitions we need members to play in, I need a formula so that I can enter initials in the competition list and have Excel automatically enter the full name in that cell. If 'Joe Bloggs' is listed in A1 of worksheet 1 I need to enter 'JB' in any cell over a set range on worksheet 2 to have Excel automatically enter 'Joe Bloggs' into that cell. Or, if I have a Joe Banks enter 'JBA' to have Excel enter 'Joe Banks'. I am sure you will see what I am trying to do here!

Once the names are entered I can then print the sheets for the members to see who is playing. We can have competitions every day of the week and having to enter long names over and over again is rather a pain as well as taking ages to do!!

I have searched the forum but most Q&A are way over my head...

Hope you can help.
Many thanks

Alan.

 

[AJW1100]

Hi Jose.

Thank you for the suggestion, I have tried it but must have got it wrong!

I have entered 7 names in cells A1:A7 on sheet 1, I highlighted these names and have entered Team_Members in the cell reference box, to the left of the formula bar.
I have, in cell B2, sheet 2 entered Team_Members as per your instructions. On dragging this down the column to fill 7 cells I have these cells filled with Team_Members and if I click on the drop down box the only words shown are Team_Members

I have also tried the Insert> Name>Define route with the same result..

If I try to enter anything into the range showing as Team_Members on sheet 2 I see the error message;
'The value you entered is not valid'
'A user has restricted values that can be entered into this cell'

Is there something I have missed?

Thanks again.

Alan.

 

[Jose_Chasez]

Sounds like you're very close. When you're in the data validation screen, make sure you type an equals sign in front of Team_Members in the "Source" box. I.e., the source box should contain =Team_Members

 

[AJW1100]

Hi Jose.
Yip, I missed the = sign, silly me! Your suggestion of course, works fine.

Last night however I remembered that a long time ago I used a formula (Originated by our IT guy before I retired) that was very close to give me what I need now. That formula was as follows:

=IF(B4="","",(VLOOKUP(B4,data1,2,FALSE)))

The above formula was in sheet 2 cell B3, and in B4 was the part number of the item, the DATA sheet contained the part number in column A and the barcode in Column B. When I entered the part number in B4 then B3 displayed the barcode. Hope that makes sense!

I have tried to modify the formula to make it work with A3 instead of B4, or in other words to be able to enter the part number in the row instead of the column. It does work with B3 and B4 but not with A3 and B3.

I also wish I knew how to explain this properly so you can easily understand what I am getting at.

If you can't I quite understand as I often have these Senior moments

Many thanks

Alan.