Collaborate on Medical Store Bill Format in Excel for Public Relations with Ease Using airSlate SignNow

Medical store bill format in excel for Public Relations

welcome back to this new video tutorial in Microsoft Excel and here in this video we are going to create a Dynamic hospitals lab report format and here you can see a sample of the lab report is given and here by using Microsoft Excel formula the report is created Dynamic if we change the reference number of a patient form here the information of the patient name is gender then date will be automatically changed here if you see the values are automatically changed and here you can see for example the report on hematology is given here you can see different kind of test name are given and the test name we can select here by using the drop down menu here and the result we have to put manually after getting the report from the lab we have to fill this result here by uh typing here and the unit will be calculated by using the formula this is automatic the bottom value and the top value for the reference range is also automatic if you go to the support data here you can see the name of the test the unit of measurement then the reference range for male and female is given here and these reference range is dynamic if the value given here in age and gender is female then the reference range will change automatically and if it is male then the reference range will change automatically for example if I go here in this seat here you can see the reference range for this PCB is different for male and female for male it is 4254 for female it is 36 to 46 so if you go here in lab report and in this case it is for female and this given is 36 to 46 and if you change this to mail by taking another reference number from here in this case it is male in this case you can see it is now 40 to 54 the reference number change ing to the gender that is given here so this reference range is dynamic and here the result is if the result goes out of the range then it will become bold and will be underlined automatically for example if I put here a value that is within the range like if I put here 45 then The Bold and underlined is removed but if the value is out of the range like if it is 38 for example in this case it is automatically highlighted with the bold and underline so this result column is dynamic here and here the support data is given here the test name there you need bottom value and Top Value is given and in the next seat the percent and the test data is given here we need to record the reference number date patient name age and gender so after upgrading these values then we can create this dynamic lab report in Microsoft Excel and here in this support data I have taken these reference number just for example these values might not be correct so don't refer to these values for actual purpose while making this actually for your use then and don't refer to these values take the values that is used by your hospital so this number here is just for just taken for example now let's go to the lab report and here I am not going to create this format I will put the workbook here Below in the description section you can download this book from the description of the video and use it here I am going to show you how to use the formula because using formula and making the template Dynamic is the important part here so first let's start with the reference number because this is the primary key here based on the reference number all the other values will be calculated here so in the reference number to create this kind of drop down we can use the data validation tool of excel to create a drop down here we have to click on the sale then go to data menu then go to the data validation which is given here and in the setting menu of data validation we have to choose list and in the source you have to link the we have to click on the source then go to the support gate and go to the patient data and we need to then link these reference number by selecting it and while selecting you have to select till the more row here because in future the reference number or the data will expand so select more rows here then click ok and here you can see the drop down is Created from this you can choose the reference number now go to the patient name where we need to use vlooker formula based on the reference number we need to capture the patient name here in this cell so here I am deleting this formula then press equal to vlookup we need to use vlooker formula here vlookup Open Bracket the lookup value will be the reference number so click on the reference number give a comma and in the table array go to the support data go to the paste data and select this difference number to General select all these columns then give a comma and in the column index number the patient name is given under one two three third column of layout table so type 3 in the column index number give a comma and in this uh range lookup we have to choose exact maths so double click on the exact match close bracket and press enter now you get the result which is the patient name now after writing the B lookup formula here then you can use the same formula for the date also you need to change the volume index number for date which is for date the column index number is 1 2 so now you can directly copy this formula from here copy and paste it over here then double click on the formula then here we need to change this lookup value we need to drag it in the reference number then here in instead of three we need to make it 2 for column index number then press enter and the date is given on the numeric format we can change the formatting of the date by right clicking then go to the format sales then go to the date option here date and you can select this date type click ok now here you can see the date is displayed here now after displaying the date here then here in this column we need to use two vlookup formula together because one binoco will capture the age of the patient and another B lookup will capture the gender of the patient so here we need to combine two vlookups press equal to then type the first V root of here vlookup then click on the reference number comma for table array go to the patient data then select from sequence number to gender then give a comma and the column index number for age is one two three four for the age so Type 4 comma then 0 for exact match close the bracket hit enter and you get the gender here now you need to join y here y letter here so to join y letter here double click on the formula then go to the end of the formula type and decimal from keyboard then here type double quotation y double quotation again press enter it joins the Y before the after the is here then we need to combine the male or female here in the formula so we will click on the formula then go to the end of the formula type the and symbol then combine another another B lookup vlookup then open the bracket now in the lookup value click on the reference number comma in the table array go to the patient data select this table then give a comma column index number for gender is one two three four five so here give five is a column index number comma and for range lookup give 0 for exact match close the bracket pressing now here you can see it is given as 32 year mail if you want to give a space between this Y and M then you can double click on the formula go before this second the vlookup then here type double quotation one space then type double quotation again then type and symbol again press enter now you get a space in between this here and mail so the formula has now calculated these values if you change this reference number from here now it displays the values ingly now let's go to the section where you we have to now use formula here in the serial number the serial number is made dynamic because if I delete these test name from here the serial number is automatically removed and if I put the test name here the serial number is auto generated so to create such Dynamic serial number we have to use this formula here equal to then come use the if formula if Open Bracket The Logical test is if the test name click on the test name then equal to then type double quotation it means that if the test name sale is blank two double quotation means it represents blank in the formula if this is blank then give a comma in the value if true we need to display blank if the test name is blank we don't need any serial number there that's why if the test is equal to blank comma in the value if true type blank which is two double quotations now give a comma and in the value of if false we have to give one then close bracket patent which will give you the serial number one here if it contains some value otherwise it does not display in this way now here in the second serial number the formula will be equal to if Open Bracket this sale equal to blank comma the value of true will be blank comma in the value it falls there is the formula will be this serial number or serial number one then Plus 1. now close bracket press enter this will be the second formula now you can go to the plus button and drag it in the last row and all the serial number is auto calculated if these sale contains some value then serial number will be given otherwise not so in this way we automate the serial number here in the first column now in this test name we need to create a drop down list and this drop down is linked to this support editor these values are linked here by using the data validation drop down as we did earlier in the reference number we can create this kind of drop down here also so follow the same method for creating the drop down for the test name here now let's go to the result where we need to put the values manually and here we need to use conditional formatting to automate this bold and underline if the result goes out of the range this should be automatically bold and underlined this we will learn later how to use conditional formatting here then here in the unit we need to use the vlooker formula to get the unit so here I am going to show you how to write formula equal to B lookup the lookup value is the test name given here click on the test name comma the table array is the support data and then select the test name till the unit this way comma and it is given on the first second column of the selection so give two as a column index number comma and in the range lookup give 0 close bracket press enter which will which will give you the unit you can now drag this till the last row and here the reference not available error is given to remove this not available error we can combine the if error formula because if this cell contains no value then it will display if n a error here so is to remove search any error you can double click on the formula go at the beginning of the formula then type the if error formula if error open the bracket go to the end of the formula then give a comma the type double quotation close bracket press enter now it will display the unit now you can drag this till the last row and here you can see if this cell contains no value then it will also become blank so in this way we display unit here by using vlookup now here we need to Now display the bottom value and Top Value by using the B lookup and this formula we need to create conditional formula because this depends upon the gender that is given here if it is male then the reference range will be different and if it is female the reference range will be different so in this case we have to make a conditional formula so here the formula will be I am deleting this formula from here delete this formula then I am now going to write the formula here for the bottom value of the reference range so press Q into then here we need to use the if formula to identify whether it is male or female so if Open Bracket The Logical test is if the last later of these age and gender is male in that case we have to capture the reference range of the male so here I am going to use the right formula to capture these last later which is rigsd right Open Bracket in the text we have to click on this cell then give a comma and in the number of characters we have to give one we have we are going to get the last character of these so we have given one here then close the bracket then here we need to give equal to then M within the inverted comma if the last layer of this is m in that case now give a comma and in the value if true now we need to get the bottom reference strings for the mean if it is male then when you can in the value if true you need to get the bottom range for mail so the formula here will be B lookup now vlookup in the lookup value click on the test name given here then give a comma and in the table array we need to go to the support data then select this test name data this way and here here for mail the bottom value column index number is one two three so give a comma and in the column index number give three because the bottom value for mail is given on the third column that's why I give 3 here then give a comma and in the range lookup give 0 for exact match then close the bracket this will work as a value if true now give a comma and in the value if false we need to display the reference range for female the bottom reference range for female so here in this case combine the another B Loop of in the value it falls combine this B lookup formula vlookup and in the lookup value of this below curve we have to go to the lab report sheet then click on the test name comma the table array we need to go to support data then select this table again and here give a comma and in the column index number we need to now give one two three four five for the females bottom value type 5 here then close black give a comma then give 0 for the exact match close the bracket then we need to now please or close one more bracket for the if formula then press enter and here you can see the value given is 40. the bottom value for female is given as 40 if you go here the bottom value of bottom value of this neutral field for female is 40 which is same with the male here and now if you drag this formula down drag this formula down this way here you can see the PCB value for bottom value of PCV for female is given as 36 if you go to support data the PCB values bottom range for female is given at 36 if I change this to male by going changing this reference number in case of mail the bottom value is given is here it should display 40 because for for mail the the bottom value of the PCB should be given as 40. but it is still displaying 36 years so need to check the formula here here in this case we need to fix the references that's why the error has happened because in the formula we have to fix this range this C11 range should be fixed in the formula so here go to the C11 then press F4 key in the keyboard which will apply the dollar sign here then press enter after fixing the F key here then drag it down this way now it has changed for mail the bottom value of PCB is given as 40 and if we change this to female by changing one new reference number now for female it is given as 36 now it is working and to remove this not available error you can go to the formula here go to the beginning of the formula then combine the if error here if error Open Bracket go to the end of the formula give a comma and here type double quotation close bracket Center now you can drag the formula down and here you can see the any error is removed now after giving the formula here in the bottom value then we need to now give the formula in the top value and here in the top value I am copying this same formula control I am copying the formula from here Ctrl C Ctrl V and in the top top value here we need to change this lookup value of of the vlooker formula so it is given in the result column we can simply drag this to the test name here again drag this to the test name press enter now here we have to change the column index number for male and female for Top Value for mail the column index number will be 1 2 3 4 for the mail and five six for the female so go here and in the column index number make it 4 and 6 for the first B look of the column index number will be 4 so remove this 3 and type 4 and in the second we look up remove the five and make it six now close now press enter now here you can see the Top Value is given you can now simply drag this formula down and here you can see all the top values are now given and for this PCB the range for male and female is different if I make this mail by going to the reference number and changing it here you can see it is automatically changed for the mail so the formula is now working fine here I am giving the Border because the border is removed from here I am going to home menu then going to this going to this outside border oven which will apply the outside border now the formula is completed for the reference range also now we have remaining one very important formula that is dynamically making it bold and underlined when the result goes out of the range so to do this we have to use the conditional formatting as you see here if it is within the range then The Bold and underline will remove for example if I make it within range by typing here 1 lakh 50 000. or 150 000 here in this case The Bold and underline is removed but if I put here out of range result like one four zero zero zero zero here it is automatically underlined and bold it means that the result is out of the range there is some problem in the result so we need further investigation on this so to make this result automatically bold and underline when the value goes out of the range we have to use conditional formatting which I am going to show you by selecting this go to home menu and here I am going to conditional formatting and I am going to manage rules here you can see a formula is used which is the or formula here you can see this is the formula to identify whether the value is out of the range and here to to do this I am removing the already used conditional formula from here I am deleting the rule apply okay now here you cannot see any gold and underline to apply the conditional formatting here select the range first then go to home menu then go to the conditional formatting go to the new root option and here in the new formatting rule select this user formula to determine with sale to format option then go to this formula ribbon and here write this formula equal to type or formula then open the bracket and in the first condition of the r formula type the dollar symbol first then type the D column and then 16 row this is the cost value of this result which is in the D16 sale that's right here type dollar then D16 and if this value is less than the bottom value which is the F 16 sale so here type dollar symbol then F16 now give a comma if this condition is fulfilled then it is out of range and another condition for out of range is if then type comma then dollar then D16 sale if it is greater than the Top Value which is given on the g16 so here type dollar symbol then Z16 this is the second condition for out of range now close the bracket so after writing this here then go to the format button here and then here in the format button we have to go to the font option and here we have to choose the Bold in the font style select bold and in the underline select the single underline option then click ok now click OK again and here you can see the conditional formatting is applied if the value is out of the range it is bold and underlined and if it if it is within the range then the font is regular here if I make this a normal range value like 155 000. 155 000 in this case The Bold and underline is automatically removed but if I make it out of the range like a pro 6000 in this case here you can see the bold and underline is automatically applied so in this way we use conditional formatting here in the result column to automate the test result so in this way we can create a Dynamic Hospital lab test report in Microsoft Excel and this was for today's video thank you very much for watching if you like this video please press the like button and if you have any comment or questions on this video go to comment section below and write comments also request you to share this video to your family and friends having interest in learning Microsoft Excel once again thank you very much for watching this video we will again meet on next video