Multilevel Drop Down Lists

1

Cascading drop downs are really useful when you have categories and sub-categories that you want to be able to specify within an Excel worksheet.  In the example shown in the video our first drop down allows us to specify a location – this could be an office location and the second drop down an employee.  If I select “London” in the first drop down I only want the London employees to appear in the second – I don’t want the full list of employees.  So in effect the first drop down needs to filter the second.

Just to walk you through the example shown in the video. Start off in the first sheet of the workbook by typing in two column headings Location in A1 and Employees in A2. In the second sheet, type the following data – or make up your own locations and names.

Location	London	Birmingham	Bristol
London	John	Fred	Harry
Birmingham	Jack	Phillip	Paul
Bristol	Mark	Kate	Tim
	Andy	Sarah
		Joan

Please make sure that the location names in cells A2 – A5 are typed in exactly the same as the location headings in B1 to D1.

2

Next select cells B1 through to D6.  We are going need to name these columns.  Naming columns makes it much easier to refer to them later on in the task. To name the columns click the Formulas tab on the Ribbon and in the Defined Names tab click the Create from Selection button.  In the resulting dialog box make sure Top Row is the only thing ticked: we are saying name these columns based on the top row values – ie London, Birmingham and Bristol.

3

Now move back to the first sheet.  Click in cell A2, under your Location heading.  Next select the Data tab on the Ribbon and in the the Data Tools group, click the Data Validation button.  Data validation allows you to specify valid entries for a cell.  One way of restricting users to entering ‘valid’ values is to provide a drop down list.  In the Allow menu select List.  In the Source box specify the range of cells in the your second sheet that contains your offices – in my example this would be A2:A5; so the range would be =Sheet2!$A$2:$A$4 (note the equals sign before the range – this is necessary!!!).  Click OK to confirm.

4

You have your first drop down list, now on to the second.  Click in cell B2 in the first sheet under your heading Employees.   Now select the Data tab on the Ribbon and in the the Data Tools group, click the Data Validation button.  In the Allow menu select List. In the source box we are going to use the INDIRECT function to specify that we only want to show values from the relevant named list in sheet 2 – so either the London, Birmingham or Bristol list.

INDIRECT returns the reference specified by a text string: the reference we want to return is the relevant location column and the text string is specified by the value selected in cell A2 (our first drop down). This only works because the values in our drop down list in cell A2 correspond exactly to the named references in sheet 2 that we created earlier on.  The formula to use is =INDIRECT($A$2).  

If you haven’t yet selected in a location from the drop down in A1, you will get the following warning.

Click on Yes to continue.  All this is saying is that there isn’t yet a value in A1 that the INDIRECT function can calculate on.  Your drop downs will work once you have made your initial selection in A1.

If the warning doesn’t appear, just click OK to confirm and you will have your cascading drop downs.

EMPLOY AUTOFILL (OFFICE)

1. Click the Sales tab.
   
2. In cell A1, type:
   Frog Sales
   Make this text Arial, 12 point, bold:
   

3. In cell A3, type:
   January
4. In cell B3, type:
   February
5. In cell C3, type:
   March
   
6. Highlight cells A3 through C3.
   
   7. Place the cursor on the Fill Handle.
      
      It should turn into a black cross:
      
   8. Drag the cursor to cell L3.
      
      9. Release the mouse button.
         The months should fill in consecutively:
      10. In cell A4, type:
          10
      11. In cell B4, type:
          20
      12. Highlight cells A4 and B4, then place the cursor on the Fill Handle.
          
          13. Drag it to cell L4.
              The numbers should fill in, in increments of 10:
              
          14. In cell N3, type:
              Total
              
          15. Click cell N4, then click the  icon.
              
              16. Drag over all the cells with numbers in them.
                  They should be surrounded by "marching ants":
                  
              17. Press ENTER.
                  The yearly total of 780 frogs sold should appear:
                  
              18. Save the workbook.
              
              

How To do Wudu

1.          First, make the Niyyah (intention) in your heart that this act of Wudu is for the purpose of preparing for Salah, and say: "Bismillah" (in the name of Allah).

2.          Wash both hands up to the wrists (starting with the right hand) three times, making sure that water has reached between the fingers.

While washing the hands:

Alla hummaj alni minat tawwabina waj alni minal mutatah hirin.

3.          Take water with your right hand, put it into your mouth and rinse thoroughly three times.

While rinsing the mouth one should say:

Alla Humma laq qini hujjati yawma alqaka wa atliq lisani bizikrika.

4.  Take water with your right hand, splash it into your nose and blow it out three times. (Use the left hand if necessary to help blow it out).

While washing the nose one should say:

Alla humma la tuharrim 'alaya rihal jannati waj 'alni mim man yashummu riha ha wa rawha ha wa tiba ha.

5.          Wash your whole face three times repeatedly. (The whole face includes: the right ear to the left ear, and the forehead to the bottom of the chin).

While washing the face, one should say:

Alla humma bayyiz wajhi yawma taswaddufihil wujuh wala tusawwid waj hi yawma tabyazzul wujuh.

6.     Wash the right arm thoroughly from wrist to elbow three times, and make sure that no part of the arm has been left unwashed.  Repeat with the left arm.

While pouring water over the right elbow, one should say:

Alla humma a'tini kitabi bi yamini wal khulda fil jinani bi yasari wa hasibni hisaban yasira.

While pouring water over the left elbow, one should say:

