Easy Index one for you all

richard_abra

Im indexing from another sheet but only want it to copy cells if i certain word is in it.

for example only fetch the cells that say Richard, james, mark, john.

I cant seem to get it to work other than doing a format that turns those cells white so you cant see them.

below is what im using

=IFERROR(INDEX({Source Sheet Job Info Column}, MATCH(Date@row, {Source Sheet Date Column}, 0)), "")

Find more posts tagged with

Accepted answers

All comments

KPH

The formula looks OK. It could be a problem with the cross references. When you say it doesn't work, what does it do?

richard_abra

https://community.smartsheet.com/discussion/comment/419169#Comment_419169

It works but for the whole colmun. basically i have 3 calendars. 1. Holiday 2, Jobs 3,Factory

I want to be able to fill in the holiday for and for that to then populate the other two. i have it copying the whole column and just turning the unwanted white but its a pain.

Ideally i fill one in then it links to the other two however i still need to be able to edit the Sheets! just need it to look at 'sick''holiday' 'late''unpaid leave'

KPH

I could be completely wrong here, but it sounds like you have a formula in the Jobs sheet that is matching on date and populating the entire column in with information from Holiday and you are doing something manual to turn text white that you don't want to see.

Instead, you want to match on two things. Date and some other column that has something in it to identify if this is information you want to see in the Jobs sheet. If so, you can use an INDEX COLLECT formula rather than INDEX MATCH.

It would look something like this:

=INDEX(COLLECT({Source Sheet Job Info Column}, {Source Sheet Date Column}, Date@row, {another column}, "thing you want to include"), 1)

If your "another column" contains more than one thing that you want to include (such as 'sick','holiday', 'late','unpaid leave'), then you can use an OR like this:

=INDEX(COLLECT({Source Sheet Job Info Column}, {Source Sheet Date Column}, Date@row, {another column}, OR(@cell = "sick", @cell = "holiday", @cell = "late", @cell = "unpaid leave")), 1)

If that is not what you are doing, or if you need more help, could you share a screen shot of your 3 sheets and explain what data is being pulled in that you do not want to see.

richard_abra

https://community.smartsheet.com/discussion/comment/419178#Comment_419178

Thanks for your response, i have tried but cant get the above to work.

So, 3 Sheets

J - Holiday (Source Sheet to feed E and F)

E - Job Scheduele

F - Workshop Install Planner

All have the same calendar layout just different deparments. The idea is to be able to select from a dropbox one of the below in the J Holiday Source sheet and it populates both E and F sheets

Hol

Half Day Hol

Half Day Unpaid

Sick

Half Day Sick

SSP

Unpaid

richard_abra

Forgot to add the Sheets F and E still need to be editable. possible?

KPH

Thanks, is this all true:

