Assign numeric value to text cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1250780%22%20slang%3D%22en-US%22%3EAssign%20numeric%20value%20to%20text%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1250780%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I'm%20making%20a%20pricing%20spreadhseet%20and%20I%20need%20to%20assign%20a%20numeric%20value%20to%20a%20text%20a%20cell%20which%20can%20be%20selected%20from%20a%20multiple%20option%20list%20that%20lies%20in%20another%20sheet%20in%20the%20same%20document.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20text%20cells%20I'm%20working%20with%20are%20in%20the%20second%20sheet%20of%20the%20book%2C%20the%20format%20is%20text%20cell%20followed%20by%20the%20price%20(example%3A%20EQUIPOS%20and%20precio%20USD).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20want%20to%20do%20is%20to%20select%20the%20name%20of%20an%20item%20in%20the%20multiple%20option%20lists%20in%20the%20first%20sheet%20but%20using%20the%20numeric%20value%20assigned%20to%20each%20option%20in%20my%20pricing%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1250780%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1251019%22%20slang%3D%22en-US%22%3ERe%3A%20Assign%20numeric%20value%20to%20text%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1251019%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F593649%22%20target%3D%22_blank%22%3E%40Diego_Samano%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20understand%20what%20you're%20asking%2C%20it's%20VLOOKUP%20that%20you%20need.%20I've%20started%20it%20for%20you%20in%20the%20first%20three%20items%20on%20your%20list%2C%20with%20the%20assumption%20that%20you%20can%20follow%20the%20example%20and%20finish%20the%20rest.%20Feel%20free%20to%20come%20back%20with%20questions%20if%20you%20still%20have%20them.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1991190%22%20slang%3D%22en-US%22%3ERe%3A%20Assign%20numeric%20value%20to%20text%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1991190%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3Bthat's%20brilliant...%20would%20you%20mind%20if%20I%20asked%20you%20a%20similar%20question%3F%3C%2FP%3E%3CP%3EI%20have%20a%20table%20that%20I'm%20trying%20to%20make%20where%20I%20assign%20an%20arbitrary%20value%20to%20a%20few%20different%20items%20-%20I'd%20like%20to%20make%20it%20easy%20for%20someone%20working%20with%20me%20to%20assign%20photo%20sessions%20so%20that%20it's%20fair%20for%20all%20the%20photographers%2C%20without%20them%20burning%20half%20they%20grey%20matter%20every%20time%20they%20have%20to%20try%20and%20balance%20it.%3C%2FP%3E%3CP%3EThe%20idea%20is%20that%20%22Sold%22%20or%20%22Not%20Sold%22%20would%20be%20equal%20to%201%20or%200%20(self-explanatory)%2C%20where%20the%20clients%20are%20staying%20is%20either%202%20or%203%20value-wise%2C%20and%20how%20many%20people%20are%20in%20the%20group%20is%20either%201%2C%202%2C%204%2C%20or%205%2C%20based%20on%20how%20much%20they%20would%20spend%20on%20average%20-%20Individual%2C%20Couple%2C%20Family%2C%20Group%2C%20respectively.%20I%20started%20it%2C%20but%20I'm%20really%20confused...%20I%20tried%20to%20use%20a%20combination%20of%20COUNTIF%20and%20VLOOKUP%2C%20but%20I%20wasn't%20able%20to%20get%20the%20correct%20result.%20Basically%2C%20I%20would%20need%20the%20%22Value%22%20to%20be%20equal%20to%20%22Sold%2FNot%20Sold%22*%22Place%22*%22People%22%2C%20where%20all%20of%20those%20have%20the%20assigned%20value.%20I%20would%20appreciate%20it%20if%20you%20had%20any%20pointers...%20Thank%20you%2C%20even%20for%20just%20reading%20this.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1991224%22%20slang%3D%22en-US%22%3ERe%3A%20Assign%20numeric%20value%20to%20text%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1991224%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3BI'm%20still%20messing%20around%20with%20it%2C%20if%20I%20crack%20it%2C%20I'll%20update...%20Thank%20you%20very%20much.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1991833%22%20slang%3D%22en-US%22%3ERe%3A%20Assign%20numeric%20value%20to%20text%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1991833%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F903122%22%20target%3D%22_blank%22%3E%40AndreiMihalache%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKeep%20messing%20around.%20I%20just%20saw%20this%20and%20am%20busy%20the%20rest%20of%20the%20evening.%20Will%20not%20be%20able%20to%20look%20at%20it%20until%20tomorrow%20morning.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1992351%22%20slang%3D%22en-US%22%3ERe%3A%20Assign%20numeric%20value%20to%20text%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1992351%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F903122%22%20target%3D%22_blank%22%3E%40AndreiMihalache%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20this%20is%20what%20you%20are%20attempting%20to%20do%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1993440%22%20slang%3D%22en-US%22%3ERe%3A%20Assign%20numeric%20value%20to%20text%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1993440%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F903122%22%20target%3D%22_blank%22%3E%40AndreiMihalache%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20added%20one%20enhancement%20to%20the%20solution%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%20provided%3A%20I%20converted%20the%20list%20of%20places%20into%20a%20Table%2C%20so%20that%20it's%20possible%20to%20add%20new%20names%20and%20values%20to%20it%2C%20and%20those%20automatically%20cascade%20to%20the%20rest%20of%20the%20spreadsheet.%20That's%20done%20with%20the%20assumption%20that%20you%20might%20want%20to%20add%20further%20variables%20in%20the%20future%3B%20if%20that%20will%20never%20happen%2C%20it's%20unnecessary.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20same%20could%20be%20done%20to%20the%20other%20tables.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello, I'm making a pricing spreadhseet and I need to assign a numeric value to a text a cell which can be selected from a multiple option list that lies in another sheet in the same document.

 

