Camera Tool Function In Excel 2010

With Excel 2010 Camera tool, you will be able to take a snapshot of datasheet portion. This enables you to place a live picture anywhere in the worksheet, you can apply different styles, colors, and designs over it. Since it is live image so it syncs with the datasheet range on run time.

Launch Excel 2010, In order to use this feature, you need to make Camera button appear on the Excel Quick Access Toolbar, because it is not present by default. Head over to Quick Access Toolbar and from small drop down button, click More Commands.

You will reach Excel Options dialog box. Hit drop-down button from right pane present beneath Choose commands. Click All Commands to show all commands which Excel contains. Now scroll-down the list and find Camera command. Select it and click Add (>>) to show it on Quick Access toolbar pane. Once added, hit OK.

You will Camera button on the quick access toolbar.

Now for using this feature, select the portion of the datasheet for which you want to take a snapshot of, and click Camera button.

Now you are ready to take a snap, just click on the location where you want to show the image.

Now when you will select this image Picture Tools tab will appear, from where you can apply different styles and designs.

So apply design and styles which best suites your datasheet image. You can apply shadowing, 3d effects, rotation, colors etc.

Most importantly, it is not a mere image of the table, but it syncs with the table values on run-time, means on changing any value in the datasheet, reflection can be seen in the image. As you can see on changing value and color of the cell, changes appear in the image.

Read more

MS Access 2010 DATE Functions

Access encompass variety of powerful tools, that facilitates user to use dates and date ranges to be specified in a certain criteria. To get by with the complex logic in your database that contains dates, then DATE functions could be very useful for you. This post will elaborate how to use simple DATE functions.

Launch Access 2010, open a table in which you want to apply Date function. For instance, we have included a table, containing record of account holders in several fields; Name, Account, Cost, Date, and Cancellation as shown in the screenshot below.

Now we need to create a query in which we will apply Date function to check the Due Date, Difference of days etc. for this head over to Create tab, and click Query Design.

You will reach a query tab, a window will pop-up asking you to add desired table, choose the desired table, and click Add.

Selected Table window will appear, showing it’s fields, drag each field to the bottom pane and enable check boxes, as shown in the screenshot below.

Now we will add a new field in this query, suppose we want to create a fields which shows the due date. For adding a Due Date field we assume that it would be 7 days after you sign-up for an account.

So we will write it as

Due: [Date]+7

Note: When you are about to work on dates, make sure that it’s data type must be set as Date/Time, for performing actions and applying functions on dates. It happens sometime that field contain dates, has a data type of Text or Memo, which also supports inclusion of dates, as writing dates would not invoke any conflict with Text or Memo data type.

We will write the formula in the last cell adjacent to Cancellation cell as shown in the screenshot below.

Click Run, and you will notice that it will add up 7 days to the date of commencement and show us the result in newly created field Due, as shown in the screenshot below.

Now if want to show the difference between the date of commencement and cancellation date we will simply write;

Difference: [Cancellation]-[Date]

Upon clicking Run, it will show the difference between the dates as numeric values, as shown in the screenshot below.

Now if we want to extract the month and year from any field, containing dates, we will simple write following lines;

Month: Month([Table name])

Year: Year([Table name])

It will extract year and month from the Date field, as shown in the screenshot below.

We have applied some basic Date functions over the table, there are lot of other ways in which you can use them according to the situation, it was the simple example to get you started with Date function, but when you need to compare multiple fields containing dates, then it would become complex.

Read more

HLOOKUP Function In Excel 2010

Excel HLOOKUP function can be used when you are dealing with huge datasheet and need to look up the values of the specific record. HLOOKUP stands for Horizontal lookup, used to find specific data from the datasheet. Unlike VLOOKUP function, it looks for the values horizontally which enables the user to extract a complete record. This post elaborates how & where you can use HLOOKUP function.

Launch Excel 2010, and open a datasheet on which you want to apply HLOOKUP function.

For instance, we have included student grading sheet containing courses name fields and a field containing name of the students as shown in the screenshot below.

Now we want to apply HLOOKUP function, for this we will start off with adding a new row in which we will be looking up for the data of specific student. Suppose we are dealing with a gigantic datasheet containing loads of student records, in this scenario if we want to look up for the record of student Jack, then we will add the row label by the name of student and start pulling the marks he secured from each each Course column; e.g. Software Engg, Se II etc.

Now we will be writing formula adjacent to Jack cell, Unlike VLOOKUP function, it looks for data horizontally, The syntax of HLOOKUP function is;

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup] )

The first parameter of the formula lookup_value defines the value of the number which we will be looking in the specified row. We need to lookup the mark in the newly created cell. The next parameter, table_array defines the table array we will be referring to in our case it will be portion of datasheet containing courses labels, with corresponding data (courses with marks secured ). row_index_num defines, which row we want to extract values to show, in our case it is the second row that contain respective course marks. [range_lookup] lets you to choose an option either TRUE(approximately matching of values) or FALSE (Exact matching of values).

We will write this function in the cell adjacent to Jack cell, it will go like this;

=HLOOKUP(B1,$B$1:$H$11,2,FALSE)

In the formula parameters, B1 is cell of column Software Engg which contain marks secured by the student Jack, B1:H11 is the array of the data containing all courses labels and their respective values (use absolute referencing with $ sign), 2 in the formula means that values from second row will appear, and FALSE defines that we need exact match.

As you can see in the screen shot below that, the formula will yield 18 the marks secured by Jack in Software Engg course (at B1 location)

Now if you want to know marks secured by Jack in each course, just drag the plus sign across the row, as shown in the screenshot below.

There is another interesting way in which HLOOKUP function can be used, in this we will be creating exclusive table for student jack and looking up for the marks, by specifying course name, as shown in the screenshot below.

The whole formula is the same except the first parameter (A44), actually it is referring to the cell in which will write the course name. Now when we will specify course name, corresponding marks value will automatically appear.

Now apply it over the table and make an exclusive one for student Jack.

Read more