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


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 ?



8 Replies

Hi Jimbob,


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



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?

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.



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 




Many Thanks 

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! :)





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 

Sorry for the delay.  Try attached.



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