JOIN Formula from Source Sheet to Destination Sheet

Wendy Young

Please help. I'm able to join/concantenate text from different columns in the source sheet to one column in the destination sheet. However, text from different rows in the source sheet is populating in the same cell/row in the destination sheet. Please see the screen shots and formulas below.

SOURCE SHEET:

DESTINATION SHEET:

FORMULA:

=JOIN({Regulatory Form Entries- SOURCE SHEET Range 5})

What am I doing wrong?

Thank you in advance for your help!

Wendy

L_123

to get text from a single range to copy itself to another range I would use the 2 formulas below.

Formula to go in first cell

=index({Regulatory Form Entries- SOURCE SHEET Range 5},1)

formula to go in second cell prior to dropdown

=index({Regulatory Form Entries- SOURCE SHEET Range 5},1+count([regulatory change type]$1:[regulatory change type]1)

Wendy Young

Thank you! The first formula worked perfectly. However, the second formula gives me a circular reference error or an imparseable errors. Please help.

L_123

Can you post exactly what you have in the second formula?

Wendy Young

Okay. My apologies. I guess I pasted it incorrectly. However, I can't seem to update the formula references to make it work for the submission date. This is the formula that I'm trying to use to move the date over: =INDEX({Submission Date}, 1 + COUNT([Submission Date]$1:[Submission Date],3))

Also, how would I consolidate the 5 blue Q2 columns and have them post to the right row?

L_123

For submission date you would need to create a second reference to just the date column. Copy paste the formula over, then delete everything in the curly brackets (including the brackets themselves) then click the button to insert a reference.

you would sit a collect in there to get rid of the blanks

=index(collect({Regulatory Form Entries- SOURCE SHEET Range 5},{Regulatory Form Entries- SOURCE SHEET Range 5},not(isblank(@cell))),1

=index(collect({Regulatory Form Entries- SOURCE SHEET Range 5},{Regulatory Form Entries- SOURCE SHEET Range 5},not(isblank(@cell))),1 + COUNT([Submission Date]$1:[Submission Date],3))

Wendy Young

Okay. I'm sorry. Thank you for your help. I'm sorry. I did all of those things and I still can't get it to work. This is the formula I used for the first row of the Submission Date column. =INDEX({Regulatory Form Entries- SOURCE SHEET Range 2}, 1). It's giving me an Invalid Column Value Error.

Also, destination sheet is not auto populating new entries from the source sheet unless I manually copy and paste / drag the formula down the column.

Wendy Young

AH! Okay. . . I got the submission date column to work. I knew as soon as I messaged you I would figure it out.

Now I'm about to try consolidating the 5 different Q2 responses into the jurisdiction Level / Judicial Body column.

The sheet is still not auto populating the new entries.

Wendy Young

Ok, what am I doing wrong? I deleted the reference and changed the name of the column reference, but it's still telling me that it's Unparseable.

=INDEX(COLLECT({Regulatory Form Entries- SOURCE SHEET Range 5}, {Regulatory Form Entries- SOURCE SHEET Range 5},NOT(ISBLANK(@cell))),1+COUNT([Jurisdiction Level or Judicial Body]$1:[Jurisdiction Level or Judicial Body],3)))

Source Sheet:

Destination Sheet:

Sorry to be such a pain and thank you in again for all of your help.

Wendy

L_123

End of the statement has a couple errors is the first thing I notice

=INDEX(COLLECT({Regulatory Form Entries- SOURCE SHEET Range 5}, {Regulatory Form Entries- SOURCE SHEET Range 5},NOT(ISBLANK(@cell))),1+COUNT([Jurisdiction Level or Judicial Body]$1:[Jurisdiction Level or Judicial Body]1

don't put ending parenthesis at the end of your formula, smartsheet will autopopulate them for you if your formula is correct. It can often tell you where errors are occurring as well.

Wendy Young

Hello again,

Thank you for the info. However, I now have a couple of new problems with the formulas.

First, new entries from the source sheet are not auto populating to the destination sheet.

Second, with the consolidation formulas, on some rows it's populating data that doesn't exist in other rows/cells it's telling me the value is invalid is there's no data in the source sheet. Please see Column 3/Row 3 and Column 5 / Rows 2 and 3 of the destination sheet in the screen shot below.

Source Sheet:

Destination Sheet:

Formula being used in the Jurisdiction Level column:

=INDEX(COLLECT({Regulatory Form Entries- SOURCE SHEET Range 5}, {Regulatory Form Entries- SOURCE SHEET Range 5}, NOT(ISBLANK(@cell))), 1 + COUNT([Jurisdiction Level or Judicial Body]$1:[Jurisdiction Level or Judicial Body]1))

Formula being used in the city column:

=INDEX(COLLECT({Regulatory Form Entries- SOURCE SHEET Range 6}, {Regulatory Form Entries- SOURCE SHEET Range 6}, NOT(ISBLANK(@cell))), 1 + COUNT(City$1:City1))

Formula being used in the state column:

=INDEX(COLLECT({Regulatory Form Entries- SOURCE SHEET Range 7}, {Regulatory Form Entries- SOURCE SHEET Range 7}, NOT(ISBLANK(@cell))), 1 + COUNT(State$1:State1))

=INDEX(COLLECT({Regulatory Form Entries- SOURCE SHEET Range 7}, {Regulatory Form Entries- SOURCE SHEET Range 7}, NOT(ISBLANK(@cell))), 1 + COUNT(State$1:State2))

I do not understand what is happening with this.

Please help.

Thank you!

Wendy

L_123

You need to set up your references as column references. When you set them up, just click on the column header, and it will account for a variable column depth allowing you to reference future values added.

For the second issue, you are only returning values where there isn't a blank in the existing column, which means you can mix values up if one column has a blank in a row where another doesn't. As such you should pick a single column to run criteria on, and return values from the other ones.

example:

=INDEX(COLLECT({Regulatory Form Entries- SOURCE SHEET Range 6},{Regulatory Form Entries- SOURCE SHEET Range 5}, NOT(ISBLANK(@cell))), 1 + COUNT([Jurisdiction Level or Judicial Body]$1:[Jurisdiction Level or Judicial Body]1))

L_123

Also naming your ranges when setting them makes it much easier to troubleshoot, develop, and expand on formula. I always recommend doing so. (you can name them if you click edit reference, then click into the name box at the top left of the popup

Wendy Young

I previously had the ranges named, but removed them all and started over when I requested help from you.

With regard to selecting the column headers when setting up the references, that's what I've done every time even before I requested your assistance. It's still not auto populating on the destination sheet when a new entry appears on the source sheet.

I don't understand what you mean by picking a single column to run criteria on, and return values from the other ones. I just tried your formula again and it's still adding values to the destination sheet that don't exist in the source sheet, and still showing invalid value in the destination sheet even though there is data that it should be pulling from the source sheet or should just be leaving it blank if all of the cells in the source sheet are blank.

Paul Newcome

@Wendy Young I saw your post HERE. I have reviewed this thread and am curious... What is the purpose of moving all of this data into a separate sheet?

Wendy Young

Thank you Paul!

I'm creating this for another team as a master "tracker" for tracking changes in regulatory requirements across the organization. Based on the criteria submitted from the team, I created a form that will be utilized by people across the company to submit regulatory changes. There are different types of responses based on the type of regulatory change being submitted, and the team was insistent on keeping the responses separate instead of trying to consolidate response types. They wanted the similar columns on the sheet consolidated, but wanted the form for those submitting the changes to have the various specified options. I started to just create and hide consolidation columns in the source sheet and then create a report based on just those columns, but that won't allow the team to make changes later when/if a proposed regulation is passed. I hope that makes sense. Thank you again for your response and please let me know if you have a better recommendation. https://app.smartsheet.com/b/form/6dbd683ad87e4bb48e7869ae88da7109

Wendy