Module 12: End of Module Project 1

1 00:00:02,520 --> 00:00:08,340 all right what's going on so this is

2 00:00:04,980 --> 00:00:09,960 module 12 in the module project one so

3 00:00:08,340 --> 00:00:12,059 here right off the bat it is saying

4 00:00:09,960 --> 00:00:13,980 files downloaded from the same website

5 00:00:12,059 --> 00:00:16,139 are safe and do not contain viruses but

6 00:00:13,980 --> 00:00:19,020 due to a recent Microsoft policy update

7 00:00:16,139 --> 00:00:22,140 macros in downloaded file are disabled

8 00:00:19,020 --> 00:00:23,820 so to enable it if you are using Windows

9 00:00:22,140 --> 00:00:26,580 follow these and if you are using Mac

10 00:00:23,820 --> 00:00:29,220 follow this so basically macros means a

11 00:00:26,580 --> 00:00:31,679 function in Excel and it is to automate

12 00:00:29,220 --> 00:00:33,059 our task we will be using that in later

13 00:00:31,679 --> 00:00:36,120 steps

14 00:00:33,059 --> 00:00:38,700 so if you are using Windows you just

15 00:00:36,120 --> 00:00:41,879 need to locate your file and then right

16 00:00:38,700 --> 00:00:44,520 click and then show more options and

17 00:00:41,879 --> 00:00:47,399 then properties and then right here you

18 00:00:44,520 --> 00:00:49,559 can click on block and then click apply

19 00:00:47,399 --> 00:00:52,500 and then click OK and then after that

20 00:00:49,559 --> 00:00:54,600 you can open the file

21 00:00:52,500 --> 00:00:57,379 or let's show let's let's read the

22 00:00:54,600 --> 00:01:00,000 instructions so in step one it is saying

23 00:00:57,379 --> 00:01:02,640 is the admission director at this

24 00:01:00,000 --> 00:01:04,379 Academy we can skip this information and

25 00:01:02,640 --> 00:01:07,500 here is the importing one it is saying

26 00:01:04,379 --> 00:01:09,360 go to the payments worksheet and then on

27 00:01:07,500 --> 00:01:12,240 protect it so that you can edit the

28 00:01:09,360 --> 00:01:14,100 contents so let's locate to our Excel

29 00:01:12,240 --> 00:01:16,860 file and then

30 00:01:14,100 --> 00:01:20,400 click on enable content and then go to

31 00:01:16,860 --> 00:01:22,799 the payments saying which

32 00:01:20,400 --> 00:01:25,380 yep payment worksheet

33 00:01:22,799 --> 00:01:27,600 and then we need to unprotect it so

34 00:01:25,380 --> 00:01:29,880 currently if we try to edit any content

35 00:01:27,600 --> 00:01:32,100 let's double time double time it will

36 00:01:29,880 --> 00:01:34,560 say this see it is protected

37 00:01:32,100 --> 00:01:37,020 so just go to the review Tab and then

38 00:01:34,560 --> 00:01:37,979 click on on protect sheet and we are

39 00:01:37,020 --> 00:01:40,979 good to go

40 00:01:37,979 --> 00:01:40,979 want

41 00:01:42,420 --> 00:01:46,560 30 wants to include a title at the top

42 00:01:44,939 --> 00:01:48,479 of the worksheet similar to the ones

43 00:01:46,560 --> 00:01:51,420 used in rest of the workbook in third

44 00:01:48,479 --> 00:01:55,439 order using field gray action color one

45 00:01:51,420 --> 00:01:57,659 shadowy style and then type this as the

46 00:01:55,439 --> 00:02:00,780 worksheet title so

47 00:01:57,659 --> 00:02:03,600 Let's see we need to add word add in

48 00:02:00,780 --> 00:02:05,460 here so for that go to insert Tab and

49 00:02:03,600 --> 00:02:08,340 then here is text click on this drop

50 00:02:05,460 --> 00:02:10,560 down and then here is word art and then

51 00:02:08,340 --> 00:02:13,860 let's see the desired Style style

52 00:02:10,560 --> 00:02:15,480 dressing gray accent color one Shadow

53 00:02:13,860 --> 00:02:18,900 Style

54 00:02:15,480 --> 00:02:21,620 all right so over that and then let's

55 00:02:18,900 --> 00:02:21,620 search for that

56 00:02:21,720 --> 00:02:27,540 feel is gray accent color one it is

57 00:02:24,660 --> 00:02:29,580 white gray accent color one shadow

58 00:02:27,540 --> 00:02:31,440 okay

59 00:02:29,580 --> 00:02:33,900 okay yep and then when you type payments

60 00:02:31,440 --> 00:02:35,660 by account as worksheet title

61 00:02:33,900 --> 00:02:38,700 so

62 00:02:35,660 --> 00:02:40,080 payments let's see buy account

63 00:02:38,700 --> 00:02:42,599 by

