Firstly, thank you to anyone who replies to this post for taking the time to help me.
This might be a little long winded but I think to understand the nature of what I'm trying to do with my function you'll need to know a little about what I'm working on.
I play Dungeons and Dragons and I have built a somewhat complicated spreadsheet to help me track my progress in populating locations with characters. I have a demographics calculator (http://www.lucidphoenix.com/dnd/demo/) that I use to determine the professions present in a given settlement as well as the number of people in the settlement broken down by character class. So basically the calculator gives me a list of how many professional people there are in a settlement of a given population, and what the available people are broken down by their skills. For example, a settlement might have 1000 people, and the calculator will tell me how many bakers, doctors, coopers etc. can be found in the settlement. Then it gives me a breakdown of how many of the population are warriors, nobles, wizards etc as a separate number.
So what I am using the spreadsheet for is to track assigning the different characters to the professions. If I need 1 constable, but I have 3 warriors, I will assign one of them to the constable job and have 2 left over.
The professions are my rows, and the characters are my columns.
The rows are organized like this from left to right:
|Profession Name|Total in Settlement|Remainder Available|Empty Cell|Empty Cell|etc...
The columns are organized like this from top to bottom:
|Character Class Name|Total Available|Remaining Available|Empty Cell|etc...
All of the empty cells are for tracking how many of each class are assigned to each profession. What I need is a conditional formatting formula that will use both the [Remainder to be Assigned] cells to assign a color to the cell when they are both above zero. Basically when there is a profession slot and a member of that class available I want the cell to be highlighted so I can easily see where I can assign people.
The function that I have right now that I'm working on looks like this:
=AND(D5>0, E4>0)
D5 is the remaining profession slots to be filled and E4 is the remaining characters of this given class available (on my sheet this is Law Officer for profession and Commoner). My function highlights the cell yellow when I still need to assign more officers and when I have more commoners available to assign.
Now here is my quandary. The function uses relative references for both, which means that if I mass special paste it, the cells in its row will always reference one cell to the left which is wrong, the cells in its column will always reference one cell above which is also wrong and the rest of the cells will reference both the cell immediately above and the cell immediately to the left. So it seems like the rows require the D5 value to be absolute while the columns require the E4 value to be absolute. The values can’t be both absolute and relative at the same time. Is there a way to create a single formula smart enough to be pasted across the entire entry area that will highlight my cells correctly? If there were only a few rows and columns I would go in and do everything manually, but this spreadsheet is very large and that would take ages and make me go insane.
Bookmarks