Is there a limit to the number of automations and number of sheets a report can reference?

Leslie Merlino

I am looking for options where I am hitting a sheet limit for cross reference formulas. I may need to export to separate sheets, I would be using a copy rows automation, but if I go this route, it would require 209 separate automations. This is very large and I'd rather not go to time or the trouble of just creating them and hitting a block.

I would then need to pull the information back into a report to pull it together in the proper format.

Anyone know of the limits? I'm not seeing anything online.

Paul Newcome

Are you getting a specific error message?

There are a few "tricks" to consolidate cross sheet references/referenced cells that may or may not work in your case. Are you able to provide some screenshots for reference or more details as to your setup and workflow?

Why do you need 209 separate automations? That's quite a bit!

Leslie Merlino

It's so big. I can but it will probably take a long time to break it down. Let me see what I can put together.

Leslie Merlino

Client has an inventory list

From this list I have set up a report for when they order more inventory. Inside that report is a checkbox for when the item is received. I started with an automation of anytime that checkbox is checked, the line is copied to another sheet to capture price change history. They are using a LIFO process, so prices sold and purchased must be captured in a way that I can use them. This would only create one automation, but the issue I am running into with that is pulling that data out for each item of inventory, (209 items) and breaking it down for that item. I am not sure how to go about that. I haven't found a way to look at that list and find all of the items for each time a purchase was made and if I do, it should solve my issue.

The sheet that I am calculating needs to look something like this...

The red, is pulled in from the inventory list showing how many of the item are committed to production and therefore "sold". Also, this is not a live sheet yet, these numbers are just thrown in to make sure formulas are working....

The green is what is copied from the sheet when a purchase is made. This shows how many were purchased at that price and when they were purchased.

There are formulas using this information to calculate the value of units purchased using the price per unit. Another in the #Sold column that breaks down which order the units used came from. For example, if the number consumed was 120 instead of 24, line 2 would be 90 in the # Sold column and 0 in the Units Remaining column then line 3 would be 30 in the #Sold Column and 60 in the Units Remaining column.

The last 2 columns calculate the value of the units sold and remaining respectively.

So my thought process is that if I can't find a way to pull each item off into it's own sheet like the 2nd picture from a full list of items purchased, that I could do a separate automation for each item pulling it into it's own sheet. However, then I'd also need to do a report to pull the data back together for certain time frames so that all of this information is in one place for my client to review. Plus they'd need to add the sum of each of these sheets together.

Or, if I could also do these calculations inside of that sheet but have the # Sold column calculate only for those items. Right now that formula is: =MIN([Units Purchased]@row, [Our Cost]$1 - SUM([# Sold]2)) but with each purchase, that formula has to be expanded to include the row above it for the #Sold sum...

I've always felt like Smartsheets can do anything excel can do and do it better and faster and with less hassle. So I'm sure there is a way to do this, I just have yet to figure it out. Because it can be done in excel. I'm sure I'm just overthinking it.

Paul Newcome

Let me make sure I understand this second screenshot correctly...

[Units Purchased]2 = 90

[Units Purchased]3 = 90

If the number consumed is 100, then

[# Sold]2 = 90

[# Sold]3 = 10

and

[Units Remaining]2 = 0

[Units Remaining]3 = 80

I assume you want to start with the earliest [Created] first? And all of those numbers adjust based on the number in

[Our Cost]1 <-- (This is the "number consumed" which in my example is 100)

Do I have all of that correct?

Leslie Merlino

Yes, that is correct. I would love a way to leave them in one list, but that continually changing number consumed is causing me problems with my formula because the items don't copy over in order sorting them out.

That would solve the whole issue.

I meet with them again Thursday, if I don't have it solved by then, I may just have to pass on this one. :-/

Paul Newcome

What happens if you (in that same sheet as the second screenshot) on row 5 in a temporary helper column enter...

=COUNTIFS(Created:Created, @cell<= Created@row)

Basically what I want to find out is whether or not the timestamp is also being captured by the COUNTIFS or if it is just looking at dates.

Are they being entered in chronological order so that we can assume that the higher rows are older and lower rows are newer?

Leslie Merlino

Yes, because it's a copy rows automation, so new rows will always appear at the bottom of the sheet.

I'll try that formula quick.

Leslie Merlino

It returns the number 5.

Leslie Merlino

So if we had this list instead, could we narrow down the # sold column to only count the sum of items above that are for the same inventory item??? Using the # Purchased column as the units sold, although I'm not sure that would count properly either, but it would be easy to set up a separate metrics sheet to look at that calculates a running tally of each item sold rather than using the column. I'm thinking the column would overlap items purchased and count them multiple times this way as it is a column that is copied over...

Paul Newcome

And where does [Our Cost]1 come from (the number consumed)?

The basic idea is

IF [Our Cost]1 is greater than the [# Purchased]@row, output zero, otherwise SUMIFS [Primary Column] equals [Primary Column]@row and Created is less than or equal to Created@row and subtract [Our Cost]1 from that total.

Leslie Merlino

Our cost is pulling from the sheet in this screenshot. It's the column highlighted yellow.

In this screenshot, it's the Need for Orders in Production:

Paul Newcome

Ok. That's a little different than you initially had posted.

Do you need what basically amounts to a running total going down the sheet (by item of course).

So basically... There in row 2 you have 24. In row 6 you have an additional 56 which brings us to a total of 80. That means Units Remaining there on row two would drop to 76. Then on row 10 we have another 82 used which would bring the Units Remaining on row 2 down to 0 and the Units Remaining on row 6 down to 176.

Is that right, or do I have it all mixed up?

Leslie Merlino

Yes and no.

So this was my initial idea, but I could not make it work. For 2 reasons.

1. In row six, it's not an additional 56, it's an additional 32. But that's not always the case. If they finished one in the time frame between those purchases it would be an additional 33.... I have not yet hammered down how I am going to keep that number accurate to what's actually been consumed. Because when they finish one, and that number adjusts, it will in a sense, add those items used back into the inventory and we don't want that.
2. The items will flow into this sheet as they are purchased, so they won't be a set distance from each other. If they purchase more of the 5/8" plywood more often than they do the LP Soffit, there will be more rows that contain the plywood in a row before we get to LP Soffit again.

I have worked with some complex formulas but never anything that I've created and I'm just not finding what I need to make it work. I can share the sheets with you if you'd like. I do have permission from them.

Paul Newcome

Ah. So Need For Orders is a running total already. That actually makes it easier. And it doesn't matter how many rows are in between. I was just using those rows for my example since they are all the same item. We would build into the formula(s) to match on [Primary Column]@row.

Really the biggest challenge at this point is accounting for the adjustment when one is finished. The general ideas for the formulas I already have worked out. The details are just going to depend on the actual workflow.

I don't like giving my email out in such a public forum, but please feel free to look me up on LinkedIn where I can share my contact info.

Leslie Merlino

Sent you a message.