64 00:02:40,080 --> 00:02:44,879 account and then it is saying change the

65 00:02:42,599 --> 00:02:47,940 font size to 28 point and then move the

66 00:02:44,879 --> 00:02:53,580 Voda to row one so as that it expands

67 00:02:47,940 --> 00:02:56,400 column C to c3f so 28 points so Ctrl a

68 00:02:53,580 --> 00:02:58,319 select all this text go to Home tab and

69 00:02:56,400 --> 00:03:03,120 then currently it's 54 and represents it

70 00:02:58,319 --> 00:03:06,120 to 28 and then we need to move it here

71 00:03:03,120 --> 00:03:07,920 C through F of Row one so this is Row

72 00:03:06,120 --> 00:03:11,340 one we need to expand it

73 00:03:07,920 --> 00:03:15,000 see through F okay let's see

74 00:03:11,340 --> 00:03:17,640 so this is C and then this is f okay so

75 00:03:15,000 --> 00:03:21,420 we are good let's see other word yep

76 00:03:17,640 --> 00:03:24,120 so same Styles all right so step three

77 00:03:21,420 --> 00:03:27,120 it is saying Tarif 30 would print the

78 00:03:24,120 --> 00:03:30,360 worksheet but no longer plans to do so

79 00:03:27,120 --> 00:03:33,420 clear the print area from the worksheet

80 00:03:30,360 --> 00:03:36,720 all right so for that just go to the

81 00:03:33,420 --> 00:03:38,760 same worksheet and then in page layout

82 00:03:36,720 --> 00:03:41,580 and then here's will be a print area

83 00:03:38,760 --> 00:03:43,319 drop down under page set of group so

84 00:03:41,580 --> 00:03:47,159 just click here and then click on print

85 00:03:43,319 --> 00:03:49,920 clear print area and then that is done

86 00:03:47,159 --> 00:03:51,840 so step four it is saying go to the

87 00:03:49,920 --> 00:03:54,000 entry form worksheet the admissions

88 00:03:51,840 --> 00:03:55,799 staff will use this worksheet to enter

89 00:03:54,000 --> 00:03:58,080 the tuition payment and summarize the

90 00:03:55,799 --> 00:03:59,760 user information tariff wants to make it

91 00:03:58,080 --> 00:04:03,299 easy to record the payment and avoid

92 00:03:59,760 --> 00:04:05,700 error in cell C3 so first let's go to

93 00:04:03,299 --> 00:04:08,459 entry form worksheet entry form and then

94 00:04:05,700 --> 00:04:10,739 in sale C3 okay

95 00:04:08,459 --> 00:04:13,379 add a data validation there at least

96 00:04:10,739 --> 00:04:18,299 that accepts only the values in the

97 00:04:13,379 --> 00:04:20,040 range I3 through I 200 203 okay so we

98 00:04:18,299 --> 00:04:23,280 need to add data validation that accept

99 00:04:20,040 --> 00:04:25,259 list under this range so to add data

100 00:04:23,280 --> 00:04:29,340 valuation validation just click on Cell

101 00:04:25,259 --> 00:04:31,620 C3 and then go to data Tab and then

102 00:04:29,340 --> 00:04:33,780 under data tool group here is data

103 00:04:31,620 --> 00:04:36,180 validation icon just click on it and

104 00:04:33,780 --> 00:04:38,699 then click on data validation and then

105 00:04:36,180 --> 00:04:41,820 currently under allow it is any value we

106 00:04:38,699 --> 00:04:45,419 need to select list and then in source

107 00:04:41,820 --> 00:04:47,940 so it was saying I3 through all the way

108 00:04:45,419 --> 00:04:51,000 to so instead of selecting like this we

109 00:04:47,940 --> 00:04:54,740 can click select up to this and then we

110 00:04:51,000 --> 00:04:58,560 can edit here so I3 through

111 00:04:54,740 --> 00:05:01,080 i2003 and then click OK so let's double

112 00:04:58,560 --> 00:05:04,139 check yep i203

113 00:05:01,080 --> 00:05:06,120 all right so now we can only enter any

114 00:05:04,139 --> 00:05:08,820 values between this range if we try to

115 00:05:06,120 --> 00:05:12,419 enter any other so let's see uh let's

116 00:05:08,820 --> 00:05:14,160 enter i103 and then C and then if we hit

117 00:05:12,419 --> 00:05:16,139 enter it will give us warning right

118 00:05:14,160 --> 00:05:18,360 there like that it is saying this value

119 00:05:16,139 --> 00:05:20,460 does not match okay

120 00:05:18,360 --> 00:05:22,979 all right so let's see how the step in

121 00:05:20,460 --> 00:05:25,380 step 5 it is saying in cell C4 create

122 00:05:22,979 --> 00:05:28,380 another data validation rule that allows

123 00:05:25,380 --> 00:05:32,699 date values greater than or equals to

