Using Variables within Formulas

Jane Truch

Hi,

Is there a way for me to replace "Akeneo - Adobe Exchange Account Alignment" with a variable AND substitute "Range N" with a column name in the formula below? Note that Industry1 is a variable.

For Example:

Here's my formula. It works but I have to edit it for each Smartsheet reference of which there are many.

=COUNTIFS({Akeneo - Adobe Exchange Account Alignment Range 2}, Industry1, {Akeneo - Adobe Exchange Account Alignment Range 4}, <>"Archive", {Akeneo - Adobe Exchange Account Alignment Range 5}, <>"Closed-won", {Akeneo - Adobe Exchange Account Alignment Range 5}, <>"Closed-lost")

I want to do the following:

=COUNTIFS({Partner1 Industry}, Industry1, {Partner1 Priority}, <>"Archive", {Partner1 Stage}, <>"Closed-won", {Partner1 Stage}, <>"Closed-lost")

then cut and paste to a bunch of rows:

=COUNTIFS({Partner2 Industry}, Industry2, {Partner2 Priority}, <>"Archive", {Partner2 Stage}, <>"Closed-won", {Partner2 Stage}, <>"Closed-lost")

Etc...

Thank you!

Genevieve P.

Hi Jane,

It sounds like you're wanting the cross-sheet references to automatically recognize what sheet you want to reference by the text, and change to automatically reference new sheet when you copy/paste in new rows, is that correct? If so, there currently isn't a way to have this be an automatic variable in the same way that a column present in the current sheet can be used as a variable (aka the rows in your Industry column).

Cross sheet references need to be manually selected the first time you reference a new column. However, once you have created the initial connection to that sheet & column, you can copy/paste the text (for example, {Partner2 Industry}), and the formula will now know which sheet/column this references. Should you add in any other text to that same range the formula will be unparseable, as it won't know what column you want it to reference. You'll have to go in and choose the reference again. Our Help Center has more information on cross sheet references (see here).

Cheers!

Genevieve

Jane Truch

This is helpful thanks!