Indirect or Address Functions

TerryHumphrey

Is there a way to build sheet addresses in formulas using formulas the way you can with the indirect and address functions in Excel? Specifically I'm trying to achieve in Smartsheet what I achieved in cell F2 in the attached spreadsheet.

Grounds KPI Calculation.xlsx

Genevieve P.

Hi @TerryHumphrey

All the formulas you're using now in the top row could likely be adjusted to calculate going down a column instead of across a row. It sounds like it would be best to show your whole set-up to someone over a screen share and ask them advice on the best way to proceed; if your plan has access to it, a 30-minute Pro Desk could be helpful!

Cheers,

Genevieve

TerryHumphrey

Thank you. I've contacted my reseller.

Genevieve P.

Hi @TerryHumphrey

Smartsheet does not currently have either an Indirect or Address function like Excel (please provide your feedback about this to our Product team through this form, here!)

In regards to the formula you're looking to do, I would suggest adjusting the sheet structure in Smartsheet; I would suggest turning the Month Labour cells into a column, so that you have Months 1 - 12 going down a column. Then next to this you would have the Labour Value column, going down. This means you can set up a Date Column to associate with each Month (ex. Jan 1st, 2022 for Month 1 Labour).

With a date column associated with each Month text, you could use something like a COLLECT Function to filter the range that you're looking to SUM, based on the MONTH in the date column and the date in your referenced date cell.

If you want to go down this route and need help, it would be useful to see a screen capture of your Smartsheet sheet identifying the calculation you'd like to achieve.

Cheers,

Genevieve

TerryHumphrey

Thank you for responding to me. What you couldn't see from the excel spreadsheet was that we have other smartsheets where the PM's enter data via a form and the excel spreadsheet (or main smartsheet) has formulas that pull information from several other smartsheets. I'm not sure how use your suggestion to achieve the outcome I was looking for.

For now, I'll use an excel spreadsheet to perform that last calculation, but I would love to see those two powerful formulas incorporated.

Genevieve P.

Hi @TerryHumphrey

All the formulas you're using now in the top row could likely be adjusted to calculate going down a column instead of across a row. It sounds like it would be best to show your whole set-up to someone over a screen share and ask them advice on the best way to proceed; if your plan has access to it, a 30-minute Pro Desk could be helpful!

Cheers,

Genevieve

TerryHumphrey

Thank you. I've contacted my reseller.