124 00:05:28,380 --> 00:05:34,440 this okay so just copy this and then in

125 00:05:32,699 --> 00:05:37,500 cell C4

126 00:05:34,440 --> 00:05:40,740 C4 again data tab under data tool there

127 00:05:37,500 --> 00:05:44,759 is data validation and then any value

128 00:05:40,740 --> 00:05:47,580 change it to date and then let's see

129 00:05:44,759 --> 00:05:50,220 so it is saying all out date values

130 00:05:47,580 --> 00:05:52,860 greater than or equals to this

131 00:05:50,220 --> 00:05:57,360 So currently it's between choose greater

132 00:05:52,860 --> 00:05:59,580 than or equals to and then paste that

133 00:05:57,360 --> 00:06:02,039 and then hit OK

134 00:05:59,580 --> 00:06:04,020 so let's read all the instructions it is

135 00:06:02,039 --> 00:06:07,620 saying add an input message to the sale

136 00:06:04,020 --> 00:06:10,020 with the title this and use

137 00:06:07,620 --> 00:06:13,199 with the title data payment and use

138 00:06:10,020 --> 00:06:16,620 enter a date or later as the input

139 00:06:13,199 --> 00:06:20,160 message okay so just copy this text

140 00:06:16,620 --> 00:06:22,199 and then again go to data validation

141 00:06:20,160 --> 00:06:24,539 and then in input message here is

142 00:06:22,199 --> 00:06:26,280 another tab input message in title going

143 00:06:24,539 --> 00:06:28,620 to enter data payment

144 00:06:26,280 --> 00:06:31,080 and let's see

145 00:06:28,620 --> 00:06:32,880 and we need to enter this as the input

146 00:06:31,080 --> 00:06:34,860 message so make sure you copy all of

147 00:06:32,880 --> 00:06:36,360 this including fully stuff including

148 00:06:34,860 --> 00:06:37,919 this period

149 00:06:36,360 --> 00:06:40,560 copy these

150 00:06:37,919 --> 00:06:41,880 and then input message paste this and

151 00:06:40,560 --> 00:06:43,500 then hit OK

152 00:06:41,880 --> 00:06:46,020 and then it is saying add a warning

153 00:06:43,500 --> 00:06:48,720 alert with the title invalidate okay so

154 00:06:46,020 --> 00:06:50,520 copy this

155 00:06:48,720 --> 00:06:53,280 and

156 00:06:50,520 --> 00:06:54,960 again go to data validation any input

157 00:06:53,280 --> 00:06:58,919 message tab we enter this we need to

158 00:06:54,960 --> 00:07:01,880 enter the details in error a lot so in

159 00:06:58,919 --> 00:07:05,160 title just paste that value invalidate

160 00:07:01,880 --> 00:07:07,740 and use these as the error message so

161 00:07:05,160 --> 00:07:09,960 just copy this make sure you also copy

162 00:07:07,740 --> 00:07:13,979 this period

163 00:07:09,960 --> 00:07:17,160 because it is also in bold format

164 00:07:13,979 --> 00:07:19,440 error message paste it and then enter

165 00:07:17,160 --> 00:07:21,900 okay so step six it is saying hide

166 00:07:19,440 --> 00:07:23,819 column I to avoid distracting user with

167 00:07:21,900 --> 00:07:27,180 the account ID list

168 00:07:23,819 --> 00:07:29,580 so just click on this column column I

169 00:07:27,180 --> 00:07:30,419 and then right click and then click on

170 00:07:29,580 --> 00:07:32,340 hide

171 00:07:30,419 --> 00:07:34,620 so let's see done

172 00:07:32,340 --> 00:07:37,500 now instead of saving it is saying after

173 00:07:34,620 --> 00:07:40,500 users enter the account ID date and

174 00:07:37,500 --> 00:07:42,840 payment amount Tarif wants them to click

175 00:07:40,500 --> 00:07:45,300 the entertainment button to add a record

176 00:07:42,840 --> 00:07:47,340 to the late payment table on the

177 00:07:45,300 --> 00:07:50,520 payments worksheet recorder macro named

178 00:07:47,340 --> 00:07:52,380 these stored in the current workbook use

179 00:07:50,520 --> 00:07:53,880 macro recording for from the following

180 00:07:52,380 --> 00:07:56,099 steps

181 00:07:53,880 --> 00:07:58,500 and so it is saying go to the payment

182 00:07:56,099 --> 00:08:02,400 worksheet and then click CLS so first

183 00:07:58,500 --> 00:08:07,020 let's let's do this copy this

184 00:08:02,400 --> 00:08:10,020 copy this and then it is saying

185 00:08:07,020 --> 00:08:12,419 after so we need to enter we need to

186 00:08:10,020 --> 00:08:15,599 create this macros stored in the current

187 00:08:12,419 --> 00:08:17,400 workbook okay so to create any macros

