How do I make cell references in formulas dynamic in Smartsheet?

VISION & STRATEGIE HOLDING · 03/31/21

Cell A must be the average of cells D11 and E11 of the different SUCRIVOIRE-BOROTOU-KORO and SUCRIVOIRE-ZUENOULA sheets.

So within A we have the formula = AVG ({reference of D11}; {reference of E11}).

How to ensure that by copying or duplicating the formula from cell A into cells B and C, on the SUCRIVOIRE sheet, we automatically have the formulas:

= AVG ({reference of D12}; {reference of E12}) for cell B and

= AVG ({reference of D21}; {reference of E21}) for cell C?

Sterling Crawford · 04/02/21

If I understand what you're trying to do there's not a way to do it. When you reference another sheet you're creating a static reference. So when you create {reference of D11}, it will always point to D11 regardless of where you copy it to within your sheet. To the best of my knowledge there's not an easy way to do what you're wanting to do. If you just needed the average of each column that wouldn't be too bad, but since you need an average of specific cells I don't know of an easy way to do it. Here's my understanding of what you're needing, so correct me if I've misunderstood:

There are 3 separate sheets

Sheet 1 is a sheet where you're wanting to use formulas to automatically average data from sheets 2 and 3

Each cell in Sheet 1 should be the average of the corresponding cells from sheets 2 and 3, for example:

Sheet1!A1 = AVG(Sheet2!A1, Sheet3!A1)

Sheet1!A2 = AVG(Sheet2!A2, Sheet3!A2)

Sheet1!B1 = AVG(Sheet2!B1, Sheet3!B1)

Paul Newcome · 04/02/21

Actually you can reference two separate sheets within the AVG function (just tested it successfully to confirm)!

To get it to dynamically update for each sheet, I suggest adding a text/number column and enter the numbers 1 - 5 down the side of the table where the data will be displayed.

Then you would use an INDEX function with a cross sheet reference encompassing the entire data portion of the table. THe we can reference the text/number column for the row number and use a COUNTIFS across the label row for the column number.

=INDEX({Sheet 1 Data Portion of Table}, $[Number Column]@row, COUNTIFS($janv$1:janv$1, @cell <> ""))

Duplicate the INDEX function for Sheet 2 then drop them both into the AVG function.

=AVG(INDEX({Sheet 1 Data Portion of Table}, $[Number Column]@row, COUNTIFS($janv$1:janv$1, @cell <> "")), INDEX({Sheet 2 Data Portion of Table}, $[Number Column]@row, COUNTIFS($janv$1:janv$1, @cell <> "")))

You can then dragfill this formula to fill the rest of the data in the grid.

Sterling Crawford · 04/02/21

If I understand what you're trying to do there's not a way to do it. When you reference another sheet you're creating a static reference. So when you create {reference of D11}, it will always point to D11 regardless of where you copy it to within your sheet. To the best of my knowledge there's not an easy way to do what you're wanting to do. If you just needed the average of each column that wouldn't be too bad, but since you need an average of specific cells I don't know of an easy way to do it. Here's my understanding of what you're needing, so correct me if I've misunderstood:

There are 3 separate sheets

Sheet 1 is a sheet where you're wanting to use formulas to automatically average data from sheets 2 and 3

Each cell in Sheet 1 should be the average of the corresponding cells from sheets 2 and 3, for example:

Sheet1!A1 = AVG(Sheet2!A1, Sheet3!A1)

Sheet1!A2 = AVG(Sheet2!A2, Sheet3!A2)

Sheet1!B1 = AVG(Sheet2!B1, Sheet3!B1)

Paul Newcome · 04/02/21

Actually you can reference two separate sheets within the AVG function (just tested it successfully to confirm)!

To get it to dynamically update for each sheet, I suggest adding a text/number column and enter the numbers 1 - 5 down the side of the table where the data will be displayed.

Then you would use an INDEX function with a cross sheet reference encompassing the entire data portion of the table. THe we can reference the text/number column for the row number and use a COUNTIFS across the label row for the column number.

=INDEX({Sheet 1 Data Portion of Table}, $[Number Column]@row, COUNTIFS($janv$1:janv$1, @cell <> ""))

Duplicate the INDEX function for Sheet 2 then drop them both into the AVG function.

=AVG(INDEX({Sheet 1 Data Portion of Table}, $[Number Column]@row, COUNTIFS($janv$1:janv$1, @cell <> "")), INDEX({Sheet 2 Data Portion of Table}, $[Number Column]@row, COUNTIFS($janv$1:janv$1, @cell <> "")))

You can then dragfill this formula to fill the rest of the data in the grid.

VISION & STRATEGIE HOLDING · 04/03/21

https://community.smartsheet.com/discussion/comment/285988#Comment_285988

Sorry for my English, that's exactly my difficulty👍️

Paul Newcome · 04/05/21

@VSH HOLDING Have you taken a look at my solution above? There is not a way to make the reference itself dynamic, but using a little bit of helper data and the INDEX function you can reference the entire grid and make where it pulls from within the grid dynamic.

VISION & STRATEGIE HOLDING · 06/18/21

https://community.smartsheet.com/discussion/comment/285992#Comment_285992

Thanks very that help me

How do I make cell references in formulas dynamic in Smartsheet?

Best Answers

Answers

Help Article Resources

Categories