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 autopopulate 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!!
Best Answer

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)
Answers

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 Thank you! I think this will do exactly what I need.

You're welcome!
Help Article Resources
Categories
Check out the Formula Handbook template!