188 00:08:15,599 --> 00:08:19,620 just go to the developer tab if you

189 00:08:17,400 --> 00:08:21,840 don't have developer tab just hover any

190 00:08:19,620 --> 00:08:24,900 item in this tab and then right click

191 00:08:21,840 --> 00:08:27,000 and then click on customize the ribbon

192 00:08:24,900 --> 00:08:30,060 and then this we will appear and under

193 00:08:27,000 --> 00:08:32,279 main tab just make sure you check this

194 00:08:30,060 --> 00:08:34,800 developer option and then click OK and

195 00:08:32,279 --> 00:08:37,080 developer tabs will appear here and then

196 00:08:34,800 --> 00:08:38,599 just click on record macro and then the

197 00:08:37,080 --> 00:08:41,700 name of the macro is

198 00:08:38,599 --> 00:08:44,099 entertainment and then store macro in

199 00:08:41,700 --> 00:08:45,420 this works in this workbook make sure

200 00:08:44,099 --> 00:08:48,000 you select that

201 00:08:45,420 --> 00:08:50,399 and then click ok so the macros is

202 00:08:48,000 --> 00:08:52,620 recording our activities so let's read

203 00:08:50,399 --> 00:08:54,600 all the instructions so it is saying go

204 00:08:52,620 --> 00:08:57,899 to the payments worksheet and then click

205 00:08:54,600 --> 00:09:00,420 sell as for so just click on button your

206 00:08:57,899 --> 00:09:02,040 famous worksheet and then click on sale

207 00:09:00,420 --> 00:09:03,839 as for

208 00:09:02,040 --> 00:09:06,300 and then after that it is saying insert

209 00:09:03,839 --> 00:09:09,600 a table row above click an empty cell

210 00:09:06,300 --> 00:09:11,700 then re-click on sale S4 so we need to

211 00:09:09,600 --> 00:09:14,700 insert table row above

212 00:09:11,700 --> 00:09:18,240 so for that just right click and then

213 00:09:14,700 --> 00:09:20,100 insert and then click table rows above

214 00:09:18,240 --> 00:09:23,519 and then let's see design click and

215 00:09:20,100 --> 00:09:26,519 empty cell then re-click on sale ads4

216 00:09:23,519 --> 00:09:28,620 okay so just click on this LS4

217 00:09:26,519 --> 00:09:31,320 and then it is saying go to the entry

218 00:09:28,620 --> 00:09:35,220 form worksheet and then copy the data in

219 00:09:31,320 --> 00:09:38,519 the range C3 through C5 so in reform

220 00:09:35,220 --> 00:09:40,800 worksheet and then C3 through C5

221 00:09:38,519 --> 00:09:42,120 and then right click and then just click

222 00:09:40,800 --> 00:09:44,220 copy

223 00:09:42,120 --> 00:09:46,080 and then after that it is saying return

224 00:09:44,220 --> 00:09:48,720 to the payments worksheet click the

225 00:09:46,080 --> 00:09:49,920 paste arrow and then click the transpose

226 00:09:48,720 --> 00:09:52,620 options

227 00:09:49,920 --> 00:09:55,380 so just go to the payment to worksheet

228 00:09:52,620 --> 00:09:57,600 and then go to home and then here is

229 00:09:55,380 --> 00:09:59,399 paste option and then make sure you

230 00:09:57,600 --> 00:10:02,040 select the transpose the stop all right

231 00:09:59,399 --> 00:10:04,560 and it will show the description so this

232 00:10:02,040 --> 00:10:05,820 is the transpose just click there

233 00:10:04,560 --> 00:10:09,080 okay

234 00:10:05,820 --> 00:10:12,720 and then it is saying select cell L1

235 00:10:09,080 --> 00:10:14,700 so this is L1

236 00:10:12,720 --> 00:10:17,519 and then it is saying return to the

237 00:10:14,700 --> 00:10:20,040 entry form worksheet press escape and

238 00:10:17,519 --> 00:10:21,600 then click cell B5

239 00:10:20,040 --> 00:10:23,760 so

240 00:10:21,600 --> 00:10:27,060 return to the entry from worksheet yeah

241 00:10:23,760 --> 00:10:31,040 in reform worksheet press escape and

242 00:10:27,060 --> 00:10:34,800 then it is saying to click v15

243 00:10:31,040 --> 00:10:37,800 so B 15 okay

244 00:10:34,800 --> 00:10:41,160 and then we can stop regarding the macro

245 00:10:37,800 --> 00:10:43,620 so just go to the developer tab and

246 00:10:41,160 --> 00:10:45,779 click stop recording so step 8 it is

247 00:10:43,620 --> 00:10:48,660 saying that is plan to display uh

248 00:10:45,779 --> 00:10:50,940 confirmation message after users add a

249 00:10:48,660 --> 00:10:52,860 payment record to the late payment

250 00:10:50,940 --> 00:10:54,959 table enter the

