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

Kate Allison · 05/11/22

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!!

ericncarr · 05/11/22

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)

ericncarr · 05/11/22

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 · 05/12/22

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

ericncarr · 05/12/22

You're welcome!

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

Best Answer

Answers

Help Article Resources

Categories