Dynamic Formula

TWHT

Hi!

I'm using smartsheets to manage my business's projects and I have an automation that triggers when my PO checkbox is ticked, so that it copies the project over to a separate client sheet. Necessary so that I can keep all of my projects for Client A, B, C etc separate, for some specific calculations I need. (Mainly relating to their different payment plans).

The problem is, I'm using this formula to make some calculations on the destination client list so that I can read weekly invoice totals:

=(SUM([Week 24]6:[Week 24]50) - SUMIF(Status6:Status50, "Blue", [Week 24]6:[Week 24]50) - SUMIF(Status6:Status50, "Red", [Week 24]6:[Week 24]50))


and because that counts rows 6-50 as having information, my project that gets sent over gets sent to row 50 and onwards (the first "empty" row), leaving an enormous space, and not getting picked up by the formula.

From what I can tell Smartsheets can't do a dynamic reference like this: [Week 24]6:[Week 24]. So I'm not sure as to how I can have the new projects be automatically read by my formula.

Any ideas regarding a solution? Because if I can't get this to work, Smartsheets becomes completely redundant. 😅

Thanks!

Paul Newcome

Are you able to provide some screenshots for context?

TWHT

It's just going to be the formula in use, but sure:

Is there some more specific context that you'd like?

The project rows get copied over onto the client specific version of this page. They get sent to the bottom of the stack, which due to this formula reading rows 6-50, is 51 and onwards.

I'd like the project either to automatically be copied onto row 7, 8 etc. in order, so that it is still within the reading range of the formula, or, have the formula read rows 6 onwards dynamically, rather than a static 6-50(or any other number, 50 is just being used as a placeholder).

Hope that's clearer.

Thanks!

Paul Newcome

My suggestion would be to move the calculations to another sheet so that you are able to reference the entire column of the source data.

TWHT

Thanks for the tip!

Does each column need to have it's own reference or can you create a reference that includes all of the columns (so as not to have to create 200 odd references for each week of the year, for each client)?

Something like =SUM({Client A[Week 24]}) rather than =SUM({Week 24}) for example? (Only different cause what I typed didn't work aha)