251 00:10:52,860 --> 00:10:57,540 edit the entertainment macro in the

252 00:10:54,959 --> 00:11:00,779 Visual Basic window after the line of

253 00:10:57,540 --> 00:11:03,060 code that selects cell B15 so after this

254 00:11:00,779 --> 00:11:05,040 line we need to insert a falling line

255 00:11:03,060 --> 00:11:08,519 following lines of code and then save

256 00:11:05,040 --> 00:11:10,860 and close the macro so let's copy this

257 00:11:08,519 --> 00:11:11,700 and then it is saying range B15 dot

258 00:11:10,860 --> 00:11:14,459 select

259 00:11:11,700 --> 00:11:16,620 okay so to edit the macro just click on

260 00:11:14,459 --> 00:11:18,959 Macros and then here is the

261 00:11:16,620 --> 00:11:20,940 entertainment this is the macros which

262 00:11:18,959 --> 00:11:23,519 we need to edit just click there and

263 00:11:20,940 --> 00:11:26,459 then click on edit and it will open this

264 00:11:23,519 --> 00:11:30,060 in Visual Basic editor so after this

265 00:11:26,459 --> 00:11:33,380 range b15.select we need to press enter

266 00:11:30,060 --> 00:11:37,140 and then just paste that and then just

267 00:11:33,380 --> 00:11:38,100 to format this in same intent just press

268 00:11:37,140 --> 00:11:39,959 tab

269 00:11:38,100 --> 00:11:41,820 and then

270 00:11:39,959 --> 00:11:44,579 click on Save

271 00:11:41,820 --> 00:11:47,760 and then close this

272 00:11:44,579 --> 00:11:49,620 okay so step 8 is done uh not done here

273 00:11:47,760 --> 00:11:51,779 it is saying assign the entertainment

274 00:11:49,620 --> 00:11:54,480 macro to the enter payment button or

275 00:11:51,779 --> 00:11:56,160 rounded rectangle shape okay so this is

276 00:11:54,480 --> 00:11:58,200 the button just click here and then

277 00:11:56,160 --> 00:12:01,019 right click and then here is assign

278 00:11:58,200 --> 00:12:02,459 macro and then enter payment and then

279 00:12:01,019 --> 00:12:05,519 click ok

280 00:12:02,459 --> 00:12:07,860 and then it is saying uh enter the

281 00:12:05,519 --> 00:12:09,779 following assign okay so step 8 is done

282 00:12:07,860 --> 00:12:12,540 now step nine it is saying enter the

283 00:12:09,779 --> 00:12:15,360 data found in table one on the entry

284 00:12:12,540 --> 00:12:17,459 form form worksheet and run the entire

285 00:12:15,360 --> 00:12:20,339 payment macro to test the confirmation

286 00:12:17,459 --> 00:12:22,560 message that has been added correctly so

287 00:12:20,339 --> 00:12:25,680 we need to enter this in range CS3

288 00:12:22,560 --> 00:12:28,220 through C5 so in C3 we need to enter AC

289 00:12:25,680 --> 00:12:32,760 1027

290 00:12:28,220 --> 00:12:36,120 so here I wanted to enter 1 0 2 7 and

291 00:12:32,760 --> 00:12:40,200 then enter and then date is 10 7 20 21

292 00:12:36,120 --> 00:12:42,300 10 7 20 21 and amount paid C5 is three

293 00:12:40,200 --> 00:12:45,420 one zero zero so

294 00:12:42,300 --> 00:12:46,320 wanted to do it three one zero zero and

295 00:12:45,420 --> 00:12:47,459 enter

296 00:12:46,320 --> 00:12:49,740 okay

297 00:12:47,459 --> 00:12:51,540 so it is saying and run the

298 00:12:49,740 --> 00:12:53,519 entertainment macro to test the

299 00:12:51,540 --> 00:12:56,220 confirmation messages when I applied

300 00:12:53,519 --> 00:12:57,600 okay so just click here

301 00:12:56,220 --> 00:12:59,180 and then it is saying payment

302 00:12:57,600 --> 00:13:02,339 information

303 00:12:59,180 --> 00:13:04,160 and then if we go to payment staff so it

304 00:13:02,339 --> 00:13:07,800 added this

305 00:13:04,160 --> 00:13:09,600 3100 and then one zero two seven oops

306 00:13:07,800 --> 00:13:12,660 which we entered here

307 00:13:09,600 --> 00:13:15,839 okay so step 10 it is saying that is

308 00:13:12,660 --> 00:13:18,120 created a macro named clear that clears

309 00:13:15,839 --> 00:13:20,639 the data in the range C3 through C5 as

310 00:13:18,120 --> 00:13:22,320 well as the confirmation message he

311 00:13:20,639 --> 00:13:25,079 assigned the clear macro to the clear

312 00:13:22,320 --> 00:13:29,579 entries button so let's see up here is

