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