formula not finding data

**Rkreutz** · 12-14-2009, 10:46 PM

{=AND('Super Powers'!=data!CL31,OR('Super Powers'!C13="YES",'Super Powers'!C13="Always On"))}

Right now, this formula is on my "data" page. What I want to the formula to do is check my "Super Powers" page to see if anywhere the data in the blue cell exists, then to check it vs. the other two items. The information in the blue will be static and unchanging. If I have the formula looking in a single static place, such as "Super Powers C6", it works perfectly. But the data on "Super Powers" is supposed to be in a variable location if it shows up at all. I may have this on C6, or it may be on C16 or anywhere. That's why I need it to check the whole page. I have it set for an array search now but that is not working.

Also, I need this formula repeated about 75 times and I don't want to have to go in and re-type a unique phrase that many times. So i want the formula to look at cell CL31 and then check another page to see if whatever is in CL31 exists. Then check it against the other items and return T or F.

As a secondary thing, if my formula returns a "TRUE" result, I need another formula to locate that certain phrase, then read a cell that is in the same row and page (i.e. "Super Powers"), but is 19 columns to the left. This formula will also be on the data page.

Thanks for any help you can give!

**davesexcel** · 12-14-2009, 11:11 PM

It would be best to supply a sample workbook for somebody to work with.

**Rkreutz** · 12-14-2009, 11:34 PM

Sure. The section I'm working on in "Data CL25" and the formula refers to the page "Super Powers". If you have any questions about what I'm aiming for just ask. The sheet should be attached.

Rudy

EDIT: I've mostly solved the first question. I have to use a combination of formulas. =OR('Super Powers'!A5:S22=data!CL31) as well as =AND(OR('Super Powers'!C13="YES",'Super Powers'!C13="Always On")). Now does anyone know how to combine these into a single formula? Also, now that I have established that my data exists, I need another formula that will locate where the data resides, then pull the contents of a cell that is n columns to the right of said data in the same row. I was thinking something along =IF(whatever_cell=TRUE,OFFSET("xxx",0,19,0,0),0) It is the reference that I don't know how to work. How can I get it to find data that exists somewhere then count 19 cells over and pull that number?