Forum Discussion
Trying to find a way to find and add text in front of found text.
Yes it's possible.
Easiest to do if the yellow color in the cell (background, I presume) is there as a result of Conditional Formatting. Because if it's there in Cell A1 as a result of Conditional Formatting, that means there's a formula that can be written in B1 using the same conditional (IF) clause that leads to the conditional format.
In effect you'd want to write a formula that says IF (specified condition exists in A1) THEN ("D"&A1) , the ampersand (&) being the short-cut way to say Concatenate these two things.
If what's in A1 is a number, formatted as a number, then you'd have to use the TEXT function to convert it to text before concatenating. That could all be done in the one formula.
Now, I'm assuming you were just asking to be pointed in the right direction.....if you need more complete example, actually in a spreadsheet, by all means come back.
mathetes Also this is what I'm currently working with. I'm trying to build a schedule that shows the number of days a person had off since the last time they pulled a Duty Shift. The days off are being annotated by a number that counts up everyday they have a break from Duty until they pull it again in which case the count resets. The "D" signifies they have another type of duty and are ineligible to work the Duty Shift but, the count still continues. The count is essential to the schedule because that's how I fairly determine who should work the Duty Shift.
If you're asking for help, you can help me by uploading the actual spreadsheet you used to create that image. An image by itself is truly not anywhere near as useful as the actual spreadsheet.
Also, I don't fully follow your description. I'm sure it's clear in your mind, but maybe I'm confused by "D" meaning other Duty, and you're counting Days off since a Duty shift....
So if you could give a couple of examples of a sequence of days with DaysOff noted, Other Duty Days, Duty Shift Days.... the annotated count for the days off.... that would help. Along with the spreadsheet itself in whatever form it currently exists.
mathetes The schedule which is known as a DA6 in my organization is formatted in the following way: I record the number of days each Soldier has had off since they performed Staff Duty, (recorded as a yellow filled in "X"), in numerical order.
Soldiers who are unavailable for Staff Duty will be given the following abbreviations: A, (for leave, pass, special duty, temporary duty, sick-in quarters, any other authorized reason) D, (for duty that prevents the Soldier from assuming duty that day) U, (for Soldier’s absent without leave, in arrest, in confinement, or otherwise unavailable due to their own misconduct).
Whenever the abbreviation “A” is used, the numbering sequence will be interrupted. Whenever the abbreviation “D” or “U” is used the numbering sequence will continue with the appropriate abbreviation included.
I've attached the most recent schedule I used. The schedule I should mention is governed by my organization's regulations and policies so the abbreviations can't be altered neither can the method of counting.
Also, sorry for my late reply I ended up just doing the work manually because, of the due date was imminent.
mathetes Thank you, I'll try this out. I'm an Excel beginner so, if you have any obvious suggestions to perform this task that would be much obliged. Thanks again for your comment.
