Formula help - reference data from 2nd sheet, with conditions

Hi,

I’m looking for help with a formula for my sheet. Images of my sheets are below

Goal: I want to auto-populate the Holiday column in Sheet 1 with data from Sheet 2, based on certain conditions.

Populate Sheet 1 Holiday column with data from Sheet 2 Holiday column, only if:
Sheet 1 Date matches Sheet 2 Date, AND
Sheet 2 has an X in it
If Sheet 2 does NOT have an X, then the Sheet 1 Holiday column would stay blank.

Sheet 1 – This sheet has nested rows for each brand, so I think I’ll have to use a slightly different formula for each brand’s group of child rows.

Sheet 2

Thank you for any help!!

Find more posts tagged with

Sheets

Formulas

Marketing

Cell linking

Accepted answers

ericncarr

I'd recommend a few things:

Both sheets - standardize your Dates - make both Date columns of the Date type
If Sheet 1 needs the weekday, you can create a new column with =Weekday([Date]@row) to get the number of the day of the week (4 for Wednesday for example) and then use a separate table to convert it to the weekday (I would think most people have a number to weekday conversion table they use for that..)
Sheet 2 - Change your x's to checkboxes or 1's
Sheet 2- Create a column in Sheet 2 that's something along the lines of "Is checked" or whatever that means for your context.

Then on Sheet 2 you could do an if statement in the new "Is checked" column to check if any of the 4 columns for a given row have an x or not.

=IF(OR([RV]@row = 1, [SP]@row = 1, [AP]@row = 1, [TH]@row = 1), 1, 0)

Then your column formula on sheet 1 would be much simpler:

=index(collect({Sheet 2 Holiday Column Reference Range}, {Sheet 2 Date Column Reference Range}, =[Date]@row, {Sheet 2 Is Checked Reference Range}, =1), 1)

All comments

ericncarr

I'd recommend a few things:

Both sheets - standardize your Dates - make both Date columns of the Date type
If Sheet 1 needs the weekday, you can create a new column with =Weekday([Date]@row) to get the number of the day of the week (4 for Wednesday for example) and then use a separate table to convert it to the weekday (I would think most people have a number to weekday conversion table they use for that..)
Sheet 2 - Change your x's to checkboxes or 1's
Sheet 2- Create a column in Sheet 2 that's something along the lines of "Is checked" or whatever that means for your context.

Then on Sheet 2 you could do an if statement in the new "Is checked" column to check if any of the 4 columns for a given row have an x or not.

=IF(OR([RV]@row = 1, [SP]@row = 1, [AP]@row = 1, [TH]@row = 1), 1, 0)

Then your column formula on sheet 1 would be much simpler:

=index(collect({Sheet 2 Holiday Column Reference Range}, {Sheet 2 Date Column Reference Range}, =[Date]@row, {Sheet 2 Is Checked Reference Range}, =1), 1)

Kate Allison

@ericncarr Thank you! I think this will do exactly what I need.

ericncarr

You're welcome!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.