Limitations to a sheet?
Hello, I'm in the process of automating a tracking sheet, and it will no longer allow me to copy and paste the formulas into the rows- see screen shots below- it stops at row 1400
The formulas are:
=IF(ISDATE(Start1375), "Complete", IF(ISBLANK(Start1375), "In Progress"))
=IF(Status1375 = "in progress", [Panel S/F]1374, 0)
=IF(Status1375 = "complete", [Panel S/F]1374, 0)
Thanks!
Best Answer
-
Hmm... That's very odd. Try logging out. Then clear your cookies and cache. Then log back in again. See if that helps.
Answers
-
Hi Sean,
Current sheet size limits are 5,000 rows, 200 columns, or 200,000 cells (see here).
How many columns do you have? Is it possible you're approaching one of these limits?
Instead of using row references, what happens if you try using @row instead:
=IF(ISDATE(Start@row), "Complete", IF(ISBLANK(Start@row), "In Progress"))
=IF(Status@row = "in progress", [Panel S/F]@row, 0)
=IF(Status@row = "complete", [Panel S/F]@row, 0)
This will be easier on your sheet as it won't have to search through row numbers to find the referenced number (see here for more information about @row and @cell).
If this hasn't helped, how are you pasting the formula in? Have you tried drag-filling in the formula, instead?
Cheers,
Genevieve
-
oh, as a side-note, I realize you have "Complete" and "In Progress" in one formula, and "complete" and "in progress" in another. Keep in mind that words in "quotes" in formulas are looking for exact matches, including capitals...
you may need to adjust that depending on how it's typed in your sheet, otherwise you'll always have 0 for "Complete" if you're searching for "complete".
-
Someone else pointed out that second comment, I'm learning these lessons as I'm working through Smartsheets.
I'm going to take a stab at the @Row formulas. I still don't fully understand what @ row actually does, I've done some reading, but am not well versed at all in the language so there's been quite the learning curve. Do you have an elementary explanation 😅
-
Haha yes of course, happy to help explain!
Essentially @row replaces the numbered row reference and tells the formula that it only needs to look within that specific row (the one where the formula has been pasted).
So first let's look at how you would use a regular number row reference. For example:
=[Primary Column]1
In this case, [Primary Column] is the name of the column we're referencing. The number 1 after it indicates what row number to look in. You have this properly configured in your formula:
=IF(Status1375 = "complete", [Panel S/F]1374, 0)
Here, you're looking for the word "complete" in the column called Status, and in the row number 1375.
In fact, this is a good example because there's a discrepancy. Notice that for the [Panel S/F] you have row 1374 after it. This means that if the Status in row 1375 is Complete, then you want it to return what's in the cell of the Panel S/F column in the row above, in row 1374. (Is this what you want it to do?)
The numbers can be easy to mix up and lose your place. So if you're always looking to only reference data within the same row as the formula, then you don't need to have numbers. Instead, use @row.
=[Primary Column]@row
Now no matter where I paste in this formula, it will always return the Primary Column's value from that same row.
So for your formula:
=IF(Status@row = "complete", [Panel S/F]@row, 0)
This says, if the status in this row is "complete", then return whatever is shown in this row in the Panel S/F column. Otherwise, return 0.
Does this help clarify? Were you looking to return the Panel S/F from the same row, or from the row above?
-
I was intentionally pulling from above, I'm using it in a manufacturing setting, for each panel, there are 6 steps, so I was basically making a predecessor equation, if that makes sense? when the top row is complete, the bottom automatically moves to In Progress (moving the SF to complete and the following steps SF to In Progress)
I think understand the @ row, your explanation has helped me a lot so far. I'm going to poke around for a few hours. Thank you so much for your time and help!!
-
Ah ok! Good to know it was intentional... in that case, keep the numbers as they are for the Panel S/F column.
No problem at all - let me know if you need any additional help. @Paul Newcome is also a wizard with formulas and explanations.
-
Paul has been a huge help already, thank you both!
-
@Genevieve P Thanks for the vote of confidence. Haha.
I read through the thread and I do actually have a couple of thoughts on it.
Putting text in quotes is no longer case sensitive. I can't remember when this changed. Now if you are concerned about case sensitivity you have the FIND and HAS functions. To help with this, you can always use UPPER or LOWER functions wrapped around your range to convert it to all the same case so that you can enter wither "TEXT" or "text" respectively.
FIND and HAS are actually the only case sensitive functions.
As for the issue of copy/pasting stopping at a certain number of rows... I have run into this before as well on larger sheets. I am not sure exactly why it happens, but I do have a few theories.
My main idea on this is that Smartsheet can only handle 4,000 characters including spaces within a cell. Even if a formula only generates a single digit, if the formula itself is beyond 4,000, Smartsheet will not work.
I have also noticed this is true when pulling a text string using something such as a JOIN function. If you have a ton of rows and use a JOIN function to pull all of them together down a column, it will only pull the first 4,000.
If you are using another function to search that string such as a FIND function, it will only run on the first 4,000 characters. So if you are searching a massive list for a specific email address, and that email address is at the end of the string generated by the JOIN, you won't get a result.
=FIND("email@email.com", JOIN([Email Column]:[Email Column]))
The above formula does not exceed 4,000 characters nor does it have an output that exceeds that limit, but if that JOIN function is pulling in too many characters to analyze, you may not get your expected results. It doesn't produce an error either. It will just show as 0 (zero) meaning not found if that specific email address is too far down the line.
Following along with those lessons learned... I almost wonder if the copy/paste isn't limited like this as well. Will it work if you copy/paste in smaller sections or does it stop at row 1,400 no matter what you try?
-
it stopped at 1400 no matter what, I couldn't drag down, paste, or even type in a new equation. The sheet has 2388 rows and 11 columns so I'm well within the size limits as far as I can tell. I uploaded an excel export of the sheet, if you care to look
-
Well, I just tried again today, and it's allowed me to do another 470 lines, so I'm stopped at 1871. I'm wondering if it needs to take a break between large volumes?
-
Unfortunately excel doesn't preserve any formulas, and excel behaves very differently in some aspects from Smartsheet.
Are you able to "Save as New", remove sensitive/confidential data, then publish as "edit by anyone" and post that published link here?
That would allow us to take a look at the sheet actually in Smartsheet to observe the behavior and see if we can play around with it to try to fix it without messing up any of the real data.
-
I have a thought, Sean...
Do your copy/paste beyond where it is working (so down to maybe row 1,900) and then double click into one of those cells that is beyond where it works but within the range you copy/pasted to.
-
It seems to be working for me in the published view...
-
Interesting- have you saved it?
this is how I'm copying and pasting- maybe that's the problem? I'm attempting to paste in the blue cell
