Grid That Sums By Sub Categories

AKBuffalo

I'm new to Smartsheet and I'm running into a problem I can't seem to figure out after searching the community and docs.

I have two grids. One with team budget data and another with team spend data.

I have two goals, create a report showing the team spend grouped by items and a report with the the remaining balance of the budget. Complicating things is I won't know the items they spend on in advance. As you can see in the example, Team A bought a Pineapple they aren't budgeted for. Hard coding all possible fruits in the world in a big sumifs isn't a solution.

I can't seem to solve the last use case of the budget balance because the only solutions I can come up with for the first which is a dependent, like creating a report, won't work because you can't create reports of reports.

In Excel I'd use a query function but I don't think Smartsheet has that, correct? Any tips or tricks?

Expected Output Reports

Neil Watson

Hi @AKBuffalo

I will start off by saying this is not a very elegant solution!

I started off with the Spend sheet and add a column that identifies whether the item is a budgeted item or not, using a cross sheet formula that looks at the column "Fruit". Budgeted is a checkbox type.

Back on New Sheet, I added a category under each team called "Unbudgeted".

I then used various formula to bring in from the spend sheet the costs for each team and fruit, and also the costs for the things that are not budgeted.

Reports can then be developed from these 2 sheets.

AKBuffalo

This looks good and I think may solve my issue.

One thing that is tripping me up is how you populated "unbudgeted" in the New Sheet Fruit column?

Neil Watson

@AKBuffalo the formula contains a cross sheet reference back to New Sheet, that references the fruit column

I manually added the unbudgeted row in the New Sheet and typed in unbudgeted. I could not think of a way to get around this step and realise it could be tedious for large data sets.