Here is the link to the google sheet:- https://docs.google.com/spreadsheets/d/1n9OhAmcVRuve_tWcGB6TcB1wueILozzx5g1YyoRXrFQ/edit#gid=1149710762
Scripting will be done for this sheet only. Please keep in mind that everything will be online.
I'm trying to collect data from one sheet that are located in a different locations and post them in another sheet as a list.
In this file we I would like to collect information from this sheet "Client Profile" and post them to this one "Client Profile List View"
I would like to collect from "Client Profile" this information:
1. Full Name, found in cell "i2, i24, i46, i68, i90, and so on..."
2. Email Address, found in cell "AR2, AR24, AR46, AR68, AR90, and so on..."
3. Mobile, found in cell "BW2, BW24, BW46, BW68, BW90, and so on..."
4. ID Number, found in cell "i3, i25, i47, i69, i91, and so on..."
5. Nationality, found in cell "AC3, AC25, AC47, AC69, AC91, and so on..."
6. Gender, found in cell "AO3, AO25, AO47, AO69, AO91, and so on..."
7. Medical Condition, found in cell "BD3, BD25, BD47, BD69, BD91, and so on..."
8. Blood, found in cell "BK3, BK25, BK47, BK69, BK91, and so on..."
9. T-Shirt Size, found in cell "BU3, BU25, BU47, BU69, BU91, and so on..."
10. DOA, found in cell "CA3, CA25, CA47, CA69, CA91, and so on..."
And post them to this sheet "Client Profile List View" under the following order:
1. Full Name, to be posted under "B9"
2. Email Address, to be posted under "D9"
3. Mobile, to be posted under "C9"
4. ID Number, to be posted under "E9"
5. Nationality, to be posted under "F9"
6. Gender, to be posted under "G9"
7. Medical Condition, to be posted under "H9"
8. Blood, to be posted under "i9"
9. T-Shirt Size, to be posted under "J9"
10. DOA, to be posted under "K9"
Now the project will be divided in 2 missions
Mission 1:
1. At raw number 178 its the start of a new profile. So if a new client registered we will add another profile on raw 200 and repeat that for every new client.
2. Years will be added as well, now all profiles has 2017, we will need to add 2018 soon, and 2019 when its time and so on, so all raws will be moved down, nothing is fixed in place in this sheet.
3. We just need to take data from data from the first 2 raws of each profile.
Second mission:
1. Now in the "Client Profile" sheet, go to the second client "John Michle" on November you will find a "✔️" on that month for his membership on Sun-Tue-Thu. That mean that this client joined a 4 week program and he is attending 3 times a week
2. now go to "Class Capacity" shert and go to November at raw 55. Now that client joined a 4 week program and he is attending 3 times a week, we would like to create a formula or script whatever,, to make the "✔" that we put on "client profile" sheet to be posted here in "Class Capacity" sheet automatically
3. now if 2 clients joins on same day and date than : go to november 12 on "Cass capacity". as you can see each class has 21 slot. so if two or more clients booked at the same day, their ✔ will be added to the class capacity
Last requirement:
we will need a reporting system that show us each day who is attending as a list for example in november 12 we have 3 ✔, we need to see names of this 3 ✔ in another sheet. to know who booked in this day as a list view