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

VISION & STRATEGIE HOLDING

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

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)