Linking excel and power point in shared environment

Copper Contributor
I need some advice / guidance
Currently i am managing COVID-19 Data in my Region / State in Excel and Power Point files. I receive different data in form of Excel tables / Power Point files, which causes lot of mess when furnishing "Summary / COVID Statistics files".
Is there any way that I could ask all stakeholders to provide me Data in one common excel file or Database, in a format that is best suited for me. I want to restrict all my Data Sources (I receive Data From District Management, Hospitals and Civil authorities in variety of formats e.g excel tables and ppt files) to follow one common format and not accidentally make mistakes.

Basically, what i need is, One large file or ledger, where all parties can put their Data (probably on Cloud) so that i can link that consolidated excel data to excel charts and further to power point files.

A common problem that i face is when someone shares a ppt file in which there are excel tables / charts linked, I cannot update them due to "Broken link" error. On top of that, there is no way to track which table in powerpoint is connected / linked to which excel file (usually one ppt file is comprised of my tables and charts which are further linked to several excel files).
Need your kind help as COVID Data is growing day by day.
6 Replies

@saddi2u 

 

I find it surprising, in all candor, that you're asking this question now, on May 1 of 2021. Covid is not new, nor is the data gathering process. One of the first things I helped another user with on this site, well over a year ago now, had to do with tracking data on Covid-related work being done by fire fighters.

 

Anyway, I would in your place be asking how your counterparts in other states and regions have solved the same problem.

 

And I certainly wouldn't be using Power Point in any way in the data gathering phase of things; maybe in reporting, but that's a total misuse of Power Point.

 

Have you considered creating a standard form in in Google Sheets for those who submit data to you? That would be one way to standardize, and have multiple users entering in the same place....

 

You don't have an easy task, for sure........

 

If you desire more help, might I suggest you post some sample sheets that show what all the data is that you need, and how it's formatted now, how you'd like it, etc....

Dear Sir,
You are rightly surprised by my query in May 2021 when COVID data management is a thing of past. But here in Pakistan situation is different in District administrative setups. We're still living in 80's trying to represent Data in Word and power points documents / Tables. I realise that we should move to RDBM (Relational Database Mgmt) but our data entry operators /clerks would have hard time understanding it. Moreover, top administrator feels comfortable with outdated power point presentation.
So, I've different sources of Data like Civil Administration, Hopitals etc. Most if them shares ppt and Word file. Though I'm maintaining my own excel sheet (which needs hell of improvement), but sometimes it is useless, because when we share ppt file with administration, they can't edit graph and charts that have been linked with excel. See attached power point data. This is how i receive it. Also check my excel sheet.
I'll share final ppt report after excluding confidential info.
That you were in Pakistan was not at all clear from your first post (nor is there anything in your profile to indicate that), so I had been making an assumption about your being in the US. Now that you've made that clear it makes sense, as you say, that your systems are nowhere near state-of-the-art.

I think I'd focus first on getting my own spreadsheets in order if I were you, getting them into as few solid databases as is possible, from which reports can be more easily generated.
The more that you can do to get people on the same "input" page, of course, the better. I had suggested Google Sheets--it's certainly similar enough to Excel; and it's free. It might take--probably will take--some teaching on your part, especially for those who are (mis)using PPT or Word as data input systems, but it should be possible.
After your valuable suggestion i started working on Google sheet owing to its collaborative feature.
Google sheets are greater but... The most painful shortcoming it has is that it doesn't support excel table. The absence of this one single feature triggered many challenges for me
1. I cannot call data from a Table by its Colum header in any of formula, specially query function. I had to refer columns as A, B and C which is less friendly. Moreover i add new columns in my Data query formula gets messed up.
2. Table doesn't auto fill formulas when new rows are added. To work around i used array formula, which worked pretty good until it came down to calculate 3 day moving average. I could not accommodate 3 moving average inside array formula. I cannot use sum function inside array formula.

Only excel Table fulfill all of the above requirements seamlessly.
I thought of air Table to get the benefits of Database Table but then i had to spent more hours to understand it and than import it to Google sheets.

Here is the link of my Google sheet. Please look into it and suggest a viable solution.

https://docs.google.com/spreadsheets/d/1az3WHu2SBwdcfe73HR67WXrh8gW18JduBYc2lt_8H2s/edit?usp=drivesd...

Hi @saddi2u,

 

as suggested my @mathetes try using Microsoft Forms or google from to collect the data with your selected data-point, this will ease your data entry and your can quickly analyze the data.

 

Microsoft Forms are best to collect the data.

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer & like it to help the other members find it more.

About forms, I currently struggling with following issues:
1. I need my form to be filled by authorised user and not anyone else
2. Since my Table has date column, i want my user to avoid duplicate date entries. For one day, only one record (or Row) is enough. And once the user updates / enteres a record, he / she should be able to modify it at any point in time using "search Date" field.
3. My Table has some Colums that has formula field, so i don't want all of my columns to be filled through Form. I only need selected columns to be filled by user through from.

Your valuable suggestions are appreciated.

Here's the link of Google sheet
https://docs.google.com/spreadsheets/d/1az3WHu2SBwdcfe73HR67WXrh8gW18JduBYc2lt_8H2s/edit?usp=drivesd...