Home

Is it possible for excel to recognize text in one cell to generate value of another cell?

%3CLINGO-SUB%20id%3D%22lingo-sub-538315%22%20slang%3D%22en-US%22%3EIs%20it%20possible%20for%20excel%20to%20recognize%20text%20in%20one%20cell%20to%20generate%20value%20of%20another%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-538315%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20new%20at%20the%20more%20intermediate%20functions%20of%20excel%20-%20so%20please%20bare%20with%20me.%20Here's%20my%20question.%3C%2FP%3E%3CP%3ECan%20you%20determine%20the%20value%20of%20a%20cell%20on%20one%20tab%20based%20on%20the%20text%20of%20a%20cell%20on%20a%20different%20tab.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20tracking%20inventory%20of%20CDs.%20We%20have%206%20different%20CDs%20stored%20at%205%20different%20locations.%20(Each%20location%20has%20all%206%20CDs).%26nbsp%3B%20%26nbsp%3BIf%20I%20have%20to%20ship%2C%20I%20have%20to%20subtract%20from%20location%201%20%22tab%20A%22%20and%20add%20to%20location%202%20%22tab%20B%22%20or%20location%203%20%22tab%20C%22%20or%20whatever.%26nbsp%3B%20I'm%20trying%20to%20find%20if%20there's%20a%20way%20to%20do%20this%20in%20one%20step.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20excel%2C%20my%20Cds%20are%20listed%20across%20top%20row%201.%26nbsp%3B%20Column%20A%20is%20the%20info%20of%20where%20I'm%20shipping.%20Column%20B%20is%20the%20date.%26nbsp%3B%20Columns%20C-H%20are%20the%20individual%20CDs%20and%20where%20I%20type%20how%20many%20of%20CDs%20are%20being%20shipped.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet's%20say%20I%20need%20to%20ship%2025%20CDd%20from%20location%202%20to%20location%203.%26nbsp%3B%20So%2C%20on%20tab%202%20(location%202)%26nbsp%3B%3CSPAN%3EI%20want%20to%20be%20able%20to%20type%20%22to%20Location3%22%20in%20say%20cell%20A4%20(the%20info%20column).%26nbsp%3B%20Then%20in%20adjoining%20columns%20of%20CDs%2C%20I%20type%20the%20number%20of%20CDs%20I'm%20shipping%20out%2C%20so%2C%20I'd%20type%20%22-25%20in%20cell%20C4%22.%26nbsp%3B%20I%20want%20excel%20to%20add%20that%2025%20to%20cell%20C4%20on%20tab%203%20(location%203).%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20make%20excel%20recognize%20text%20this%20way%3F%26nbsp%3BThanks%20for%20any%20help%20-%20even%20if%20it's%20%22no%2C%20that's%20not%20possible%22.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-538315%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-539222%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20it%20possible%20for%20excel%20to%20recognize%20text%20in%20one%20cell%20to%20generate%20value%20of%20another%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-539222%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F336108%22%20target%3D%22_blank%22%3E%40tgeer72%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suggest%20you%20modify%20the%20layout%20of%20your%20data%2C%20similar%20to%20the%20attached%20file.%20The%20formula%20in%20I3%2C%20copied%20down%20rows%20and%20across%20columns%2C%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DSUMPRODUCT((%24E%242%3A%24E%2425*(%24C%242%3A%24C%2425%3D%24H3)*(%24D%242%3A%24D%2425%3DI%242))-%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E(%24E%242%3A%24E%2425*(%24B%242%3A%24B%2425%3D%24H3)*(%24D%242%3A%24D%2425%3DI%242)))%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
tgeer72
Occasional Visitor

I'm new at the more intermediate functions of excel - so please bare with me. Here's my question.

Can you determine the value of a cell on one tab based on the text of a cell on a different tab.

 

I am tracking inventory of CDs. We have 6 different CDs stored at 5 different locations. (Each location has all 6 CDs).   If I have to ship, I have to subtract from location 1 "tab A" and add to location 2 "tab B" or location 3 "tab C" or whatever.  I'm trying to find if there's a way to do this in one step. 

 

In excel, my Cds are listed across top row 1.  Column A is the info of where I'm shipping. Column B is the date.  Columns C-H are the individual CDs and where I type how many of CDs are being shipped. 

 

Let's say I need to ship 25 CDd from location 2 to location 3.  So, on tab 2 (location 2) I want to be able to type "to Location3" in say cell A4 (the info column).  Then in adjoining columns of CDs, I type the number of CDs I'm shipping out, so, I'd type "-25 in cell C4".  I want excel to add that 25 to cell C4 on tab 3 (location 3). 

 

Is there a way to make excel recognize text this way? Thanks for any help - even if it's "no, that's not possible".

1 Reply

@tgeer72 

I suggest you modify the layout of your data, similar to the attached file. The formula in I3, copied down rows and across columns, is: 

=SUMPRODUCT(($E$2:$E$25*($C$2:$C$25=$H3)*($D$2:$D$25=I$2))-
($E$2:$E$25*($B$2:$B$25=$H3)*($D$2:$D$25=I$2)))