You have three sheets: J, E, and F.
You have a column called Date on each sheet.
The Date column is sorted the same way on each sheet and starts and ends with the same date on each sheet.
You also have a column per person, such as Ashley, Chris, Darius, etc., on each sheet.
The person columns are NOT in the same order on each sheet, and not all people appear on each (for example, we can't say the 6th column is always going to be Gary).
The people columns are single-select drop-down columns.
The drop-down options include: Hol, Half Day Hol, Half Day Unpaid, Sick, Half Day Sick, SSP, Unpaid.
There are also other drop-down options.
You want to enter data on sheet J, using the drop down.
If the data entered into sheet J is one of the things in assumption 7 then that should appear on sheets E and F, under the correct persons column. If the data entered into sheet J is not one of the things in assumption 7, then you do not want it to appear in sheet E and F. This is the formula you need.
You want to be able to overtype the data that is entered into sheet E and F via the formula.
The data manually entered in step 11, does not need to update sheet J.

richard_abra

https://community.smartsheet.com/discussion/comment/419250#Comment_419250

Thank you so much for taking the time.

You have three sheets: J, E, and F. CORRECT
You have a column called Date on each sheet. CORRECT
The Date column is sorted the same way on each sheet and starts and ends with the same date on each sheet.CORRECT
You also have a column per person, such as Ashley, Chris, Darius, etc., on each sheet.NO, THE HOLIDAY SHEET HAS EVERYONE ON, BUT SOME PEOPLE ARE NOT ON THE JOB SCHEDULE AND VICE VERSA FOR SHEET F. BASICALLY SOME EMPLOYEES COVER MORE THAN 1 ROLE AND THESE SHEETS SCHEDULE TWO ROLES.
The person columns are NOT in the same order on each sheet, and not all people appear on each (for example, we can't say the 6th column is always going to be Gary).CORRECT NO ORDER, THIS CAN BE CORRECTED THOUGH, WOULDNT MATTER TO US WHAT ORDER THEY ARE IN.
The people columns are single-select drop-down columns.THEY ARE DROP DOWNS IN HOLIDAY SHEET ONLY ATM. THIS COULD CHANGE THO.
The drop-down options include: Hol, Half Day Hol, Half Day Unpaid, Sick, Half Day Sick, SSP, Unpaid.CORRECT BUT ONLY IN THE SOURCE SHEET ( J-HOLIDAY )
There are also other drop-down options.NO E AND F ARE MANUALLY FILLED OUT CELLS.
You want to enter data on sheet J, using the drop down.YES THEN THAT POPULATES THE OTHER TWO SHEETS
If the data entered into sheet J is one of the things in assumption 7 then that should appear on sheets E and F, under the correct persons column. If the data entered into sheet J is not one of the things in assumption 7, then you do not want it to appear in sheet E and F. This is the formula you need. WHICH FORMULA ?
You want to be able to overtype the data that is entered into sheet E and F via the formula.CORRECT
The data manually entered in step 11, does not need to update sheet,, CORRECT THE ONLY DATA IN J IS WHATS IN THE DROP DOWN. NOTHING ELSE SO ONE OF THOSE OPTIONS SELECTED IN J THEN POPULATES E AND F, BUT CAN BE OVERRIDDEN IN E AND F, WITHOUT IT UPDATEING J.

Did this answer the question? Yes No

KPH

My only confusion is around item 8 (and to a lesser extent, 10).

If there is nothing else in the drop-down on sheet J. What is it that you are changing to white text so it doesn't appear in E and F? I thought we were looking for a formula to only pull certain things from sheet J and leave everything else blank.

I was about to say...

The formula you have will work to pull in the data you want but will pull any value in the column (so assumption 10 is not fulfilled and you are changing the things you don't want to white).

=INDEX({Source Sheet Job Info Column Ashley}, MATCH(Date@row, {Source Sheet Date Column}, 0))

You can add an IF to the start of this formula to only return certain values, like this:

=IFERROR(IF(OR(INDEX({Source Sheet Job Info Column Ashley}, MATCH(Date@row, {Source Sheet Date Column}, 0)) = "Hol", INDEX({Source Sheet Job Info Column Ashley}, MATCH(Date@row, {Source Sheet Date Column}, 0)) = "Half Day Unpaid", INDEX({Source Sheet Job Info Column Ashley}, MATCH(Date@row, {Source Sheet Date Column}, 0)) = "SSP", INDEX({Source Sheet Job Info Column Ashley}, MATCH(Date@row, {Source Sheet Date Column}, 0)) = "Unpaid", INDEX({Source Sheet Job Info Column Ashley}, MATCH(Date@row, {Source Sheet Date Column}, 0)) = "Half Day Hol", INDEX({Source Sheet Job Info Column Ashley}, MATCH(Date@row, {Source Sheet Date Column}, 0)) = "Half Day Sick", INDEX({Source Sheet Job Info Column Ashley}, MATCH(Date@row, {Source Sheet Date Column}, 0)) = "Sick"), INDEX({Source Sheet Job Info Column Ashley}, MATCH(Date@row, {Source Sheet Date Column}, 0)), ""), "")

As long as this is not a column formula, but is dragged down each column, you will be able to overtype it.

However, this is somewhat messy and will be hard to update should your list change.

And you would need to set up and reference a new column for every person.

I was going to ask if you would you be open to rearranging your data? But now I am not sure this is what you want anyway.

richard_abra

https://community.smartsheet.com/discussion/comment/419254#Comment_419254

rearraning is fine. they should probably be in the same order anyway!! makes life easier

KPH

I'm actually thinking of something more than just moving the columns about. More creating another feeder sheet that populates J, E and F. The fact that sheets E and F don't contain the full list of people means just sorting the columns is not going to solve the problem.

But can you explain exactly what your current issue is? It isn't that the formula is annoying to copy into each column. I thought you needed helping making it take things from sheet J into E and F onto the correct date row, and only displaying a subset. But now you say there is no subset, so I am not sure why the simple INDEX MATCH is returning things you have to change to white text?

richard_abra

https://community.smartsheet.com/discussion/comment/419256#Comment_419256

Sorry im confusing you ! To be honest i cannot get a simple index to work in this situation. im only assuming because im trying to get it to work with two sheets. (3 in total) while i am new to smartshart im indexing successfully elsewhere in thre workspsace. Could you suggest what else i might be dong to cause this not to work? i do think that while this community is invaulable sometimes, us newbies need abit more to help make the forumlas to work. (column names and types, refferances etc) against that i am enjoying the challenge !

richard_abra

https://community.smartsheet.com/discussion/comment/419269#Comment_419269

=INDEX({Source Sheet Job Info Column Ashley}, MATCH(Date@row, {Source Sheet Date Column}, 0))

This Works, however it wipes the whole column out to directly copy the source sheet. i only want it todo that when it says ssp, sick etc

KPH

Hi Richard

You have two options to limit what the INDEX brings across.

You can use the INDEX COLLECT method to only match on multiple things (for example one date and a selection of time off reasons) and populate the column with those.
You can use the IF outside of an INDEX MATCH to only return certain matches and not others.

Neither are what you want.

My confusion is where you say you only want to bring in certain values but then that there are no other values, so I don't understand what you are bringing in that you are changing to white text. I do want to get you going and have been wracking my brain to try to understand what you are trying to do.

Now, you say "it wipes the whole column" I am wondering if the problem is not that the formula is bringing in more than you want, it is that you already have text in the column that you are placing the formula in and the formula is deleting what was there. Is that the issue?

richard_abra

https://community.smartsheet.com/discussion/comment/419349#Comment_419349

Does this make it clearer?

KPH

Hi Richard

I understand how the sheets are working together and I think I understand what you want. I don't understand what the INDEX COLLECT or INDEX MATCH formulas are doing that you do not want. I read through some of your other discussions to see if they added any background but I'm at a loss.

What happens when you use the formula?

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.