313 00:13:25,079 --> 00:13:32,880 clear entries button and ask you to test

314 00:13:29,579 --> 00:13:35,459 the assigned macro click the enter click

315 00:13:32,880 --> 00:13:38,040 the clear entries button to run the

316 00:13:35,459 --> 00:13:40,320 clear macro so just click here

317 00:13:38,040 --> 00:13:42,839 and then you are ready to add another

318 00:13:40,320 --> 00:13:44,639 payment enter an account ID so it

319 00:13:42,839 --> 00:13:46,320 cleared everything

320 00:13:44,639 --> 00:13:49,380 okay

321 00:13:46,320 --> 00:13:51,480 so Step 11 it is saying Tarif wants to

322 00:13:49,380 --> 00:13:54,480 display statistics about tuition payment

323 00:13:51,480 --> 00:13:56,220 in the range E3 through z12 this

324 00:13:54,480 --> 00:13:58,459 information is stored in the summary

325 00:13:56,220 --> 00:14:01,019 paper table on the payment summary

326 00:13:58,459 --> 00:14:02,959 worksheet okay so let's go to payment

327 00:14:01,019 --> 00:14:05,279 summary worksheet

328 00:14:02,959 --> 00:14:07,560 and then it is saying in the summary

329 00:14:05,279 --> 00:14:09,600 pivot table so this

330 00:14:07,560 --> 00:14:12,180 this is the pivot table because when I

331 00:14:09,600 --> 00:14:14,160 click on the cell these two tab appears

332 00:14:12,180 --> 00:14:16,980 if I click other cells the tab will

333 00:14:14,160 --> 00:14:20,459 never tab will not appear but if we

334 00:14:16,980 --> 00:14:22,680 click here the tab will appear so pivot

335 00:14:20,459 --> 00:14:24,180 table analyze and the name of your table

336 00:14:22,680 --> 00:14:25,800 is summary

337 00:14:24,180 --> 00:14:29,100 okay

338 00:14:25,800 --> 00:14:31,980 so it is saying is in the merged cell E4

339 00:14:29,100 --> 00:14:34,860 use this function to display the number

340 00:14:31,980 --> 00:14:37,260 of registered student from cell B9 in

341 00:14:34,860 --> 00:14:41,459 the summary pivot table okay we need to

342 00:14:37,260 --> 00:14:43,560 use get pivot data function in cell E4

343 00:14:41,459 --> 00:14:45,600 so we don't need to switch anything we

344 00:14:43,560 --> 00:14:48,180 just need to stay here and then enter

345 00:14:45,600 --> 00:14:50,880 those data with the reference of these

346 00:14:48,180 --> 00:14:52,920 data inside payment summary so it is not

347 00:14:50,880 --> 00:14:55,260 telling anywhere that we need to switch

348 00:14:52,920 --> 00:14:57,300 the worksheet so we just need to stay in

349 00:14:55,260 --> 00:15:00,360 the same worksheet and then in the

350 00:14:57,300 --> 00:15:03,720 merged cell E4 so E4 is the merged cell

351 00:15:00,360 --> 00:15:06,420 which is formed by merging row of four

352 00:15:03,720 --> 00:15:08,940 five and six so this is the required

353 00:15:06,420 --> 00:15:10,860 cell we need to enter gate pivot data

354 00:15:08,940 --> 00:15:12,899 function so equals to

355 00:15:10,860 --> 00:15:16,079 and then get

356 00:15:12,899 --> 00:15:18,779 your data so just double click and then

357 00:15:16,079 --> 00:15:21,660 it accepts data field pivot table

358 00:15:18,779 --> 00:15:23,519 okay so let's see it is saying use this

359 00:15:21,660 --> 00:15:26,040 function to display the number of logist

360 00:15:23,519 --> 00:15:28,279 third student from cell B9 in the

361 00:15:26,040 --> 00:15:31,800 summary pivot of the payment summary

362 00:15:28,279 --> 00:15:34,579 worksheet social B9 so we need to add

363 00:15:31,800 --> 00:15:39,620 data field let's see in payment summary

364 00:15:34,579 --> 00:15:43,800 so in B9 so this is B9

365 00:15:39,620 --> 00:15:45,899 okay so let's see so the data field name

366 00:15:43,800 --> 00:15:49,560 is registered student

367 00:15:45,899 --> 00:15:52,079 say Escape so just go here and then sell

368 00:15:49,560 --> 00:15:54,480 B9 and then go to key whatever analyze

369 00:15:52,079 --> 00:15:56,880 and the active field name is registered

370 00:15:54,480 --> 00:15:59,639 student so we just need this value just

371 00:15:56,880 --> 00:16:03,420 copy that and then go to entry form and

372 00:15:59,639 --> 00:16:07,440 then again get viewer data and then the

373 00:16:03,420 --> 00:16:11,820 data field name is registered student

