Jan 28 2020 02:51 PM
Jan 28 2020 02:51 PM
Lawd - this one has consumed way too much of my time, and somewhere in my brain, I'm convinced this isn't as difficult as I'm making it. However, I feel like I've tried every combination, and pieces of the final answer have appeared, but alas, never together.
Here's the setup and issue:
I have an attendance sheet that will be filled in by facilitators for a 25-day class. Columns A/B are Last/First, and C:AA are the actual days of the class. Cells in C:AA are data validated to only be the codes we recognize (P, A, T, F, R, Pf, X). The Columns are fixed, but the rows aren't - they will flex based on class size.
All I want to happen is if a facilitator gets to day 4 (column F), for example, and enters code "x", then the rest of the row (columns G:AA) automatically populate the "x". This isn't the case for the other letters - they will be input manually day-by-day, but when the facilitator enters "X", I want the rest of the days in the row to be filled in automatically.
The problem is that I can't put the formula into each cell because each cell is otherwise being used for the other codes, if the student isn't "x"'d. Moreover, the codes to the left-columns, must remain...so, if the student was P,P,P,R,X, then I would want the final record to be:
P,P,P,R,X,X,X,X,X,X,X,X,X,X,X,X,X,X,X,X,X,X,X,X,X. Thus, it's important that the formula NOT be in the range, that the formula finds the X, and only populates cells to the right with subsequent X's until day 25
I think a good part of the problem is that the cells that we would be populating will be blank prior to the completion task...meaning, if the solution herein involves dual conditions (1-From Left to Right, If there is an "X" on a row and 2-if there is a blank cell in that same row, then insert "X" into blank cells on that same row) - but, that may not matter.
I've gotten the array to produce False/True results in OTHER Cells but, not in the target cells. In the supplied file example (Xtestfile.xlsx), if it were a snapshot in time, I would want rows 3,4,5 to now automatically have "x" in columns S-AA, keeping columns A-R as-is, and leaving row 2 and 6 as-is, save for the possibility that they may X on a subsequent day.
I've played with all of the following formulas to some extent, with no results...but, the answer is somewhere in there....maybe?
Index, Match, CountIf, Search, Lookup, Isnumber, isblank, substitute, replace, countif, OFFSET, countA, Countblank, And, or, If, XOR...and probably others!
I hope you can help! Thank you, in advance!
Jan 28 2020 07:18 PM - edited Jan 28 2020 07:20 PM
You needed to allow for a blank (space) in your Data Validation choices. Then a simple IF formula fills the x's all the way across, but leaves it blank and allows the other allowed entries if that's what's called for.
By the way, although your "list" for Data Validation is short enough to fit in the space allotted in the dialog box entirely, are you aware that you could also create a list elsewhere in your workbook, let's say in a sheet called DataVal, cells A1:A8 (or whatever), and then in the field in the dialog box just enter the address of that list. You can also give the A1:A8 range of cells a name ("AttendCodes" perhaps) and then just refer to the name =AttendCodes
Doing that gives you more flexibility in maintaining the list. Just be sure that one of them contains a space.