How do I pull data using cross-sheet reference and multiple criteria

perryal

Help! I have tried to get pull this data using many different combinations of formulas (using AI and searching through other posts.) Here is what I'm trying to do:

I want an "X" to appear in the appropriate column when pulling from another sheet labeled "Roles"

The Roles sheet has five different columns that I need to check criteria

* Title = Roles_MGR,Finance is a checkbox field (this column changes based on the appropriate column in the table below

* Required1, Required2, Required3, and Required 4 = values that need to match the data in the first column of the table below

Not every cell has data in the required fields.

Here is the formula I started with (but it doesn't work when I extrapolate to include additional fields:

=IF(INDEX(COLLECT({Roles_Required1}, {Roles_MGR,Finance}, 1, {Roles_Required1}, [Primary Column]@row), 1) = [Primary Column]@row, "X", "")

Here is the Roles table

Thanks!

Georgie

Hi @perryal,

Following @hooverisbad's suggestion, I was able to come up with the following formula:

• =IF(IFERROR(IFERROR(IFERROR(IFERROR((INDEX(COLLECT({Required 1}, {Finance checkbox}, @cell = 1, {Required 1}, [Primary Column]@row), 1)), INDEX(COLLECT({Required 2}, {Finance checkbox}, @cell = 1, {Required 2}, [Primary Column]@row), 1)), INDEX(COLLECT({Required 3}, {Finance checkbox}, @cell = 1, {Required 3}, [Primary Column]@row), 1)), INDEX(COLLECT({Required 4}, {Finance checkbox}, @cell = 1, {Required 4}, [Primary Column]@row), 1)), "") = [Primary Column]@row, "X", "")

You’ll need to update the cross sheet references so they refer to the right columns in your sheet. You can then use the same formula but swap the {Finance checkbox} range for the relevant range for each column in your sheet.

The only thing this doesn’t account for is where the course name appears in multiple rows - so the formula will display “X” if there’s any row that has the course name and the relevant checkbox is checked, even if the course name appears in another row with the checkbox unchecked.

I hope that helps, even if it just points you in the right direction!

Georgie

Georgie

Hi @perryal,

You'll need to select the entire column when creating the cross-sheet references. You'll be able to edit your existing references by clicking on them within the formula and selecting Edit Reference - take a look here: View, modify, or delete cross sheet references in formulas.

Once you've edited the references to use the whole columns, let me know if that's working for you!
Georgie

perryal

Hi Georgie, I updated the cross-sheet references and it worked!!!!!

Thank you, thank you, thank you!

Georgie

@perryal,

That's great! So glad it's working for you 😊

Georgie

hooverisbad

Hey there,

If I understand what you're asking for, you want to search the Required Courses (1, 2, 3, and 4) for the values listed in your Primary Column. If you find a match, you want to see if the checkbox is checked for a specific role. If that role is checked you want an X, and if not you want it blank.

If I got your problem statement correct, then you'll have to have a nested IF statement that looks at each column in order. My concern is that it appears that course names appear multiple times in the 'Required Course' columns. Can you confirm whether I got the problem statement correct? If not, can you say a little more about what specifically you're trying to accomplish with your data table referencing the roles?

perryal

Good morning. Yes, you understand my dilemma perfectly. Some courses do appear multiple times. The training needed depends on the security role.

Georgie

Hi @perryal,

Following @hooverisbad's suggestion, I was able to come up with the following formula:

• =IF(IFERROR(IFERROR(IFERROR(IFERROR((INDEX(COLLECT({Required 1}, {Finance checkbox}, @cell = 1, {Required 1}, [Primary Column]@row), 1)), INDEX(COLLECT({Required 2}, {Finance checkbox}, @cell = 1, {Required 2}, [Primary Column]@row), 1)), INDEX(COLLECT({Required 3}, {Finance checkbox}, @cell = 1, {Required 3}, [Primary Column]@row), 1)), INDEX(COLLECT({Required 4}, {Finance checkbox}, @cell = 1, {Required 4}, [Primary Column]@row), 1)), "") = [Primary Column]@row, "X", "")

You’ll need to update the cross sheet references so they refer to the right columns in your sheet. You can then use the same formula but swap the {Finance checkbox} range for the relevant range for each column in your sheet.

The only thing this doesn’t account for is where the course name appears in multiple rows - so the formula will display “X” if there’s any row that has the course name and the relevant checkbox is checked, even if the course name appears in another row with the checkbox unchecked.

I hope that helps, even if it just points you in the right direction!

Georgie

perryal

Good morning, Georgie. I copied and updated as you suggested. I received no errors, but I also didn't get any "X"s.

When creating the cross-sheet reference, do I highlight the whole column, or just a cell or all the cells? I thought I understood this concept, but this task has me questioning my capabilities with more advanced formulas.

=IF(IFERROR(IFERROR(IFERROR(IFERROR((INDEX(COLLECT({Roles_Required1}, {Roles_MGRChecked checkbox}, @cell = 1, {Roles_Required1}, [Primary Column]@row), 1)), INDEX(COLLECT({Roles_Required2}, {Roles_MGRChecked box}, @cell = 1, {Roles_Required2}, [Primary Column]@row), 1)), INDEX(COLLECT({Roles_Required3}, {Roles_MGRChecked checkbox}, @cell = 1, {Roles_Required3}, [Primary Column]@row), 1)), INDEX(COLLECT({Roles_Required4}, {Roles_MGRChecked checkbox}, @cell = 1, {Roles_Required4}, [Primary Column]@row), 1)), "") = [Primary Column]@row, "X", "")

Thanks,

Angela

Georgie

Hi @perryal,

You'll need to select the entire column when creating the cross-sheet references. You'll be able to edit your existing references by clicking on them within the formula and selecting Edit Reference - take a look here: View, modify, or delete cross sheet references in formulas.

Once you've edited the references to use the whole columns, let me know if that's working for you!
Georgie

perryal

Hi Georgie, I updated the cross-sheet references and it worked!!!!!

Thank you, thank you, thank you!

Georgie

@perryal,

That's great! So glad it's working for you 😊

Georgie