is there a formula with the IF function that would work?

**jgrever2** · 10-11-2011, 03:35 PM

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

**NBVC** · 10-11-2011, 03:41 PM

Try:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down as far as needed.

**Andrew-R** · 10-11-2011, 03:44 PM

The attached workbook has a list with X's on sheet 1 and a formula on sheet 2 to handle up to 100 attendees (it can be extended quite easily).

The formula used on sheet 2 is =IF(ROW()<=COUNTIF(Sheet1!B:B,"X"),INDEX(Sheet1!A:A,LARGE(INDEX(ROW(Sheet1!$A$1:$A$100) * --(Sheet1!$B$1:$B$100="X"),0),COUNTIF(Sheet1!B:B,"X")-(ROW()-1)),1),"")

Hope this helps.

**jgrever2** · 10-11-2011, 04:02 PM

Originally Posted by Andrew-R

The attached workbook has a list with X's on sheet 1 and a formula on sheet 2 to handle up to 100 attendees (it can be extended quite easily).

The formula used on sheet 2 is =IF(ROW()<=COUNTIF(Sheet1!B:B,"X"),INDEX(Sheet1!A:A,LARGE(INDEX(ROW(Sheet1!$A$1:$A$100) * --(Sheet1!$B$1:$B$100="X"),0),COUNTIF(Sheet1!B:B,"X")-(ROW()-1)),1),"")

Hope this helps.

so this comes close, except for some reason it begins with the first one being Jan Arthur - from my example image...why would this happen?

I actually don't understand your formula well enough I guess, because I cannot seem to figure out why it would start with her...but at least all of the names after Jan are working...

**Andrew-R** · 10-11-2011, 04:06 PM

Originally Posted by jgrever2

so this comes close, except for some reason it begins with the first one being Jan Arthur - from my example image...why would this happen?

I don't know, I'd be happy to look into it, but if you want people on here to work with your test data you're best off posting a workbook. Nobody wants to have to re-type your data from an image.

**jgrever2** · 10-11-2011, 04:10 PM

Originally Posted by Andrew-R

I don't know, I'd be happy to look into it, but if you want people on here to work with your test data you're best off posting a workbook. Nobody wants to have to re-type your data from an image.

That seems fair, but I have too much personal data in this one...

but the GOOD NEWS is that your solution actually worked perfectly...I just needed to start from cell B1 in the other page of the worksheet...for some reason if I posted the formula in B7 for example, it didn't work out, it would start somewhere in the middle of the list.

But if I start at B1, it works...so I will just design my spreadsheet around it, thank you SO MUCH!

I will try to remember to create a spreadsheet that I can attach next time...thanks again!!!

**jgrever2** · 10-11-2011, 04:12 PM

Originally Posted by NBVC

Try:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down as far as needed.

I don't know how to use the "if error" function...if I had used this, where would I post it?

is this taking the place of my formula, or is it supplemental? if it is supplemental, where would I paste it so that it interacts with my already existent formula/function?

**NBVC** · 10-11-2011, 04:15 PM

That formula would replace yours. IFERROR() is a post Excel2003 function and is used to "error trap" your formulas and return a desired outcome instead of an error.

After entering the formula, you need to confirm it by holding the CTRL and SHIFT keys together and then pressing enter.. then copy it down the column.