Need a formula that can search whether a previous cell has specific text, and then...

I am trying to create a list of possible perks depending on cards I obtain in a game (working on a spreadsheet for the game both to have it and to learn more of Excel). I have 17 slots on the table and 32 possible perks. It is very unlikely I will ever have 17 perks in-game so that should suffice. The list must draw from previously made cells which determine whether or not I have the card, and if found true, must post the perk on my summary worksheet table.

In order to work, the formula must do two things. First, it must search whether or not I have the card. Second, it must search whether or not the cells above on the list already contain the perk and only post it in cell if false.

I have tried many combinations of =IF, =CELL, =ISNUMBER, =SEARCH, and others I can't even remember now. I either end up making formulas too long, or that fail in one area or another (i.e. will only post perk if the perk immediately before it on the list is present).

Perk	Have	QState	Cancel	Unnamed attempt
Tracey	=IF('Space Bazaar'!C5="Y","Kill Tracey First","Nothing")	=IF([@Have]="Nothing","N","Y")	Tracey	=IF('Calc Sheet'!R4="Y",IF(ISNUMBER(SEARCH('Calc Sheet'!S4,Summary!F3)),"Nothing",'Calc Sheet'!Q4))

This is my Calculation sheet which I have been building in an attempt to solve this problem. Each column is as follows:

Perk- Label for organization
Have- States what the text should read on my Summary sheet if I have the card
QState- A reverse formula determining whether I have the card
Cancel- Was used as part of my =IF(ISNUMBER(SEARCH( formula attempt, the idea is to cancel the post if said text was found in above cell on Summary sheet
Unnamed attempt- A possibly useless column used in my attempts using this formula: =IF('Calc Sheet'!R4="Y",IF(ISNUMBER(SEARCH('Calc Sheet'!S4,Summary!F3)),"Nothing",'Calc Sheet'!Q4))

This is a sample of a 32 row long list of all possible perks. The Summary sheet is simply a blank (though littered with failed attempts) table waiting for the correct formula.

*Separate but intertwined question: Why is the =IF formula able to read a single cell as text but as range it reads as numerical? (i.e. R3 is text, but R3:R33 is read as numerical, even though all cells contain text).

Thank you for trying. It is all too easy to forget how little someone else knows of something when I am so deeply engrossed in it.

For any that would take up this challenge, here is the idea. My wife and I have a board game called "Firefly", made after the show by the same name that has a cult following and IMHO was sorely robbed of its chance at greatness. The game involves moving pieces around a board and taking on jobs and such, and there are six planets taken from the show and the movie "Serenity" that was made to tie up loose ends in storyline. Each of these six worlds have a corresponding deck of purchasable crew, ship upgrades, and gear. My workbook is being designed to track which cards I have purchased and create a summarized list of all stats and perks obtained from said cards. Must of these stats have been handled, but this "perks" list has proven formidable.

The sample workbook contains just enough information to solve the perk problem, but the source pages are copied directly from my main workbook and so contain all the information and formulas I have thus far created. This can all be confusing, so I have placed specific cells on those sheets in bold borders, and these cells are activated by placing "Y" in them, signifying I have obtained the card. Doing so triggers formulas already in place on the "Calc" sheet. These formulas are discribed in the sample workbook on the "Calc" sheet to the right of the active table. The purpose of these formulas are to condense the information on the source sheets into usable data, and also to specify exactly what the "Perk" list should read on the "Summary" sheet when the card is obtained.

This is the issue. The "Perk" list on the "Summary" page needs to list these perks as I obtain the cards, starting at the top slot (cell), and progressing downwards. There cannot be any copies of perks (i.e. the first, second, and third cells all read "XYZ"). So far, I have managed to make this happen once, but only by stringing the perks such that I would have to get the cards in a specific order for any perks to show up, which is many times unlikely.

The "Calc" sheet was made to make things easier, but by no means are you required to use the information there, with one exception. The "Have" column contains the exact text to be copied onto the "Summary" sheet, and so either those cells or the text contained within the formulas must be used. I want something that works, and obviously I'm not doing something right, so feel free to change anything except the source pages (these are designed to account for every card, not to crunch all data from them).

One additional thing: While the sample workbook contains six perks, the actual workbook contains 32, and so any formula must be able to either accommodate all 32 at once, or else through a series of formulae.

Any and all help would be greatly appreciated.

I am also not really sure if I understand what you want, but lets see if this is heading in the right direction...
C3=IFERROR(INDEX('Source 1'!C:C,MATCH(Table4[@Perk],'Source 1'!B:B,0)),"Nothing")

I have another question pertaining to this. I thought I had this solved much earlier, but found that the =IF formula can see a single cell as text (i.e. C3 contains "Stop Here"), but a range it seems to interpret only as numerical (C3:C32). Why is this? It could see the text when I focused it on a cell, but when I input the range it returned "#Value". That completely derailed me.

Does it have something to do with conjunctive and disjunctive logic? If so, the formula would have to emply "or" logic rather than "and".

The INDEX/MATCH can take another argument to pick a column...
=IFERROR(INDEX('Source 1'!C:T,MATCH(Table4[@Perk],'Source 1'!B:B,0),MATCH(Table4[[#Headers],[Have]],'Source 1'!$B$2:$T$2,0)),"Nothing")

Essentially, this is...
=INDEX('Source 1'!C:T,MATCH(Table4[@Perk],'Source 1'!B:B,0),MATCH(Table4[[#Headers],[Have]],'Source 1'!$B$2:$T$2,0))
so this is doing this.....
=INDEX(range,row-num,column num)
the 2 MATCH's ID the row number and the column number
The IFERROR() catches errors (not founds) and returns the text you give it

Originally Posted by Diaxus

I have another question pertaining to this. I thought I had this solved much earlier, but found that the =IF formula can see a single cell as text (i.e. C3 contains "Stop Here"), but a range it seems to interpret only as numerical (C3:C32). Why is this? It could see the text when I focused it on a cell, but when I input the range it returned "#Value". That completely derailed me.

Does it have something to do with conjunctive and disjunctive logic? If so, the formula would have to emply "or" logic rather than "and".

You normally dont use a range with that kind of IF statement. Can you point me to where you have this?