From specific cell end of the sheet

Options

Hi,

I have a project template in which I would like to use a formula that allows me to search in a range that starts at a specific cell but ends at the last line of the sheet. In some sheets I have 100 rows and in some sheets I have 500 rows so I can't put, for example, [Actual Material Costs]2:[Actual Material Costs]500 in my template because if I have less rows it gives me an error and if I have more rows that are added over time I won't get the data from row 501. Is there a way to replace the 500 with a "last line" indication?

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi @Julie Barbeau ,

    The only things I can come up with are work around. If the start row is constant, you could make all rows below that children and then use COUNT(Children()). Alternatively, you might be able to use the entire row [Actual Material Cost]:[Actually Material Costs] and then subtract the top rows with something like -sum[Actual Material Costs]1:[Actual Material Costs]3.

    Give you anything useful?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!