May 06 2019 03:45 PM - edited May 06 2019 04:09 PM
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".
May 06 2019 10:51 PM
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)))