yogi_Pull Data From Another Sheet Based On Specified Criteria

                Google Spreadsheet   Post  #1836

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov-18-2014

post by cherold:
(https://productforums.google.com/forum/#!mydiscussions/docs/AwF7X4AhKLs)
Looking for a formula to check one sheet's data against another

I have a spreadsheet that I use to keep track of submitted short stories. It has one sheet containing a list of publications, one containing a list of my stories, and a submissions sheet with pull down menus where I can choose which story is out at which magazine.

What I would like to do is have a way where when I record a new submission, I can have a field in the story sheet that automatically fills in with that magazine. In other words, the field in the story sheet would contain a formula that would look for a story name in the submissions sheet, and if the status field of that entry is not "rejected" then it would show the publication name of that row.

Is that at all possible? 

---

Alright, as Yogi Anand requested, here is a copy of the spreadsheet I created. I made it editable by anyone with this link. Hopefully I can explain what I'd like to happen clearly.

There are three tabs. One is Markets, which has a list of publications in Column A. 

There is also a tab called Pieces, listing story titles, once again in column A. Column E is called locations. Locations is the column I'm hoping I can get information automatically written to.

The third tab is Submissions. Submissions Column A pulls data from Column A of Pieces for a pulldown menu and Submissions Column B pulls data from Column A of Markets. This allows me to choose which piece has been sent to which market. Column E indicates the status of a piece, that is whether it has been submitted, acknowledged, or rejected.

What I would like is for the Location column of Pieces to show where that piece is, which would be either at a publication, or with me. 

So let's say I go to the Submissions tab. In column A I choose "Story Sample." In column B I choose "Asimov Magazine." At this point, Pieces, in Column E of the row containing "Story Sample" in Column A, should magically read "Asimov Magazine." If I write "rejected," then Column E for that row would change to the cell default: "home." If it doesn't say "rejected" there in Submissions Column E, then the assumption is the story is at Asimov. In my example you can see "Story Sample" is listed in both rows 8 and 9. Row 8 would be ignored because it has "rejected" in column E, while Row 9 is a live row indicating where "Story Sample" is.

In short, I need column E in for each row in Pieces to tell me if the story in that row is mentioned in any row in Submissions Column A in a row where the Column E for that row does not say "rejected."

Is that clear? And if so, is that possible?

---------------------------------------------------------------------------------------