SOLVED

Create a formula to fill in data based on cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1606128%22%20slang%3D%22en-US%22%3ECreate%20a%20formula%20to%20fill%20in%20data%20based%20on%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1606128%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20create%20a%20formula%20that%20fills%20in%20Cell%20(column)%20BX%20based%20upon%20the%20input%20in%20cell%20AX.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA1%3A%20Cow%3C%2FP%3E%3CP%3EA2%3A%20Chicken%3C%2FP%3E%3CP%3EA3%3A%20Pig%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EB1%3A%20%24400.00%3C%2FP%3E%3CP%3EB2%3A%20%245.00%3C%2FP%3E%3CP%3EB3%3A%20%2450.00%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20my%20thoughts%20are%20something%20like%20B1%3A%20%3DIF(A1%3DCow%2C%24400.00%2C(A1%3DChicken%2C%245.00%2C(A1%3DPig%2C%22%2450.00)))%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20that%20formula%20doesn't%20work.%20Is%20there%20a%20place%20I%20can%20look%20up%20formulas%20to%20try%20to%20assemble%20a%20formula%20like%20what%20I'm%20looking%20for%3F%20I%20searched%20for%20formulas%20and%20it%20seems%20like%20it%20will%20have%20to%20be%20some%20type%20of%20VB%20but%20I%20don't%20know%20VB%20so...%20any%20help%20would%20be%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20want%20to%20do%20is%20type%20something%20(i.e.%20Cow%2C%20Pig%2C%20Chicken)%20in%20column%20A%20and%20then%20B%20returns%20a%20dollar%20value%20based%20upon%20the%20text%20I've%20entered%20in%20column%20A.%20BUT%20only%20for%20Rows%2021%20-%2050%20(or%20whatever).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERF%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1606128%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%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1606148%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20a%20formula%20to%20fill%20in%20data%20based%20on%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1606148%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1606149%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20a%20formula%20to%20fill%20in%20data%20based%20on%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1606149%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767963%22%20target%3D%22_blank%22%3E%40RepublicsFury%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome!%20Did%20that%20resolve%20your%20issue%3F%20If%20yes%2C%20please%20take%20a%20minute%20to%20accept%20the%20post%20with%20the%20proposed%20solution%20as%20a%20Best%20Response%20in%20order%20to%20mark%20your%20question%20as%20Solved.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1606150%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20a%20formula%20to%20fill%20in%20data%20based%20on%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1606150%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENew%20at%20the%20posting%20and%20accepting...%20.thanks%20for%20the%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20I%20did%20it%20right%20and%20accepted%20that%20as%20the%20right%20response.%20It%20did%20solve%20the%20issue.%20I'll%20create%20the%20Price%20List%20on%20a%20Worksheet%20and%20then%20use%20that%20as%20the%20reference%20for%20the%20formula%20on%20each%20other%20Worksheet%20inside%20the%20Workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20RF%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1606202%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20a%20formula%20to%20fill%20in%20data%20based%20on%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1606202%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767963%22%20target%3D%22_blank%22%3E%40RepublicsFury%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%20You%20correctly%20marked%20the%20proposed%20solution%20as%20a%20Best%20Response.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStay%20safe%20and%20healthy%3C%2FP%3E%3CP%3EHave%20a%20nice%20weekend!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1606147%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20a%20formula%20to%20fill%20in%20data%20based%20on%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1606147%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767963%22%20target%3D%22_blank%22%3E%40RepublicsFury%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20should%20be%20like%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(A1%3D%22Cow%22%2C400%2CIF(A1%3D%22Chicken%22%2C5%2CIF(A1%3D%22Pig%22%2C50%2C%22%22)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I%20would%20suggest%20that%20instead%20of%20hard%20coding%20the%20item%20and%20it's%20price%20in%20the%20above%20formula%2C%20you%20should%20create%20a%20price%20list%20somewhere%20in%20the%20file%20and%20then%20you%20can%20use%20VLookup%20formula%20to%20get%20the%20priced%20of%20the%20item%20you%20input%20in%20column%20B.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20attached%20has%20two%20sheets%20which%20show%20both%20the%20methods.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I would like to create a formula that fills in Cell (column) BX based upon the input in cell AX.

 

A1: Cow

A2: Chicken

A3: Pig

 

B1: $400.00

B2: $5.00

B3: $50.00

 

So my thoughts are something like B1: =IF(A1=Cow,$400.00,(A1=Chicken,$5.00,(A1=Pig,"$50.00))) 

 

But that formula doesn't work. Is there a place I can look up formulas to try to assemble a formula like what I'm looking for? I searched for formulas and it seems like it will have to be some type of VB but I don't know VB so... any help would be appreciated.

 

What I want to do is type something (i.e. Cow, Pig, Chicken) in column A and then B returns a dollar value based upon the text I've entered in column A. BUT only for Rows 21 - 50 (or whatever).

 

Regards,

 

RF

5 Replies
Best Response confirmed by RepublicsFury (Occasional Contributor)
Solution

@RepublicsFury 

 

The formula should be like this...

 

=IF(A1="Cow",400,IF(A1="Chicken",5,IF(A1="Pig",50,"")))

 

But I would suggest that instead of hard coding the item and it's price in the above formula, you should create a price list somewhere in the file and then you can use VLookup formula to get the priced of the item you input in column B.

 

The attached has two sheets which show both the methods.

 

@RepublicsFury 

You're welcome! Did that resolve your issue? If yes, please take a minute to accept the post with the proposed solution as a Best Response in order to mark your question as Solved.

@Subodh_Tiwari_sktneer 

 

New at the posting and accepting... .thanks for the help.

 

I think I did it right and accepted that as the right response. It did solve the issue. I'll create the Price List on a Worksheet and then use that as the reference for the formula on each other Worksheet inside the Workbook.

 

Thanks again!

 

- RF

@RepublicsFury 

Thanks! You correctly marked the proposed solution as a Best Response.

 

Stay safe and healthy

Have a nice weekend!