Join Formula from Reference Sheet

Donna B.

I am trying to use Join Formula to concatenate 2 adjacent cells from different columns of another sheet. It returns the value correctly actually, however, as I am trying to drag the formula down, it doesn't work anymore unless I specifically edit again the formula in each cell which is impractical as I have more than a thousand entries to join.

I tried adding $ everywhere but did not work too😆

Kelly Moore

Hey @Donna B.

Try using the Formula column option - it will automatically fill down your sheet. Right click in a cell that already has the formula you want. At the bottom of the cell menu is the option to Convert to Formula column. Note: After converting to Formula column you edit the formula by going back into the cell menu and Edit the Formula column. You will not be able to edit the formula directly without first accessing the cell menu.

Does that work for you?

Kelly

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

Donna B.

Thanks @Kelly Moore but sadly did not work

Or might be I have missed a crucial step? after converting to column formula, any suggestion on what changes I should make on the formula?

Kelly Moore

Hey

What isn't working? Did your formula not work to begin with, or is it not filling down?

Donna B.

the formula is working but not filling down.

Kelly Moore

Did you try to refresh your page? Save? The column formula automatically fills down to any created row.

Donna B.

@Kelly Moore , I already did - save, refresh, exit, edit reference sheet...but still it did not fill automatically.😥

Kelly Moore

Does any other data exists in the rows that are not filling?

Donna B.

Idk what you mean but you may be referring to the succeeding rows:

The first entries to join is Amal + Mohamed which is successful with the join formula i have used in another sheet

What I tried doing (just in excel), copy/drag the formula, to join the succeeding rows.

Kelly Moore

It looks like you're trying to automatically bring all the data from one sheet into another so that the new sheet will keep adding rows as new data is created in the source sheet. The formula won't do create new rows (no formula will do that). It will fill down when the row is created but it will not create a new row. Would my explanation explain why you rows are not filling down? And when it does fill down, there is nothing that indicates which row it is to reference in the source sheet so you will always see the first name in the source sheet list in every row.

Donna B.

Hey @Kelly Moore this I suspect as much. There's no other way then than to do it on the same sheet. Thanks anyway, I might have wasted more time trying to do an impossible idea😂

Kelly Moore

Do you have the smartsheet enterprise license?

Donna B.

Yes I have, we are using Smartsheet at work.

Kelly Moore

There might be two workarounds to try. With the Enterprise license you have access to the Premier Apps. I have found, when using the Premier Apps, starting from a Report instead of the Source Sheet gives me more flexibility in case I ever need to filter data or change sheets. That's my personal preference. So step 1 for me is to build a Report with all the columns that I need. Since it's a Report, I can always add more columns later if needed. You will need to add a helper column to your Source sheet and build your joined name there.

The Report will be your 'source sheet' for the Apps. The app dialogue box in the wizard will specifically ask for a source sheet - ignore the sheet reference and use the report.

These instructions may seem complicated and intimidating but the wizards in the apps will walk you through the process. You should find the process straight-forward - and the community will help you if you get stuck. There is available documentation in the Learning center and Smartsheet Univ about these apps.

1) DataMesh - Using the report, use the joined name as your unique id in both sheets. Choose the option to add new data.

2). Use Datashuttle. This will take two flows. An export flow from your source sheet to export an excel file to your OneDrive, Sharepoint or other designated smartsheet storage location. You will designate a name for the file and new data will always overwrite that file. Flow#2 is an import flow. Run it on a schedule to go get that excel file to add to your destination sheet. Have the flow set to merge/update info.

Give these a shot to see if it will accomplish what you're trying to do

Kelly