Easy Index one for you all

richard_abra

https://community.smartsheet.com/discussion/comment/419425#Comment_419425

Do you already have text in the column that you are placing the formula in (in sheet E & F) and the formula is deleting what was there even if there is nothing in sheet J. Is that the issue?

YES and i cant edit that sheet either. the formula looks the cells it seems

KPH

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.

richard_abra

https://community.smartsheet.com/discussion/comment/419429#Comment_419429

Thanks again, sounds like this would work if i can get it too! When you say sheet reference all the columns in what sheet and literaly all of them one by one? is three any formula going in the holiday sheet? Atm its just copying the data from manually entered column to the old one. i must be referencing wrong

KPH

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.

richard_abra

https://community.smartsheet.com/discussion/comment/419756#Comment_419756

I dont Fully understand but im going todo as you suggest. Hopefully when i get into making the sheets it will make more sense. I will let you know how i get on! thanks again so much !

richard_abra

Ok so, i created a sheet like you said, ( its the same as the old one? )

right click manage reference and reference the colmns on each scheduler? do i put the formlula in those sheets too?

So whats next now i have this with your formula accross all rows and columns?

KPH

The first step is to set up your data collection sheet, like this:

You do this:

If you do that, in the sheet you have just created you can use a formula to pull data from this data collection sheet. Because all the data is now in only 3 columns you only need 3 references instead of a new reference for every person.

In other words

Part1 is add the data in a format that is more accessible (use the format with one column for all the people, not one column per person).

Part 2 is create a sheet that looks like the one you had before and populate that with the data in part 1 using the new formula which re-uses the same cross sheet references for each column.

Sheet J, E, and F can all be populated from your data collection sheet.

I think you have skipped the first step and misunderstood the second step.

There are some video tutorials here that might help:

https://help.smartsheet.com/learning-track/level-1-get-started

https://help.smartsheet.com/learning-track/level-2-intermediate-users

richard_abra

argghhhh i understand now. Ok. im on it! thanks again!