Easy Index one for you all

OK. There are two things here that you cannot change.

1. If the formula is a column formula you cannot overtype it.
2. If you enter a formula into a cell that already contains data, the data in the cell will be deleted and replaced by the formula.

But there are workarounds

1. You can drag the formula down instead of converting it to a column formula. That way, you can overtype it.
2. You can leave your original data as it is, add a new column and place a formula in the new column to merge the data from the column you have typed into and the column from the other sheet.

In more detail....

A formula should be placed into a new column in sheet E (repeat for sheet F later) and will bring the text from sheet J into sheet E if there is text in sheet J (we are no longer checking for Hol, Half Day Hol, Half Day Unpaid, Sick, Half Day Sick, Unpaid, and SSP because you say there is nothing else there)

If there is not text in sheet J, it will take the text from the column that was already in sheet E in a column I am calling Manually entered data (change this to your column heading).

So if this is sheet J

Sheet E would look like this. One column with the typed data in and one with it merged with the data from J.

The formula would be

=IFERROR(IF(ISBLANK(INDEX({Source Sheet Job Info Column Ashley}, MATCH(Date@row, {Source Sheet Date Column}, 0))), [Manually entered data]@row, INDEX({Source Sheet Job Info Column Ashley}, MATCH(Date@row, {Source Sheet Date Column}, 0))), [Manually entered data]@row)

This says if the result of the index match is blank, then use the text in the Manually entered data column, if not use the result of the index match. If there is no match, also use the text in the Manually entered data column.

You will need to set up one of these formulas with cross sheet references for every column, which I am not a fan of but I think it will do what you want. If you want to make the process simpler, I would suggest having a simpler sheet J that just has 3 columns - date, reason, and name - with multiple rows per date, rather the one column per name. It would be easier to do a look up on that that than the huge grid, but that is for the future.

Hopefully, this answers your current question.

The cross sheet reference is unique to the column and the sheet. The one I set up at the weekend to get the formula started was for the Ashley column. So in sheet E and sheet F you would need to find the Ashley column, set up the reference and then use the Ashley cross sheet reference in the formula. Then for Chris, you would need to set up a reference to his column and use that in your formulas. Then for Darius, you would need to set up a reference to his column, and use that in your formulas. I do not recommend that approach but it is the only option with the way your data is currently organized.

My suggestion would be to collect the data in a sheet that has just one column with all the names in and then use this within an INDEX.

Instead of setting up J like this, with a column per person:

You do this:

You might not find it as easy to view as you will have the same date repeated on multiple rows, but you can easily create a sheet that looks like your original, from there. However, you can't (easily) do the reverse.

If you have your data set up like this, you can use an INDEX COLLECT to look up the name and type from the same columns every time. Three references rather than date plus 1 per person.

This is an example of the formula (without the extra step to merge in the other data if that has already been entered).

=IFERROR(INDEX(COLLECT({J Type}, {J Person}, "Ashley", {J Date}, Date@row), 1), "")

It brings the data from the Type column where the Person matches "Ashley" and the Date matches the date in the current date row. Like this:

Then if you want to do this for Chris, rather than setting up a reference to his column and changing the formula, you just change the name in the formula.

And, if you were willing, you could simplify it further, by having the names within the sheet, not just as column headings. If you do that, you can reference the names within the formula just like the date, and don't even need to change the formula.

If you put this in the Darius column

=IFERROR(INDEX(COLLECT({J Type}, {J Person}, Darius$1, {J Date}, $Date@row), 1), "")

When you drag this formula across and down the Date column is fixed and the row 1 is fixed but not the column name, so this formula can be dragged into any of these cells (the Darius heading changing to whatever column the formula is in). No formula to edit.

I think you would save yourself a lot of time if you set up a sheet like this to feed into the other sheets.