SEC | S20W5 | Google Sheets (Creating Sheets & Form Part 2)

This is my homework post for Steemit Engagement Challenge Season 20 Week 5 assignment of Professor @simonnwigwe’s class, Google Sheets (Creating Sheets & Form Part 2).

Task 1 – Editing the Google Sheet

1. Adding Sheet to the Existing Worksheet

1. I created a new sheet in worksheet SEC-S20W4, and named it Summary as directed.
   ^{Image is clickable and might show larger resolution.}
2. I copied the author names from the Database sheet.
   ^{Image is clickable and might show larger resolution.}
3. I already have a list of author names in the Summary sheet.
   ^{Image is clickable and might show larger resolution.}
4. Now I need to fill in the country data, first I will copy it from the Database sheet.
   ^{Image is clickable and might show larger resolution.}
5. I already have a list of country names in the Summary sheet. Next for this statistical need I need to remove the double data.
   ^{Image is clickable and might show larger resolution.}
6. To remove duplicate entries, I select all the cells contain the country names, I pressed the [Data] menu and selected [Data cleanup], then [Remove duplicates].
   ^{Image is clickable and might show larger resolution.}
7. The double entries are gone.
   ^{Image is clickable and might show larger resolution.}
8. Now to make this list of countries look more professional, I will sort them in an A to Z pattern. To do this, I select all the cells contain country names, click on the [Data] on the menu bar, then choose [Sort range], them [Sort range A-Z].
   ^{Image is clickable and might show larger resolution.}
9. This is how it looks after I sorted A-Z and put it into the Country table.
   ^{Image is clickable and might show larger resolution.}
10. I manually filled in the data for the Club table because there were only a few entries. This how it looks so far.
    ^{Image is clickable and might show larger resolution.}
    I need to put Tags entries in the Steemian Details table, before I can copy them to the Tags table in Summary sheet.

2. Adding Column to Steemian Details Table

1. I open the Steemian Details sheet and I mean to put the Tags column between the Community Posted and Country columns. I right-click on the column header D (where the Country column is) to select it, then I select [Insert 1 column left].
   ^{Image is clickable and might show larger resolution.}
2. I fill in the main tag for each Post Link entry in the Steemian Details table. Here's the result.
   ^{Image is clickable and might show larger resolution.}

3. Finishing the Tags Table in Summary Sheet

I now have the data needed to populate the entries in the Tags table in the Summary sheet. I will fill in the data in the Tags table in the same way that I filled in the data for the Country table, i.e:

• first I will copy the data from the Tags column in the Steemian Details sheet;
• Secondly, I will make sure there is no duplicate data with the data cleanup procedure;
• finally I will sort the data using the A - Z method.

This is the result.

^{Image is clickable and might show larger resolution.}

4. Finishing the Count Columns in the Summary Sheet

It is only the SUMIF function is used to fill in all the Count columns in the Summary sheet, with all references pointing to the Steemian Details sheet. What will be difference is the referencing cells, namely:

• for Author Count, the cells that are referenced in the Steemian Details sheet are G2 through G31;
• for Country COunt, the cells are E2 to E31;
• for Club Count, cells F2 through F31; and
• for Tags Count, the cells are D2 to D31.

So the code for the top entry (row 2) of the Count column in each table becomes:

• Author Count column:
  =countif('Steemian Details'!$G$2:$G$31,B2);
• the Country Count column:
  =countif('Steemian Details'!$E$2:$E$31,F2);
• Club Count column:
  =countif('Steemian Details'!$F$2:$F$31,J2);
• Tags Count column:
  =countif('Steemian Details'!$F$2:$F$31,J2).

I've added total rows to calculate each count total, so that if it differs from the number of entries on the Steemian Details sheet (30 entries), then it is immediately known that something needs to be fixed. The result:

^{Image is clickable and might show larger resolution.}

Task 2 – Google Form

1. Creating The Form

1. First question, the short answer type.
   ^{Image is clickable and might show larger resolution.}
2. The second one, also the short answer type.
   ^{Image is clickable and might show larger resolution.}
3. The third one, the long text answer type.
   ^{Image is clickable and might show larger resolution.}
4. The date type comes next.
   ^{Image is clickable and might show larger resolution.}
5. The time type.
   ^{Image is clickable and might show larger resolution.}
6. Here comes the multiple choice question,
   ^{Image is clickable and might show larger resolution.}
7. Again, short answer type.
   ^{Image is clickable and might show larger resolution.}
8. Checkboxes, I love it!
   ^{Image is clickable and might show larger resolution.}
9. The 9th question, again, short answer.
   ^{Image is clickable and might show larger resolution.}
10. Here comes the dropdown.
    ^{Image is clickable and might show larger resolution.}
11. More dropdown.
    ^{Image is clickable and might show larger resolution.}
12. And lastly, the file upload.
    ^{Image is clickable and might show larger resolution.}

2. Giving Access

1. Giving Access to Google Sheet.
   • I press the [Share] button.
     ^{Image is clickable and might show larger resolution.}
   • In the pop-up window that appears, I can fill in a series of email addresses that I give access to. Here it can be seen that the emails I want to give access to are already filled in, this I did last week when doing the assignment for week 4. So here I don't do anything but press the [Done] button.
     ^{Image is clickable and might show larger resolution.}
2. Giving Access to Google Form.
   • I click the [Send] button.
     ^{Image is clickable and might show larger resolution.}
   • Since I want to provide access with the ability to review responses, I will add the owners of the email addresses as editors.
     ^{Image is clickable and might show larger resolution.}
   • I then filled in the email addresses, wrote a message if needed in the message box, and pressed the [Send] button.
     ^{Image is clickable and might show larger resolution.}
   • There is now 1 response (1), and I have linked this Form to a Google Sheets so that response organization can be done easily (2).
     ^{Image is clickable and might show larger resolution.}
   • Here's what the Google Sheets looks like.
     ^{Image is clickable and might show larger resolution.}

Thanks

Thanks, Professor @simonnwigwe for the lesson. I invite @rayfa, @anroja, @el-nailul. Sorry for the tag, my friends.

Pictures Sources

• The editorial picture was created by me.
• Unless otherwise stated, all another pictures were screenshoots and were edited with Adobe Photoshop 2021.

---

My Introductory Post | Artikel Perkenalan Saya.

---

^{Picture created by @aneukpineung78}

Thanks for stopping by.