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

Copper Contributor

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)))