Can I Import a Large Excel Sheet with Embedded Formulas into Smartsheet?
I am needing to import an excel sheet into Smartsheet with 10+ tabs at least 16383 columns. There are imbedded formulas referencing different sheets in the excel. Is there a way to go about this rather than importing one of the tabs at a time, and manually rewriting a lot of the formulas?
Best Answer
-
Given that the formulas in Smartsheet work fairly differently than those in Excel, and that there are MANY functions that Excel has that Smartsheet has no equivalent for, I can't imagine you could import directly while preserving the function of your formulas.
Now - what you COULD do is first preprocess your data in Excel using some VBA to color code any cells/columns that have formulas, possibly (depending on your VBA skill level) even using 2 different colors to highlight one-off formulas vs formulas that you've filled down and could be conceived of as being populated throughout the whole column (you could also, of course, do this manually - whatever works for your scope). Then you could get all the information into Smartsheet in a static format (preserving formatting but losing all formulas) - note that while Smartsheet DOES have an "import from excel" function, it is limited to 20 tabs and will only import the first tab. I've not tested column limits - but I'd expect them to be able to handle a decent chunk but have some type of upper cap. Getting the data in will LIKELY take a chunk of time but be faster with the import function then just copy/paste.
Once you've got the data in, you can then use your color coding to go through your formula cells and try to figure out how to make them work inside of Smartsheet - if they are just simple arithmetic, this should be pretty easy. If they include things like "averageif" and "vlookup" and such, they are doable but trickier ("averageif" can be replicated with an "average(collect())" for example, vlookup can be done natively, but you are better off replacing it with index(match())). Note that Smartsheet works best when you can treat a formula as a column formula - that is, it applies to every single row in the column and will get included on any new rows; this won't work if you are trying to reference specific fixed cells in the formula (the column formula format simply doesn't allow it - you have to manually fill your formula instead if you try that). If you have formulas that you'd like to have be column formulas but need to reference single discreet fixed values, you may need to add columns to your sheet to simply replicate that value over and over. Not efficient, but it gets the job done.
I recently moved a few fairly complicated Excel workbooks into Smartsheet with lots of data collection and reasonably complicated formulas and it was NOT straightforward (somethings that were quite easy in Excel took a fair amount of trickiness to get to work in Smartsheet just do to the more constrained set of available formulas and features - like adding an adaptive linear trend line to a scatter plot) but was 100% DOABLE. And, on the plus side, it was a one-time effort.
Answers
-
Given that the formulas in Smartsheet work fairly differently than those in Excel, and that there are MANY functions that Excel has that Smartsheet has no equivalent for, I can't imagine you could import directly while preserving the function of your formulas.
Now - what you COULD do is first preprocess your data in Excel using some VBA to color code any cells/columns that have formulas, possibly (depending on your VBA skill level) even using 2 different colors to highlight one-off formulas vs formulas that you've filled down and could be conceived of as being populated throughout the whole column (you could also, of course, do this manually - whatever works for your scope). Then you could get all the information into Smartsheet in a static format (preserving formatting but losing all formulas) - note that while Smartsheet DOES have an "import from excel" function, it is limited to 20 tabs and will only import the first tab. I've not tested column limits - but I'd expect them to be able to handle a decent chunk but have some type of upper cap. Getting the data in will LIKELY take a chunk of time but be faster with the import function then just copy/paste.
Once you've got the data in, you can then use your color coding to go through your formula cells and try to figure out how to make them work inside of Smartsheet - if they are just simple arithmetic, this should be pretty easy. If they include things like "averageif" and "vlookup" and such, they are doable but trickier ("averageif" can be replicated with an "average(collect())" for example, vlookup can be done natively, but you are better off replacing it with index(match())). Note that Smartsheet works best when you can treat a formula as a column formula - that is, it applies to every single row in the column and will get included on any new rows; this won't work if you are trying to reference specific fixed cells in the formula (the column formula format simply doesn't allow it - you have to manually fill your formula instead if you try that). If you have formulas that you'd like to have be column formulas but need to reference single discreet fixed values, you may need to add columns to your sheet to simply replicate that value over and over. Not efficient, but it gets the job done.
I recently moved a few fairly complicated Excel workbooks into Smartsheet with lots of data collection and reasonably complicated formulas and it was NOT straightforward (somethings that were quite easy in Excel took a fair amount of trickiness to get to work in Smartsheet just do to the more constrained set of available formulas and features - like adding an adaptive linear trend line to a scatter plot) but was 100% DOABLE. And, on the plus side, it was a one-time effort.
-
Jgorsich - Thank you! This is very helpful. I appreciate the recommendations for workarounds. This also verified that there isn't a super straightforward path, so I will go ahead and start the transfer.
I really appreciate your quick response and thoughtful answer!
