Linking Columns

Vance McMillan

Hi all

I know there have been a number of archived discussions around linking an entire column from one smartsheet to another, and that it can't be done. I know that there is a workaround by selecting a column and changing the cell background to white, and then linking the"blank" cells, and this works fine.

I have a sheet with a pulished Form for staff to fill out and register Health and Safety hazards. In this form I have an Auto-Number column that will populate a new value for each new form filled out.

However, if I use the cell background trick above, (ie select 50 cells in a column and change background to white, then link to another sheet), the next entry from the form is added to the sheet BELOW the white background cells!

Any thoughts on how I can get the cells in an Auto-number column to link to a different sheet, especially when the number of entries goes beyond the default 50 rows?

TIA

Vance

Andrée Starå

@Vance McMillan

I've sent you an email. It was a weird email address, so writing here as well to make sure that you see it.

Let me know if you haven't received the email.

Have a fantastic weekend!

Vance McMillan

Hi Andree

Thanks for helping with this - I see that you've corrected the attempt I made to use your suggestion, and it's now working. That's fantastic, thank you so much!

If anyone is curious, here is what Andre suggested:

Because you're already using one Autonumber column for the Ref in the Source sheet, I'd suggest that we add an Autonumber column to the Target sheet (RowID) and start the number from 551 (or the number that makes the most sense). Then we'll remove the cell-links in the Ref column and add something like the below formula.

 

The rows that match will show, and on the other rows, it will say #NO MATCH, or we can change it to something else.

 

=INDEX({Source - Ref}; MATCH(RowID@row; {Source - Ref}; 0))

 

Regards

Vance

Andrée Starå

Hi Vance,

You'd have to use cross-sheet formulas instead.

Either VLOOKUP or a combination of INDEX/MATCH.

https://help.smartsheet.com/function/vlookup

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

Would that work?

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

@Vance McMillan

I've sent you an email. It was a weird email address, so writing here as well to make sure that you see it.

Let me know if you haven't received the email.

Have a fantastic weekend!

Vance McMillan

Hi Andree

Thanks for helping with this - I see that you've corrected the attempt I made to use your suggestion, and it's now working. That's fantastic, thank you so much!

If anyone is curious, here is what Andre suggested:

Because you're already using one Autonumber column for the Ref in the Source sheet, I'd suggest that we add an Autonumber column to the Target sheet (RowID) and start the number from 551 (or the number that makes the most sense). Then we'll remove the cell-links in the Ref column and add something like the below formula.

 

The rows that match will show, and on the other rows, it will say #NO MATCH, or we can change it to something else.

 

=INDEX({Source - Ref}; MATCH(RowID@row; {Source - Ref}; 0))

 

Regards

Vance

Andrée Starå

@Vance McMillan You're more than welcome!

Glad we got it working!