Formula for spreadsheet help

%3CLINGO-SUB%20id%3D%22lingo-sub-1696654%22%20slang%3D%22en-US%22%3EFormula%20for%20spreadsheet%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1696654%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20would%20like%20to%20build%20a%20spreadsheet%20and%20have%20the%20spreadsheet%20enter%20a%20particular%20number%20in%20the%20cell%20next%20to%20a%20particular%20word.%20For%20example%2C%20each%20time%20a%20cell%20has%26nbsp%3B%20the%20word%20labor%2C%20the%20cell%20to%20the%20right%20would%20return%2025%2C%20which%20would%20be%20the%20value%20of%20a%20cell.%20That%20way%20if%20the%20value%20in%2C%20say%20A1%20changes%20to%2027%20then%20labor%20everywhere%20in%20the%20spreadsheet%20would%20change%20to%2027.%3C%2FP%3E%3CP%3EIt%20would%20be%20even%20better%20if%20the%20cell%20could%20return%20a%20calculated%20result%3A%3C%2FP%3E%3CP%3ECell%20c1%3Dlabor%20cell%20d1%3D.13%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3Bcell%20e1%20would%20return%20A1*d1%26nbsp%3B%20where%20A1%20is%20always%20used%20each%20time%20labor%20comes%20up.%26nbsp%3B%20So%20the%20answer%20here%20would%20be%203.25.%20If%20the%20next%20line%20is%20c2%3Dlabor%20d2%3D.20%20cell%20e2%20would%20be%2025*.20%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20I'm%20making%20some%20sense.%20I%20played%20around%20with%20the%20IF%20function%20and%20the%20VLOOKUP%20and%20those%20seem%20to%20be%20close%2C%20but%20not%20quite%20it.%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3EKathy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1696654%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1700590%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20spreadsheet%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1700590%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F800792%22%20target%3D%22_blank%22%3E%40Hare0319%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20I'll%20give%20it%20a%20try!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1696701%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20spreadsheet%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1696701%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F802274%22%20target%3D%22_blank%22%3E%40Kathy1485%3C%2FA%3E%26nbsp%3BHi%2C%20I've%20tried%20the%20following%20formula%2C%20it%20seems%20can%20handle%20your%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIF(C1%3D%22%3CSPAN%3Elabor%3C%2FSPAN%3E%22%2C%20%24A%241*d1%2C%20%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20noticed%20you%20mentioned%20that%20you've%20tried%20%22IF%22%20around%2C%20but%20there's%20some%20problem%20still.%20I%20guess%20the%20problem%20you%20have%20might%20be%20related%20reference%20of%20A1.%20If%20we%20use%20absolute%20reference%2C%20it%20would%20always%20refer%20to%20A1%20as%20you%20wish.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20let%20me%20know%20if%20this%20works%20for%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESc%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

I would like to build a spreadsheet and have the spreadsheet enter a particular number in the cell next to a particular word. For example, each time a cell has  the word labor, the cell to the right would return 25, which would be the value of a cell. That way if the value in, say A1 changes to 27 then labor everywhere in the spreadsheet would change to 27.

It would be even better if the cell could return a calculated result:

Cell c1=labor cell d1=.13       cell e1 would return A1*d1  where A1 is always used each time labor comes up.  So the answer here would be 3.25. If the next line is c2=labor d2=.20 cell e2 would be 25*.20

 

I hope I'm making some sense. I played around with the IF function and the VLOOKUP and those seem to be close, but not quite it.

Thanks!

Kathy

2 Replies

@Kathy1485 Hi, I've tried the following formula, it seems can handle your problem.

 

IF(C1="labor", $A$1*d1, "")

 

I noticed you mentioned that you've tried "IF" around, but there's some problem still. I guess the problem you have might be related reference of A1. If we use absolute reference, it would always refer to A1 as you wish.

 

Please let me know if this works for you.

 

Best,

 

Sc

@Hare0319 

 

Thanks, I'll give it a try!