Insert a formula into a reference name + Summary sheet fields

LeoT

Hi. Is there any way to use a formula (or simply a cell) in the reference name to another sheet?

For example, this formula takes the first enrolled student from a source file using a reference name called Module1_Name when there is a "Yes" in the Enrollment column:

=INDEX(COLLECT({Module1_Name}, {Module1_Enrollment}, "Yes"), 1)

Considering I have 12 modules and several other columns to pull out from the source sheet, I was thinking of using Summary sheet fields to make this operation easier. I have created a field called Module with the number 1 inside. I have tested the formula in a cell called [ColumnA]1 and it works well:

=("{"+"Module"+[Module]#+"_Name"+"}")

that generate this result:

{Module1_Name}

At this point, I tried to embed this formula into the first one in this way:

=INDEX(COLLECT(("{"+"Module"+[Module]#+"_Name"+"}"), {Module1_Enrollment}, "Yes"), 1)

but I get INCORRECT ARGUMENT SET.

This way:

=INDEX(COLLECT([ColumnA]1 , {Module1_Enrollment}, "Yes"), 1)

but I get INCORRECT ARGUMENT SET.

And this way

=INDEX(COLLECT({"Module"+[Module]#+"_Name"}, {Module1_Enrollment}, "Yes"), 1)

I got an empty cell but I guess it's because the reference name doesn't exist.

Any good idea to solve this problem?

thank you.

Ariel M

Have you tried using a helper column? instead of embedding your concatenated reference into the formula itself, have it formulate in a separate column (helper) and use that @ cell into the collect function. it would somewhat look like this, =INDEX(COLLECT(Helper@row, {Module1_Enrollment}, "Yes"), 1)

LeoT

Yes, I tried: same error.

Ariel M

Try using the concatenate function. =CONCATENATE("{", "Module", [Module]# , "_Name", "}") instead of the "+" to combine values.

LeoT

Same error... :-(

I've used JOIN instead of CONCATENATE

Genevieve P.

Hiya! Just jumping in here to clarify - Smartsheet does not have a CONCATENATE function. @Ariel M is it possible you're thinking of an Excel formula?

@LeoT in regards to cross-sheet formulas, there currently isn't a way to make the text within the curly brackets dynamic and reference something else.

You'll need to manually type in the reference when you want to refer to another sheet, or manually create a reference if it's the first time.

The {reference} will always need to be in the formula as a direct {reference} item, which is why turning it into a string and adding it in a formula "{reference}" doesn't work, nor does having a formula look at another cell that has the text string {reference}. From within the {reference}, when you add a value such as [Module]#, the formula is not looking at the contents in [Module]# but instead at the words [Module]#.

Please feel free to add your vote and voice to these Product Ideas to let the Product team know of your feature request, or create a new idea of your own if neither of these quite explain what you're looking to do:

• Reference sheet name in formula.
• easier references to other sheets

Cheers,

Genevieve

LeoT

Thank you Genevieve.

So, in my case, to avoid the creation of 12 separate sheets (one for each module) and the manual edit of +50 column reference names for each sheet (50x12=600!!!), is there any other simpler way to do it?

Genevieve P.

Hi @LeoT

There is a limit of 100 unique cross-sheet references that can be created in each sheet. See: Formulas: reference data from other sheets

Depending on what it is you're looking to achieve, it sounds like using a Report to gather your sheets all in one location then using Grouping and Summary features may be a better, scalable solution. Here's a webinar on this: Redesigned Reports with Grouping and Summary Functions

Ariel M

Apologies for the confusion. I'm so used to both platforms sometimes I confuse the functions between them. Thanks for filling us in @Genevieve P. Using Reports is definitely the right option instead of trying to overcome the error @LeoT is facing.

LeoT

I will use the report features on the destination file so the initial task is to apply an initial filter to the source file to create a destination sheet and then apply a filter. Final users have also to add information to the filter so they must have the editor's permission to do it, on the filter and on the sheet. The source file is too important to give editor access. This is why I have to generate a second sheet where they can work freely.

Thank you very much for the help!