374 00:16:07,440 --> 00:16:16,980 okay and then pivot table is

375 00:16:11,820 --> 00:16:19,019 payment summary and then the B9 sale

376 00:16:16,980 --> 00:16:21,240 okay

377 00:16:19,019 --> 00:16:23,820 and then this

378 00:16:21,240 --> 00:16:27,120 inter parenthesis so get pivot data

379 00:16:23,820 --> 00:16:29,220 assets data field name so that data

380 00:16:27,120 --> 00:16:31,620 field name is still always be surrounded

381 00:16:29,220 --> 00:16:34,199 by quotation and then it is saying cell

382 00:16:31,620 --> 00:16:38,880 B9 so the name of the B9 is registered

383 00:16:34,199 --> 00:16:41,639 strength and then the pivot table is B9

384 00:16:38,880 --> 00:16:43,860 cell the reference is B9 cell and then

385 00:16:41,639 --> 00:16:46,680 payment summary worksheet

386 00:16:43,860 --> 00:16:49,440 okay and then hit enter and then it

387 00:16:46,680 --> 00:16:50,639 should enter to one

388 00:16:49,440 --> 00:16:53,339 okay

389 00:16:50,639 --> 00:16:55,079 so it is also said here clearly we can

390 00:16:53,339 --> 00:16:56,399 edit again it is saying use this

391 00:16:55,079 --> 00:16:59,579 function to display the number of

392 00:16:56,399 --> 00:17:01,620 registered from cell B9 in the summary

393 00:16:59,579 --> 00:17:04,679 pivot table on the

394 00:17:01,620 --> 00:17:06,959 so in pivot table this is the reference

395 00:17:04,679 --> 00:17:09,480 so on the payment summary worksheet we

396 00:17:06,959 --> 00:17:10,500 entered B9 which is in summary pivot

397 00:17:09,480 --> 00:17:13,079 table

398 00:17:10,500 --> 00:17:15,000 so I'll repeat it again

399 00:17:13,079 --> 00:17:17,520 so the data field name is register

400 00:17:15,000 --> 00:17:20,040 student because it is saying we need to

401 00:17:17,520 --> 00:17:22,980 add this B9 take this v9 as the

402 00:17:20,040 --> 00:17:24,660 difference and if we go to view whatever

403 00:17:22,980 --> 00:17:27,120 analyze the active field name is

404 00:17:24,660 --> 00:17:29,760 registered student so that soon will be

405 00:17:27,120 --> 00:17:32,100 registered student because

406 00:17:29,760 --> 00:17:34,500 it is Data field and data field is

407 00:17:32,100 --> 00:17:38,340 always accepts string value in quotation

408 00:17:34,500 --> 00:17:40,260 and then the pivot table is B9 sale of

409 00:17:38,340 --> 00:17:42,299 payment summary worksheet this is the

410 00:17:40,260 --> 00:17:44,220 reference to the pivot table

411 00:17:42,299 --> 00:17:47,400 okay

412 00:17:44,220 --> 00:17:49,980 and in mods the cell G4 display the

413 00:17:47,400 --> 00:17:53,460 total amount of Tucson build from cell

414 00:17:49,980 --> 00:17:56,940 C9 okay so in G4 we can copy the same

415 00:17:53,460 --> 00:18:00,179 formula that's going to be G4 Escape

416 00:17:56,940 --> 00:18:04,919 G4 and then paste here so it is saying

417 00:18:00,179 --> 00:18:08,100 which cell cld9 so D9 is the pivot table

418 00:18:04,919 --> 00:18:10,740 refresh cell and then the data field

419 00:18:08,100 --> 00:18:14,580 name is again we need to go to payment

420 00:18:10,740 --> 00:18:18,299 summary and then go to

421 00:18:14,580 --> 00:18:21,120 it is saying in cell C9 so let's go to

422 00:18:18,299 --> 00:18:23,460 C9 and then pivot table analyze and then

423 00:18:21,120 --> 00:18:26,039 the active field is total width so just

424 00:18:23,460 --> 00:18:29,820 copy that and then

425 00:18:26,039 --> 00:18:33,360 this is the data field name

426 00:18:29,820 --> 00:18:36,240 just delete this and then paste and then

427 00:18:33,360 --> 00:18:39,240 in quotation and then enter

428 00:18:36,240 --> 00:18:41,700 okay and then it is saying in Mars cell

429 00:18:39,240 --> 00:18:44,160 G8 display the total amount of tuition

430 00:18:41,700 --> 00:18:47,580 paid from cell D9 of the summary table

431 00:18:44,160 --> 00:18:50,580 summary fever table so D9 is the

432 00:18:47,580 --> 00:18:53,520 reference of this paper table

433 00:18:50,580 --> 00:18:55,440 let's just go here and then paste the

434 00:18:53,520 --> 00:18:58,080 same from oops

435 00:18:55,440 --> 00:19:01,200 go here copy the formula

