2

We currently have an excel file that is a large table that the user fills out and then some graphs are manually created from the data.

We have a sharepoint online modern site (using Spfx for any existing webparts) and so far have come up with the following options

  • leave in excel and add a front end form along with automated graphs
  • change to a SP list then create a form to enter data into this list plus create custom web part to display the results as graphs we want
  • could we use powerapps to do this?

I am looking for anyone who has done something simliar, any pitfalls or advice on which approach to take or suggestions for other routes to go?

  • So...was my lengthy answer of any use? Or did you decide to do it another way? – Tally Jul 27 '18 at 11:28
  • Hi Jason, did you try out my method? – Tally Aug 7 '18 at 11:44
  • Hi, yes we did although the approach we took in the end was to have an excel file saved in sharepoint and then connecting it to a powerapp for a form entry with powerBi for the graphs - so far it has worked very well – Jason Aug 13 '18 at 11:07
0

I've only used SP 2010 & 2013, but I'll tell you my experience in case it helps.

I have a SP List to store data/KPIs -> users update the data monthly with the new KPI for a project (item).

I've used Export to Excel to get the SP list data in to Excel. I keep the file linked and keep it as a table. This means that when users update the SP data, when a user opens the linked Excel file they are prompted to 'update links' to refresh Excel with the latest data. You can also configure the file to update (refresh) automatically when the file is opened.

For your graphs - you can use either a normal graph or use pivot tables and then create the graphs. Either way, the graphs will update when you refresh the Excel file. I believe with pivot table graphs you might need to select each tab and select 'refresh' for the table and graph to update.

So, back to your question - yes create a SP list. You can configure views for people to log their latest PKIs/figures, by using [ME] as a filter in the view e.g. if Person Responsible is a name column, set the view to show the items if Person Responsible is equal to [Me].

You can save Excel tabs as a 'htm' file (actually a web page) - this can be embedded in to a Sharepoint page, it can then show the Excel graph. Insert a web-part in to the page to display the .htm file. OR save each graph as a separate .htm file (web page) - like this: enter image description here

Then if you want to show the progress of e.g. Project 1; you create a graph in Excel -> save as a .htm file, save it in Sharepoint. In your data list, you can create a link which will take the user to project 1's web page graph. If you want to be fancy, you can include an icon and paste a link on the icon - like this:

enter image description here

If you decide to do what I'm describing, make sure you save your Excel graph file on the same site and 'level' as your list - don't save it on a sub-site (I ran in to issues once).

Furthermore, if you want to get really fancy (interactive), you can create a dashboard which incorporates all of your graph web pages as well as you list data. (It all depends on what your requirements are, but management like this kind of thing, very visual and interactive). If you upload a picture to a dashboard page, you can use html to create some links on the text - then connect the link to another page (or view of your list).

Here are some pics of how it looks:

enter image description here enter image description here

I hope this helps a bit!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.