Create a SharePoint list based on Excel sheet w. formulas

Question

Hi,

I have to create a SharePoint list based on an Excel sheet which contains a lot of formulas. There's some manual input, but mostly formulas. 

I know that there's some restriction regarding how many calculated columns there can be in a list, but I my issue is something else.

For example: 

Basic input is a training cost for a specific country, and if there's only one country this would not be an issue. But there mightbe specific costs for up to 20 different countries (meaning that all of these countries have to have there own column in the list?).

Is there a way to "hide" columns if they're not relavant? I know that this should be possible with PowerApps, but at the moment my organization haven't released that yet.

Also, should everything be in the same list? Or is it preferrable to keep formulas(/other things) in a seperate list and then connect the two? 

I'm not sure if the information is enough, but hopefully! 

Thanks a lot. 

Linn

Answer 1

Hi LinnGaf,

I have read your post carefully and I want to share with you my idea.

Although I didn't view your database, there are two basic methods to customize a list, one is modify your sheet directly to meet your expectation , another is create a list then Edit a list view in SharePoint Online.  

If you have a column named Country name, 20 different countries means 20 rows and training cost will be next column. If you use training cost as a column, assume 100$ is one value and there are 20 different countries have the same value, not only these 20 countries should have their own column but also all the countries, because there must be more cost (may be traffic cost, books cost) or other project need to be set as a column. In conclusion, SharePoint don't care even don't know if there is any formula in your sheet, what it care about is presentation of what your sheet display and then show the rang in the list.

According to Create a list based on a spreadsheet, if you want to have two separated lists, you can check steps 7 in the article and choose the different range to export, but as I said before, there is no connection between two list which formula achieve in Excel.

You said you want to hide column that remind me of edit view, you can create a view which show the wanted column, you can also create different view for different people.

If you insists to use formula, there is a way to add formula to existing columns, below two article for your reference.

1. Examples of common formulas in SharePoint Lists.

2. Introduction to SharePoint formulas and functions.

Regards,

Eric

Answer 2

Hi LinnGaf,

If you need further help, you can post your latest condition.

Regards,

Eric