I would like to have the function check a specific cell for a certain string, if it returns true then to place the value of the adjacent cell on another page within the worksheet, if it returns false, then it should check the next cell, and if THAT is true, return the value of the adjacent cell to THAT one, and if it returns false, then it should check the next cell, etc.
Basically, here is what we are dealing with...
http://img21.imageshack.us/img21/881...endeelist.jpg#
So I want the Function to look for "x" in B3, and if it returns true, then place the value of A3 in the cell on the other page...if it returns false, then look for the next cell in column B that returns true for "x", and then return the value of A4 to the cell in the other page of the worksheet...
What I am doing is Column A is a list of invitees, and column B has an "x" in it if the plan to attend...
On the other sheet, I want to create a list of the "people who will attend" with no blank cells appearing...meaning, if the person is not attending, therefore having no "x" in column B, then excel will automatically check the next cell in column B for an "x"...
here is where it comes to a point where I cannot figure out what to do...once you DO get a true value, then you cannot just copy the formula to the next cell on the other page, because it will not necessarily start with the correct one...
If the first person who is attending in in A7, then my formula when copied into the next cell (that only increases by one, beginning with B4, will duplicate the same result as the one before it...
not to mention that my formula only works if there aren't gaps bigger than 10 cells in between TRUE results...can anyone help?
Here is my formula (the page I am pullling from is labeled 'Attendees'):
=IF(Attendees!B3 = "x", Attendees!A3, IF(Attendees!B4="x", Attendees!A4, IF(Attendees!B5="x", Attendees!A5, IF(Attendees!B6="x", Attendees!A6, IF(Attendees!B7="x", Attendees!A7, IF(Attendees!B8="x", Attendees!A8, IF(Attendees!B9="x", Attendees!A9, IF(Attendees!B10="x", Attendees!A10, DONE))))))))
Bookmarks