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

%3CLINGO-SUB%20id%3D%22lingo-sub-310357%22%20slang%3D%22en-US%22%3EEntering%20a%20formula%20to%20Enter%20a%20Text%20Value%20in%20a%20different%20cell%20dependant%20on%20drop%20down%20box%20entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-310357%22%20slang%3D%22en-US%22%3E%3CP%3EBeen%20trying%20to%20get%20my%20worksheet%20to%20automatically%20enter%20a%20%22Word%22%20in%20a%20column%20based%20on%20the%20value%20of%20a%20drop%20down%20box%20currently%20containing%2045%20options.%26nbsp%3B%20I%20tried%20the%20IF%20function%20but%20I%20think%20I%20need%20to%20put%20all%2045%20options%20in%20the%20formula%20to%20get%20this%20to%20work%20and%20that%20cant%20be%20the%20best%20way%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20the%20drop%20down%20list%20that%20gets%20the%20data%20from%20a%20different%20sheet%2C%20this%20data%20grows%20as%20time%20goes%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIts%20simply%20to%20add%20in%20a%20sales%20persons%20name%20depending%20on%20which%20account%20we%20are%20inputting%20on%20the%20sheet%20as%20I%20have%20to%20do%20this%20manually%20at%20the%20end%20of%20each%20month%20to%20create%20pivot%20charts.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-310357%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-321554%22%20slang%3D%22en-US%22%3ERe%3A%20Entering%20a%20formula%20to%20Enter%20a%20Text%20Value%20in%20a%20different%20cell%20dependant%20on%20drop%20down%20box%20entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-321554%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Tim%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%2C%20I%20have%20been%20away.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20reply.%26nbsp%3B%20I%20can%20see%20you%20have%20put%20Iforceco%20in%20one%20of%20the%20cells%20but%20don%E2%80%99t%20see%20the%20connection.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20elaborate%20for%20me%2C%20I%20don%E2%80%99t%20understand.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJim%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-313023%22%20slang%3D%22en-US%22%3ERe%3A%20Entering%20a%20formula%20to%20Enter%20a%20Text%20Value%20in%20a%20different%20cell%20dependant%20on%20drop%20down%20box%20entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-313023%22%20slang%3D%22en-US%22%3E%3CP%3ESorry%20for%20the%20delay.%26nbsp%3B%20Try%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETim%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-312270%22%20slang%3D%22en-US%22%3ERe%3A%20Entering%20a%20formula%20to%20Enter%20a%20Text%20Value%20in%20a%20different%20cell%20dependant%20on%20drop%20down%20box%20entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-312270%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20TJ%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20that%20last%20week%20but%20maybe%20due%20to%20the%20source%20data%20tab%20being%20in%20table%20format%20(i%20dont%20know)%20it%20tells%20me%20that%20it%20shoudl%20be%20a%20single%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20the%20sheet%20I%20will%20be%20using%20next%20month%20and%20reduced%20the%20accounts%20%26amp%3B%20couriers%20to%20just%20a%20couple%20so%20you%20can%20have%20a%20look%20and%20see%20what%20I%20am%20trying%20to%20do.%26nbsp%3B%20I%20have%20added%20the%20A%2FC%20Type%20and%20Sales%20Person%20Fields%20to%20the%20source%20tab%20I%20am%20trying%20to%20get%20the%20relevent%20data%20from%20so%20you%20can%20see%20the%203%20types%20of%20accounts%20and%20the%203%20sales%20people.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-310635%22%20slang%3D%22en-US%22%3ERe%3A%20Entering%20a%20formula%20to%20Enter%20a%20Text%20Value%20in%20a%20different%20cell%20dependant%20on%20drop%20down%20box%20entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-310635%22%20slang%3D%22en-US%22%3E%3CP%3Eok%2C%20I%20hope%20that%20I%20got%20all%20of%20the%20moving%20parts!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suggest%20that%20you%20have%20one%20tab%20(and%20maybe%20you%20already%20do)%20to%20set%20up%20the%20account%20info%20that%20doesn't%20change.%20Then%20each%20week%20when%20you%20select%20a%20particular%20account%2C%20the%20static%20info%20doesn't%20change%20but%20is%20automatically%20filled%20in.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20attached.%26nbsp%3B%20This%20would%20probably%20go%20quicker%20is%20you%20could%20send%20a%20blank%20copy%20of%20your%20model%20or%20screen%20shots!%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3ETim%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-310462%22%20slang%3D%22en-US%22%3ERe%3A%20Entering%20a%20formula%20to%20Enter%20a%20Text%20Value%20in%20a%20different%20cell%20dependant%20on%20drop%20down%20box%20entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-310462%22%20slang%3D%22en-US%22%3E%3CP%3ESure%2C%20maybe%20I%20should%20explain%20what%20it%20does%20a%20little%20better%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20%2C%20I%20have%20a%20drop%20down%20list%20of%2045%20accounts%2C%20each%20account%20is%20selected%20from%20a%20list%20on%20a%20seperate%20sheet%20(which%20gets%20added%20to%20each%20week%20or%20so)%26nbsp%3B%20From%20this%20dropdown%20list%20we%20then%20select%20the%20account%20thats%20booking%20a%20job.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20have%203%20types%20of%20job%20for%20all%20accounts%20SameDay%20Dedicated%20or%20Route%20Cover%20and%20each%20account%20is%20attributed%20to%20one%20of%203%20sales%20persons.%26nbsp%3B%20On%20Each%20account%20I%20would%20like%20these%20factors%20attributed%20to%20each%20of%20the%20accounts.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEG%26nbsp%3B%20Account%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BSales%20Person%26nbsp%3B%20%26nbsp%3B%20Account%20Type%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Aviva%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20John%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20SameDay%20Dedicated%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrently%20we%20have%20to%20add%20in%20the%20account%20type%20%26amp%3B%20sales%20person%20manually%20but%20they%20never%20change%20so%20I%20would%20like%20the%20Sales%20Person%20%26amp%3B%20Account%20Type%20to%20be%20filled%20in%20in%20the%20correct%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20makes%20sense%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJim%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20Thanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-310445%22%20slang%3D%22en-US%22%3ERe%3A%20Entering%20a%20formula%20to%20Enter%20a%20Text%20Value%20in%20a%20different%20cell%20dependant%20on%20drop%20down%20box%20entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-310445%22%20slang%3D%22en-US%22%3E%3CP%3EVersion%202%20Attached%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20give%20me%20a%20better%20picture%20of%20the%20setup%20of%20your%20woksheet%3F%26nbsp%3B%20If%20I%20know%20how%20the%20data%20is%20entered%20and%20how%20the%20%22word%22%20is%20displayed%20I%20can%20provide%20a%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETim%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-310423%22%20slang%3D%22en-US%22%3ERe%3A%20Entering%20a%20formula%20to%20Enter%20a%20Text%20Value%20in%20a%20different%20cell%20dependant%20on%20drop%20down%20box%20entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-310423%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20the%20Reply%20TJ%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20did%20look%20at%20this%20but%20my%20look%20up%20value%20changes%20each%20time%2C%20with%20each%20different%20row%20-%20should%20this%20still%20work%20if%20this%20is%20the%20case%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-310404%22%20slang%3D%22en-US%22%3ERe%3A%20Entering%20a%20formula%20to%20Enter%20a%20Text%20Value%20in%20a%20different%20cell%20dependant%20on%20drop%20down%20box%20entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-310404%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jimbob%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20setup%20a%20vlookup%20with%20the%2045%20options.%26nbsp%3B%20See%20attached%2C%20does%20this%20work%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETim%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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?

 

Tim

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.

 

Tim

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 

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

 

 

 

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.

 

Tim

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