436 00:18:58,080 --> 00:19:03,240 and then escape and then click here and

437 00:19:01,200 --> 00:19:05,940 then delete this and then paste that

438 00:19:03,240 --> 00:19:07,559 formula and then the reference cell this

439 00:19:05,940 --> 00:19:11,059 time is D9

440 00:19:07,559 --> 00:19:11,059 oops D9 in both

441 00:19:11,520 --> 00:19:17,780 the previous one is C9 but this G8 is D9

442 00:19:14,820 --> 00:19:17,780 so let's check again

443 00:19:18,900 --> 00:19:23,340 B9 so this must be C9

444 00:19:21,660 --> 00:19:26,640 enter

445 00:19:23,340 --> 00:19:32,280 and then this is D9 but the name is not

446 00:19:26,640 --> 00:19:34,620 total build the name is D9 yep D9 so go

447 00:19:32,280 --> 00:19:38,460 to payment summary and then click on D9

448 00:19:34,620 --> 00:19:40,260 and then go here and then total paired

449 00:19:38,460 --> 00:19:41,940 copy this

450 00:19:40,260 --> 00:19:46,220 and then again go here

451 00:19:41,940 --> 00:19:46,220 and then change this total pair

452 00:19:46,980 --> 00:19:53,580 and then quotation mark and then enter

453 00:19:49,860 --> 00:19:55,440 okay okay now step 12 it is saying to

454 00:19:53,580 --> 00:19:58,200 prevent users from modifying the

455 00:19:55,440 --> 00:20:00,900 formulas Tarif wants you to protect the

456 00:19:58,200 --> 00:20:03,480 entry form of oxid unlock cell C3

457 00:20:00,900 --> 00:20:07,200 through C5 so that users can enter data

458 00:20:03,480 --> 00:20:09,539 in the range okay so let's see so C3

459 00:20:07,200 --> 00:20:12,600 through C5 we need to unlock it so to

460 00:20:09,539 --> 00:20:15,900 unlock this just right click and then

461 00:20:12,600 --> 00:20:18,299 click on format sales and then click on

462 00:20:15,900 --> 00:20:21,960 protection Tab and then uncheck this

463 00:20:18,299 --> 00:20:24,059 locked check mark and then click ok

464 00:20:21,960 --> 00:20:26,100 all right so after that it is saying

465 00:20:24,059 --> 00:20:28,440 protect the worksheet without using a

466 00:20:26,100 --> 00:20:30,539 password so to predict this worksheet

467 00:20:28,440 --> 00:20:34,080 just go to the review Tab and then click

468 00:20:30,539 --> 00:20:35,880 on protect sheet and then select log

469 00:20:34,080 --> 00:20:38,280 cell select unlock cell project

470 00:20:35,880 --> 00:20:41,400 worksheet and content of the log sales

471 00:20:38,280 --> 00:20:44,220 so just leave LEDs and then don't enter

472 00:20:41,400 --> 00:20:47,220 any password and then click OK all right

473 00:20:44,220 --> 00:20:49,860 so now if we try to edit any other sales

474 00:20:47,220 --> 00:20:52,100 so just do double click it will say this

475 00:20:49,860 --> 00:20:55,080 is the product seed we cannot make sense

476 00:20:52,100 --> 00:20:58,380 but if we oops

477 00:20:55,080 --> 00:21:00,299 but if we try to edit any these any of

478 00:20:58,380 --> 00:21:02,580 this we should be available

479 00:21:00,299 --> 00:21:04,740 so we can edit this we can edit this we

480 00:21:02,580 --> 00:21:07,679 can edit this but we cannot edit this

481 00:21:04,740 --> 00:21:09,720 cell all right so that is what we wanted

482 00:21:07,679 --> 00:21:12,059 okay

483 00:21:09,720 --> 00:21:14,760 so we need to unlock this so that is

484 00:21:12,059 --> 00:21:17,160 unlocked all right so step 12 is done

485 00:21:14,760 --> 00:21:18,900 that was our final step so let's check

486 00:21:17,160 --> 00:21:22,039 our result

487 00:21:18,900 --> 00:21:24,660 so in payments worksheet

488 00:21:22,039 --> 00:21:27,900 let's see

489 00:21:24,660 --> 00:21:30,419 here AC one zero two seven three one

490 00:21:27,900 --> 00:21:32,960 zero zero so everything is good here and

491 00:21:30,419 --> 00:21:36,600 then here

492 00:21:32,960 --> 00:21:40,280 one two four seven yep everything is

493 00:21:36,600 --> 00:21:40,280 good and then here

494 00:21:41,039 --> 00:21:46,020 also everything is good okay if you are

495 00:21:43,740 --> 00:21:48,840 confused in any step just comment down

496 00:21:46,020 --> 00:21:52,340 below and I'll make sure to reply it

497 00:21:48,840 --> 00:21:52,340 okay thank you for watching