Google Sheet / Microsoft Excel

Question

Seeking out on your help expertise as I'm checking if there's a way or a function or formula so that the dates under Column C will reflect will reflect on each cells in Column S.

Here's the file: https://docs.google.com/spreadsheets/d/1Xd6AH_Fwp78G8TBuhji-XCjuZWSrV0VAB-QDySPebts/edit#gid=0

For example:

Agent#1 that's highlighted in red, his shift is 3/6/2022. I wanted his dates to reflect in each cells in Column S matching his name.

For Agent#2, the schedule shown is 3/7/2022 and so on and so forth.

actual file

If possible to help me get the times under Col D and E as well to have those reflected on each cell on Col S and T respectively, that would be very helpful!

Thank you so much in advance! 🙏🙇‍♂️

Answer 1

So each column C date is exactly 4 rows down from the start of a new agents "entry". So if you can find a formula to find this "first row" of each agent name (B), you can use INDEX or OFFSET to jump 4 rows down and one cell right to the date (C).

To get to the name cell you can use XMATCH to look upwards in reverse (search from last) for the first non-blank cell. Here you might have to play around with the match mode ("exact match" or "next smallest/largest" or wildcard) to get to name and ignore the blanks. XMATCH will then give you the row index to start off with. INDEX($B$1:$C$10000, XMATCH(...)+4, 2) will then index to the date cell, and can be adjusted for the time cells too.

Alternatively, you can add a helper column to keep track of the row offset to the name cell e.g.

=IF(ISBLANK(B2), B1+1, 0)

Now use OFFSET to jump to the date cell e.g.

=OFFSET(B2, -*helper value*+4,1)

Or preferably use non-volatile INDEX

=INDEX($B$1:$C$10000, ROW()-*helper value*+4,2)

Answer 2

You might want to play around with the IFS function.

In the first link try

IFS(R2="Abanto, Jerome", $C$6, R2="Acero Campos, July Stephany", $C$17, etc etc

and see if it works. R2 should point to the name cell. The $ sign locks the date cell so it doesn't move around.

Answer 3

You have all the names sussed out already and in column R. Building on that, the following does the trick:

=OFFSET($C$1,  XMATCH(R13,  RIGHT($B$1:$B$39,  LEN($B$1:$B$39)-FIND(" ",$B$1:$B$39,FIND(": ",$B$1:$B$39)+2)),  0)+4-1,  0)

The nested FIND()' locate the : after "Agent" in the column B data and use that (+2) as a starting point for locating the space before the Agent's name. Subtracting that value from the full lengths of the column B data gives the length of each Agent's name in the string. It is the rightmost data in the string, so using RIGHT() extracts it.

That gives you a list of column B data pared down to just the name portions, which will match the names in column R. Which allows XMATCH() to find them in that virtual data set (internal to the function when calculating, not existing elsewhere). This returns their up/down (in this case) position in the virtual data set. Since it starts with row 1, not the more "natural" row 2, that value is also the row number the name appears in.

So now it has the row number, and that is "absolute" not relative to some row. You know the dates are in column C, so the column number needed for OFFSET() (or INDIRECT/ADDRESS) is known for all cases. The date needed is known to be four rows below the Agent's name row so the offset from the name row is 4.

The formula then uses OFFSET() giving it cell C1 for its starting point. This works then with that fact that the "absolute" row number is being found, not one relative to some other row. Cell C1 works for all rows in column S because of that. For the row offset, you give it the XMATCH() result plus the four rows from there to the date's row minus one for the fact that the starting point is in row 1. Can't make it row 0, so it's necessary.) The column offset is easy being the next column over.

If using this method more broadly, and the distance between columns could change up or down, using COLUMN(S:S) - COLUMN(B:B) would keep it accurate.

So, the only other thing is propagating the formula down the column. Unfortunately, I could not get either OFFSET() or INDIRECT/ADDRESS to take a dynamic range (errored out). So I made the range addresses and C1 absolute, then copied down the column. Works, just isn't elegant in these modern times. "Success" trumps "elegant" in my book though so...

It was INDIRECT() that refused the dynamic range idea (and OFFSET()) as neither seemed willing to take the multiple inputs (ranges rather than cells) needed for a dynamic range. ADDRESS() was perfectly happy to take it. I believe this is due to how the text of the internal input strings is formed. INDIRECT(), for instance, would take a single cell range like B2:B2 but not a multiple cell range. The single cell ranges resolve to a single value in the string while the multiple cell ranges have multiple values and that's where I believe the sticking point is. It's willing to evaluate them, just it gets them way wrong, #VALUE!-wrong, so...

Sometimes though, wrapping such a thing in another function (or two if the one that lifts it from the error stage doesn't actually put out the info the way it is needed) overcomes that kind of error and that might be so here as well. If so, then probably for OFFSET() too.

But the "... and copy down... " approach works just fine.