Alla humma la tutini kitabi bishimali wala min wara'i zahri wala taj alha maghlu latan ila unuqi wa a'uzu bika min muqat ta'atin niran.

7.          Move the palms of the wet hands lightly over the head, starting from the top of the forehead to the back of the head, and passing both hands over the back of the head to the neck, and then bringing them back to the forehead.

While performing the wiping of the head, one should say:

Alla humma ghashshini bi rahmatika wa barakatika wa 'afwika.

8.          With the same water, rub the grooves and holes of both ears with the wet index fingers, while also passing the wet thumbs behind the ears from the bottom upward.

9.          Finally, wash both feet to the ankles three times, starting with the right foot.  Make sure that water has reached between the toes and covered the rest of the foot.

While performing the wiping of the feet, one should say:

Alla humma thabbitni alas sirati yawma tazillu fihil aqdam. Waj'al sa'yi fi ma yurzika 'anni ya zal jalali wal ikram.

At the end of the above steps recite: "Ashhadu alla ilaha illallahu, wa ash-hadu anna muhammadan abduhu wa rasuluhu.” by pointing your finger to sky.

"أشهد أن لا إ له إلا الله وحده لا شريك له،

 و أشهد أن محمدا عبده و رسوله"

This means:

“I bear witness that there is no deity worthy of worship except Allah alone, and I bear witness that Muhammad is His servant and His messenger.”

General Shortcuts for any program

* Windows key + E = Explorer

* Windows key + Break = System properties

* Windows key + F = Search

* Windows key + D = Hide/Display all windows

* Windows key + R = Run command pop-ups

* ALT + Tab = Switch between windows

* ALT, Space, X = Maximize window

* CTRL + Shift + Esc = Task Manager

* CTRL + C = Copy

* CTRL + X = Cut

* CTRL + V = Paste

* CTRL + Z   = Undo an action

* CTRL + Y  = Redo an action

* CTRL + A  =Select all items in a document or window

* F1 = Display Help

* F2 = Rename the selected item

* F3 = Search for a file or folder

* F4 = Display the Address bar list in Windows Explorer

* F5 = Refresh the active window

* F6 = Cycle through screen elements in a window or on the desktop

* F10 = Activate the menu bar in the active program

Energy consumption calculator

Now-a-days the amount of energy consumed and the cost per unit are becoming more than

expected. In order to avoid use of more energy a efficient calculator was designed. Using this you

can calculate the total power consumed and also your monthly bill..

Although it states the average assumption for all states more or less your bill bill be around the value

it calculated.

BREAD UPMA

Bread Upma recipe

Recipe Cuisine: Indian  |  Recipe Category:Breakfast
Prep Time: 5 mins    |  Cook time: 15 mins    |  Serves: 2

Ingredients

Bread slice	4
Onion	1
Tomato	1
Green Chilli	2
Sambar powder	3/4 tsp
Turmeric powder	1/8 tsp
Ginger garlic paste (optional)	1/2 tsp
Salt	3/4 tsp
Curry Leaves	1 sprig
Coriander leaves	To garnish
Ghee	1 tsp

To temper

Oil	1 tblsp
Mustard	3/4 tsp
Urad dal	1 tsp
Channa dal	1 tsp
Cashew nuts(optional)	3-4

Method:

1. Cut the bread slices into bite size squares. Slice the onion,green chillies and chop tomato.
2. Heat a pan with oil and season with the items given under the ‘To Temper’ table(I omitted cashews).Fry onions till transparent and add the ginger garlic paste and fry for a minute.

3. Add tomatoes,salt and fry till tomatoes turn soft.Add turmeric and sambhar powder and stir well. Sprinkle some water.

4. Add the bread pieces and mix/toss well.Add ghee,keep in low flame and cook for two minutes to see golden brown spots here and there in the bread pieces.

Notes:

• You can omit tomatoes and add tomato sauce/ketchup.
• Cashew,ghee are optional to enhance the taste.
• You can wheat bread (or multigrain/whole meal bread)instead of white bread.
• Sprinkle a tsp of lemon juice at the end if you want an extra zing.

Garnish with the coriander leaves,serve hot with tomato ketchup or onion raita. 

ALOO BHINDI FRY

Aloo Bhindi fry recipe

Prep Time: 10 mins    |  Cook time: 10 mins    |  Serves: 3

Ingredients

Potatoes	1
Ladies finger	20
Onion	1
Red chilli powder	3/4 tsp
Amchoor powder	1/4 tsp
Jeera	1 tsp
Oil	2 tblsp
Salt	As needed
Coriander leaves	2 tblsp

Method

1. Clean and chop ladies finger into bite size pieces. In a microwavable bowl, add the ladies finger and toss it with 2 tsp of oil. Microwave high for 2-3 minutes with stirring in between. This will help reduce the stickiness.
   
2. Chop onions finely, cube the potatoes. Heat kadai with remaining oil and splutter jeera. Add finely chopped onion and fry till transparent. Add cubed potatoes and sprinkle some salt for it. Cook for 3-4 minutes.
   
3. Add the microwaved ladies finger. Add more oil if needed and check for salt. Cook covered for 5 minutes.
   
4. Add red chilli powder and amchoor powder. Give a stir for a minute. Finally add chopped coriander leaves.
   
    

Notes

• Instead of microwaving the ladies finger, you can fry it in oil stove top too. Microwaving is to reduce oil consumption.
• You can add Dhaniya powder along with red chilli powder.

We had it with rice and dal. Pair it with any rice variety. Or even you can eat it with phulka/ roti.