Excel 2007 Confusion: What Are Your Biggest Questions and How Can I Help?

Dear My Fans,

Simplify your Excel 2007.

What are all the doubts in Excel? Please note them here, and I will provide you with the exact solution.

Regards,
JA

The Excel VLOOKUP function is rather useful. If you look below, you can see it in action. With the file using Internet Explorer.

In this example, we have a table of sales budget figures for each month (in green). By changing the month number in cell D2 (in red), it gives the corresponding sales figure for the month in cell D6 (in blue).

To do this, all that is required is the VLOOKUP formula as follows:
=VLOOKUP(D2, F3:G14, 2, FALSE)

So, what does that actually do? The function looks up the value in D2 and searches for it in the first column in cells F3:G14. It then returns the corresponding value in the second column. In our example, the second column represents the sales budget column. Adding "FALSE" just ensures that Excel looks for exact matches.

Regards,
JA

Dear Joshua,

I want to know about the password for each individual cell in an Excel sheet (not the whole document/sheet or a group of cells in a row/column for protection). That means in an Excel sheet, I want some cells to be locked and some cells to be open. Is this option available in Excel, and how can I achieve it? Also, could you please explain what macros are and how to use them?

Hi,

1. Select All or press Ctrl+A.
2. Press Ctrl+1 to go to Protection, remove the checkmark from Lock and Hidden.
3. Select the cell you want to protect.
4. Again, press Ctrl+1, go to Protection, then mark and check the boxes for Lock and Hidden.
5. Go to Tools, choose Protect Sheet, enter a password if desired, and click OK.
Now, you will see that the selected cells are protected.

Regards,
Satish G.

In Excel, the DCount function returns the number of cells in a column or database that contain numbers and meet a given criteria. The syntax for the DCount function is: DCount( range, field, criteria )

DAVERAGE(Database, field, criteria)

DCOUNT(Database, field, criteria)

DCOUNTA(Database, field, criteria)

DGET(Database, field, criteria)

DMAX(Database, field, criteria)

DMIN(Database, field, criteria)

DSUM(Database, field, criteria)

Example:

Note: - Column G shows how the formula has been written in column F.

From the example above, you can see the most common database worksheet functions that are all referring to the database above (rows 5 to 15) but mainly extracting information from the "Rounds" column. The following explains the syntax of the formula.

Database = all the cells (the range) that are used to make up the list of related data. In the example above, each formula references the range B5 to K15 because that is where all the data is. The first row of the database, B5, is the label for each column and is important for the formula to work, as you will see in the criteria explanation.

Field = the column that is used in the formula. As mentioned above, each formula is using the "Rounds" field to provide information. This word is in F1, F5, and can be written in the formula within quotations. For example, DMIN could have been written as '=DMIN(B5:K15, "Rounds", B1:K2)' instead of the reference to F1.

Criteria = the range of cells that contains the conditions specified. I left each cell blank but could have made a condition within the cells B1 to K2. You can use any range for the criteria, as long as it contains at least one column label (this example used "Rounds") and at least one cell below the column label for specifying a condition for the column.

The following is a brief description of each of the Worksheet Functions:

- DAVERAGE: finds the average of all numeric amounts in the selected field that meet the specified criteria.
- DCOUNT: counts all numeric amounts in the selected field that meet the specified criteria.
- DCOUNTA: counts all items in the selected field that meet the specified criteria and are not blank cells.
- DGET: finds the item in the selected field that meets the specified criteria. If two items are in the field, then the #NUM error will be the answer.
- DMAX: finds the maximum of all numeric amounts in the selected field that meet the specified criteria.
- DMIN: finds the minimum of all numeric amounts in the selected field that meet the specified criteria.
- DSUM: finds the total of all numeric amounts in the selected field that meet the specified criteria.

Regards,

JA