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

Best Answer

  • ericncarr
    ericncarr
    Answer ✓

    I'd recommend a few things:

    1. Both sheets - standardize your Dates - make both Date columns of the Date type
    2. 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..)
    3. Sheet 2 - Change your x's to checkboxes or 1's
    4. 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

  • ericncarr
    ericncarr
    Answer ✓

    I'd recommend a few things:

    1. Both sheets - standardize your Dates - make both Date columns of the Date type
    2. 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..)
    3. Sheet 2 - Change your x's to checkboxes or 1's
    4. 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