Using @row with references to other sheets

Lynn Meadow

I have one sheet that contains a number of calculations and I am trying to use a cell reference in another sheet if a condition occurs. I have the formula correct when all the data is in one sheet, but when i try to pull from another sheet, i am missing something.

When in the same sheet, i am using the following formula to basically say that if it is a value of Yes in the surgeon field, then display a value that appears in teh Naive Cognitive Composite Score field:

=IF([Surgeon/ FA]@row = "y", [Naive Cognitive Composite Score]@row)

I am trying to separate this information into multiple sheets because we have various roles and it is confusing to have surgeon information in the same sheet as other roles.

So in the new sheet, i am trying to reference the same columns, but the @row seems to be mixing it up since that value is not in my destination sheet.

This formula: =IF({Naive Surgeon} = "Y", {Naive Cog Score}) returns an INVALID OPERATION

This formula: =IF({Naive Surgeon}@row = "Y", {Naive Cog Score}@row) returns UNPARSEABLE

Any assistance would be most appreciated!

Kelly Moore

Hey @Lynn Meadow

This formula would be in your destination sheet, [Naive Surgeon Cognitive Composite] column

=IF([Primary Task]@row<>"", IF(COUNTIFS({Source sheet Surgeon/FA}, UPPER(@cell)="Y", {Source Sheet Unique Key}, [Unique Key]@row)>0, INDEX({Source sheet Naive Cognitive Composite Score}, MATCH([Unique Key]@row, {Source Sheet Unique Key}, 0))

Each of my cross sheet references are a single column in the source sheet. Note how I named them - a good practice is to change the name of the generic smartsheet range to reflect the sheet/column that you are referencing.

Let me know if this works for you.

Kelly

Kelly Moore

The first IF is checking to see that the [Primary Task] cell in your destination sheet is not blank. <> means Not. In my formulas you'll often see me using <>"" or ="" rather than ISBLANK or NOT(ISBLANK). It's just a preference thing.

Since we can't directly query the referenced source sheet with an IF to see if it is blank, we use the COUNTIFS function to see if anything is found. Any value greater than zero means the cell, which meets the countifs criteria, is not blank.

Rather than using VLOOKUP which, as you mentioned you're familiar with, uses a structured table approach to lookup values, INDEX/MATCH does this by looking at columns. This means you can look right or left in row, which makes the function more robust- particularly if you sheet is shared and someone with the right sheet permissions might inadvertently rearrange columns. On large sheets you may also see better sheet performance since the sheet is collecting a smaller, more targeted data set.

I hope that helps. If I didn't explain it well, ask me again and I'll try again.

cheers!

Kelly

Kelly Moore

Hey @Lynn Meadow

When referencing another sheet (called a cross sheet reference in smartsheet terms) the syntax is different and a direct IF statement cannot be used. We work around not being able to directly query using IF by using other functions instead. Cross sheet references, as you learned, do not use @row references.

If you could share a screenshot or mock-up table of the source sheet (the sheet you're cross referencing) as well as your destination sheet (the sheet you want to pull the data into), I can try to help you with your formula

cheers,

Kelly

Lynn Meadow

Thank you so much for your response and for helping to look at this. I've attached two screen shots. In the Surgeon sheet, i am using a VLOOKUP to pull the data from the first three columns of the master.

Kelly Moore

Hey @Lynn Meadow

This formula would be in your destination sheet, [Naive Surgeon Cognitive Composite] column

=IF([Primary Task]@row<>"", IF(COUNTIFS({Source sheet Surgeon/FA}, UPPER(@cell)="Y", {Source Sheet Unique Key}, [Unique Key]@row)>0, INDEX({Source sheet Naive Cognitive Composite Score}, MATCH([Unique Key]@row, {Source Sheet Unique Key}, 0))

Each of my cross sheet references are a single column in the source sheet. Note how I named them - a good practice is to change the name of the generic smartsheet range to reflect the sheet/column that you are referencing.

Let me know if this works for you.

Kelly

Lynn Meadow

This worked beautifully. Thank you so much for your help. I am going to work to apply this to some other similar columns to ensure i am understanding the application here.

May i ask for a little clarification on a few pieces of the formula to help me better understand?

At the beginning, what do the "<>" indicate?

When you are looking at the Y/N value, what does the UPPER(@cell) refer to?

Is the INDEX just aligning the unique key between the two sheets?

Once again, a huge thank you for taking your time to help me!

Kelly Moore

The first IF is checking to see that the [Primary Task] cell in your destination sheet is not blank. <> means Not. In my formulas you'll often see me using <>"" or ="" rather than ISBLANK or NOT(ISBLANK). It's just a preference thing.

Since we can't directly query the referenced source sheet with an IF to see if it is blank, we use the COUNTIFS function to see if anything is found. Any value greater than zero means the cell, which meets the countifs criteria, is not blank.

Rather than using VLOOKUP which, as you mentioned you're familiar with, uses a structured table approach to lookup values, INDEX/MATCH does this by looking at columns. This means you can look right or left in row, which makes the function more robust- particularly if you sheet is shared and someone with the right sheet permissions might inadvertently rearrange columns. On large sheets you may also see better sheet performance since the sheet is collecting a smaller, more targeted data set.

I hope that helps. If I didn't explain it well, ask me again and I'll try again.

cheers!

Kelly

Lynn Meadow

Thank you so much, that was very valuable. I am still pretty new to some of these formulas, so it takes me a bit!!! Thanks again