Indirect or Address Functions

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.


Best Answers

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    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

  • 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.
    Genevieve P. Employee Admin
    Answer ✓

    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
    Answer ✓

    Thank you. I've contacted my reseller.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!