Entering a formula to Enter a Text Value in a different cell dependant on drop down box entry

[Jimbobmcwalton]

Been trying to get my worksheet to automatically enter a "Word" in a column based on the value of a drop down box currently containing 45 options.  I tried the IF function but I think I need to put all 45 options in the formula to get this to work and that cant be the best way ?

I have the drop down list that gets the data from a different sheet, this data grows as time goes on.

Its simply to add in a sales persons name depending on which account we are inputting on the sheet as I have to do this manually at the end of each month to create pivot charts. 

Can anyone help ?

[#2]

Hi Jimbob,

I would setup a vlookup with the 45 options.  See attached, does this work?

Tim

[#3]

Thanks for the Reply TJ

I did look at this but my look up value changes each time, with each different row - should this still work if this is the case?

[#4]

Version 2 Attached

Can you give me a better picture of the setup of your woksheet?  If I know how the data is entered and how the "word" is displayed I can provide a solution.

Tim

[#5]

Sure, maybe I should explain what it does a little better :) , I have a drop down list of 45 accounts, each account is selected from a list on a seperate sheet (which gets added to each week or so)  From this dropdown list we then select the account thats booking a job.  

We have 3 types of job for all accounts SameDay Dedicated or Route Cover and each account is attributed to one of 3 sales persons.  On Each account I would like these factors attributed to each of the accounts.

EG  Account       Sales Person    Account Type

      Aviva            John                SameDay Dedicated 

Currently we have to add in the account type & sales person manually but they never change so I would like the Sales Person & Account Type to be filled in in the correct cells.

Hope this makes sense 

Thanks in advance 

Jim

Many Thanks 

[#6]

ok, I hope that I got all of the moving parts!

I suggest that you have one tab (and maybe you already do) to set up the account info that doesn't change. Then each week when you select a particular account, the static info doesn't change but is automatically filled in.

See attached.  This would probably go quicker is you could send a blank copy of your model or screen shots! :)

Tim

[#7]

Thanks TJ,

I tried that last week but maybe due to the source data tab being in table format (i dont know) it tells me that it shoudl be a single list.

I have attached the sheet I will be using next month and reduced the accounts & couriers to just a couple so you can have a look and see what I am trying to do.  I have added the A/C Type and Sales Person Fields to the source tab I am trying to get the relevent data from so you can see the 3 types of accounts and the 3 sales people.

Thanks for your help 

Thanks for your help 

[#8]

Sorry for the delay.  Try attached.

Tim

[#9]

Hi Tim,

Sorry, I have been away.

Thanks for the reply.  I can see you have put Iforceco in one of the cells but don’t see the connection.

Could you elaborate for me, I don’t understand.

Thanks in advance

Jim