The text cells I'm working with are in the second sheet of the book, the format is text cell followed by the price (example: EQUIPOS and precio USD).

 

What I want to do is to select the name of an item in the multiple option lists in the first sheet but using the numeric value assigned to each option in my pricing formula.

6 Replies

@Diego_Samano 

If I understand what you're asking, it's VLOOKUP that you need. I've started it for you in the first three items on your list, with the assumption that you can follow the example and finish the rest. Feel free to come back with questions if you still have them.

@mathetes that's brilliant... would you mind if I asked you a similar question?

I have a table that I'm trying to make where I assign an arbitrary value to a few different items - I'd like to make it easy for someone working with me to assign photo sessions so that it's fair for all the photographers, without them burning half they grey matter every time they have to try and balance it.

The idea is that "Sold" or "Not Sold" would be equal to 1 or 0 (self-explanatory), where the clients are staying is either 2 or 3 value-wise, and how many people are in the group is either 1, 2, 4, or 5, based on how much they would spend on average - Individual, Couple, Family, Group, respectively. I started it, but I'm really confused... I tried to use a combination of COUNTIF and VLOOKUP, but I wasn't able to get the correct result. Basically, I would need the "Value" to be equal to "Sold/Not Sold"*"Place"*"People", where all of those have the assigned value. I would appreciate it if you had any pointers... Thank you, even for just reading this.

@mathetes I'm still messing around with it, if I crack it, I'll update... Thank you very much.

@AndreiMihalache 

 

Keep messing around. I just saw this and am busy the rest of the evening. Will not be able to look at it until tomorrow morning.

@AndreiMihalache 

 

I think this is what you are attempting to do?

@AndreiMihalache 

 

I've added one enhancement to the solution @JMB17 provided: I converted the list of places into a Table, so that it's possible to add new names and values to it, and those automatically cascade to the rest of the spreadsheet. That's done with the assumption that you might want to add further variables in the future; if that will never happen, it's unnecessary.

 

The same could be done to the other tables.