Forum Discussion
Linking excel and power point in shared environment
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....
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=drivesdk