I am attempting to utilize conditional formatting to handle a lot of the legwork on a project I'm working on, which relates to the ordering of skills for a roleplaying game. Unfortunately, after multiple attempts I don't think I'm getting anywhere.
Specifically, I would like all my text to read as green unless one of the following criteria is met:
- The text should be red if the skill is restricted by Journiate rank
- The text should be red if the skill's pre-requisite is restricted by Journiate rank
- The text should be purple if the skill is restricted by the Mastery rank
- The text should be purple if the skill's pre-requisite is restricted by the Mastery rank
I've developed data labels for pretty much every set of data there is on the skill lists, but I've still been unable to figure out what formula to input to have conditional formatting output even one of my desired colors.
Edit 1: I wasn't sure what the best way was to present this so I've attached both a marked up jpeg for reference.
I've simply provided a section of one of the skill sets: spells that can be cast by the Alchemist class.
The majority of these spells can be cast right out the gate. Either they have no pre-requisites or those pre-requisites are not rank restricted. These I would like to conditionally format themselves to green text.
A handful of those spells, however, are restricted by either the Journiate or Master rank.
The list of Journiate restricted skills lists Beguile Arcanea, which is the pre-req for Control Arcanea. The latter is not shown on the list because it's rank restriction is implied by needing to pass through Beguile Arcanea. I would like the formatting to check against the list of Journiate skills and if it comes back positive, then show the skill in red. Furthermore, I would like the the command to check the associated pre-req column to check if there's a pre-req skill that is rank restricted to Journiate, and if so, indicate that the skill should be red.
Lastly, there is a list of skills restricted to Master rank. This would follow the same routine as the Journiate skills, but override the Journiate color of red and replace it with purple.
I'm using a series of data tables to associate the information with each other. I think that this needs some sort of multi-step If-Then function with VLOOKUPs built into it, but after multiple iterations, I've been unable to figure out how to craft this function.
