Most common question/answer

To some extent, that's already done... at the bottom of the linked page, there are categories for:

Excel training videos about Charts
Excel training videos about VBA
Excel training videos about Pivot Tables
Excel training videos about Data Analysis
Excel training videos about Formulas
Excel training videos about Formatting
Excel training videos about Printing
Excel training videos about Excel 2007

Click to expand...

Oaktree said:

To some extent, that's already done... at the bottom of the linked page, there are categories for:

Click to expand...

Ok. Fair enough. but I would have to say that at the bottom of the page is not very intuitive place to have a category listing. It is just a shame to have between 20-50 hours of pod-casts not easily utilized.
I will still go and look to find gems but....

Not a big deal. Thanks for pointing out the podcast listing in the first place.
Cheers!

Oaktree said:

To some extent, that's already done... at the bottom of the linked page, there are categories for:

Click to expand...

i would like to say, if you work in an office like mine, if I start watching a podcast I would probably get in trouble, although reading written word would not be a problem.

Whilst I agree that the list is a bit hard to find, I doubt it would look right at the top, but maybe where you have user CP, FAQ, Members list, etc. You could have a "New users guide" section or something like that, then a link to the podcasts and the new written guide that is hopefully going to be added

And unfortunately in my office I cannot access the videos due to our security.

I feel this may be the case with most companies

Let me add a little structure to this, while suggestions are Kool and the Gang.

jonmo1 gets a first crack at this, it's his idea.

My current thinking is to create a working document with the top-10 or so frequently asked questions (subjective - I know), and it will be a working document, here, for a while, subject to review and edits by the MVP group.

When we finalize it, could be two weeks or so, I'll make it a stand-alone post in the Q&A forum with a HOF write up, crediting all involved, except myself.

Deal?

(Note - this isn't to replace the board, podcasts, articles, etc... This is a quick hit on the most often-asked questions in a single post)

OK, here it goes.

This got a lot longer than I thought it would. Took about a whole page just for Vlookup and #N/A.

I'll post it in a few minutes, I've got to do some formatting specific to the forum software.

I have no statistical data to support what are the most commonly asked questions. But after a couple years of reading this forum, it seems fairly obvious what they are.

I've split them up into 2 categories, Formula and VBA.

I think there should be more for the VBA category, but I couldn't think of any more.


Formula:
1. How to Eliminate #N/A Errors from a Vlookup Formula.
2. Vlookup doesn't find the match but I know it's there.
3. How to perform a multiple criteria Count or Sum.
4. How to assign a different rate based on value ranges (1-10 = 10%, 11-20 = 15%, 21-30 = 20%).
5. How do I nest more than 7 IF's.
6. How to make a range dynamically adjust size according to how much data is in the range.
7. How to lookup value in a matrix with Row and Column Variables
8. Displayed Value Vs. Actual Value

VBA:
1. How to find the Last Used Row or Column #
2. How to speed up my code
3. How to apply macro to multiple sheets
4. I protected my sheets but now my macros don't work



How to Eliminate #N/A Errors from a Vlookup Formula
Bear in mind the same logic in this section can be applied to Hlookup as well as Vlookup, just transposed.

Use this example formula for this section


There are 2 reasons for a Vlookup to return #N/A.
1. The Value in A1 was not found in Column B AND FALSE was used in the 4th argument of the Formula.
2. The Vlookup formula found a match for A1 in Column B. But the resulting cell contained a formula That results in #N/A.


Resolution 1. - If you are EXPECTING #N/A errors, but just don't want to see them.
Basically, you know sometimes there won't be a match, and you just don't want to see the #N/A Errors.

There are 2 basic methods to eliminate the #N/A errors
Again, using the example formula above.

1. Test the VLOOKUP formula for the error, and return blank (or other value) if it is an error.


2. Check if the value in A1 exists in Column B, If it does, do the Vlookup, if Not, return blank.


Method 2 is Widely considered the more efficient method, While Mehtod 1 Seems a little easier to understand and Impliment. Method 1 actually must do the vlookup twice. Where Method 2 only has to look in Column B for a match, it does not have to return a value and test for #N/A.

Resolution 2. - If you think the vlookup should be returning a result, but it's giving #N/A.

That means there was no match. Make sure the data matches EXACTLY. Look at Values in BOTH A1 and in Column B. Check the spelling and Look for Extra spaces or other non visible characters.

Probably the most common problem is "Numbers Stored As Text". Sometimes Excel doesn't see numbers as numbers. This is especially true when importing or Copy / Pasting numbers from an external source. Excel will treat these as Text.

The best way to tell if that's the problem is to use the ISNUMBER function. Again, given the example Vlookup formula above, you would want to test BOTH A1 and Column B. So put these formulas anywhere.

- This one you would fill down to the bottom of the data.

Those will return TRUE if they are numbers, false if they are "Numbers stored as text"
You want them ALL to be the same - Either ALL True OR ALL False, not a mixture.
It would be preferred if they are all Numbers.

There are a few ways to resolve this problem.
1. Copy ANY blank Cell
Highlight the range that contains the "Numbers Stored As Text" - ISNUMBER returned False
Click Edit - Paste Special, Select Values, Select ADD, Click OK
2. Highlight the range that contains the "Numbers Stored As Text" - ISNUMBER returned False
Click Data - Text To Columns, Select Deliminated, Click Finish
3. You can adjust your formula to account for that, IF Column B is Either ALL Numbers Or ALL Text, not a mix of both.
If A1 is a Number and Column B is "Numbers Stored As Text"
If A1 is "Number Stored as Text" and Column B is Numbers



Vlookup doesn't find the match but I know it's there.

See Resolution 2 from previous section "How to Eliminate #N/A Errors from a Vlookup Formula"


How to perform a multiple criteria Count or Sum.

Very often I see posts that say I have this Sumif formula that works great
It Sums column C where Column A = "SomeWord"
But I want to add another criteria, to Sum Column C where Column A = "SomeWord" and Column B = "AnotherWord"

SUMPRODUCT is a VERY powerfull function that you can use to accomplish this.

Here is a basic structure using the example of Column A = "SomeWord" and Column B = "AnotherWord"

That will Sum Column C where Column A = "SomeWord" AND Column B = "AnotherWord"

It is a very simple formula and works extremely well.

Each section defined by --(range=crieria) is a logical Question with a True or False Answer.
The -- converts the True/False answer to 1/0. TRUE = 1, FALSE = 0
The 3rd section C1:C100 is not a question, it only contains a numerical Value

It reads the formula 1 row at a time, and answers each True/False Question.
So take Row 1 - Is A1 = "Someword" and IS B1 = "AnotherWord"
Each question has a true false ansewr, converted to 1 or 0.
It then multiplies the 3 sections together, The 2 Answers And the Numerical Value in Column C.
The result of that multiplication is the Value for Row 1.

Then moves on to the next row
Is A2 = "SomeWord" AND "B2 = "AnotherWord"
It then multiples the 3 sections together, The 2 Answers And the Numerical Value in Column C.
The result of that multiplication is the Value for Row 2.

Each row has only 4 possible combinations of Answers (since only 2 criteria)
Both True = 1*1*(Value of Column C)
Both FALSE = 0*0*(Value of Column C)
True and False = 1*0*(Value of Column C)
False and True = 0*1*(Value of Column C)

Each row has only 2 possible values, 1*(Value of Column C) or 0*(Value of Column C)

Only if both criteria are TRUE, will the result be 1*(Value of Column C),
If EITHER (or BOTH) question is FALSE, the resulting value will be 0*(Value of Column C).

After all questions in all rows are answered and multiplied and values assigned to the row,
it then Sums the Values of each row.

So given a small example
A1 = SomeWord
B1 = AnotherWord
C1 = 25
A2 = SomeWord
B2 = BlahBlah
C2 = 40

This results in (1*1*25) + (1*0*40)
Breaks down to (25) + (0)
Result is 25, only 1 row (row 1) met the criteria of Col A = "SomeWord" and Col B= "AnotherWord"

IMPORTANT RULES
The Ranges CANNOT be entire column references like A:A, you must use a Finite range like A1:A100
The Ranges MUST be the Same Size
You can use Cell references for the variables like
D1 = SomeWord
E1 = AnotherWord

You can make it a conditional Count by simply removing the SumRange
That will Count Rows where Column A = "SomeWord" AND Column B = "AnotherWord"



How to assign a different rate based on value ranges (1-10 = 10%, 11-20 = 15%, 21-30 = 20%).

A common example of this is for Sales Commissions
If my Sales for the month were:
0 - 1000 I get 5%
1001 to 1500 I get 7%
1501 to 2000 I get 9%
2001 Or More I get 10%

So say A1 is your total Sales Amount

Make sure the Values in {0,1001,1501,2001} are sorted Ascending Left to Right.
It looks for the largest Number in {0,1001,1501,2001} That is Less than or Equal To A1
It Returns the Corresponding value from {.05,.07,.09,.1}
Then multiplies A1 by that #

Another way to do it is to use a lookup table on a sheet.
That way you don't have to hard code the values in the formula, and it's easier to adjust/maintain.

So you could put The LOW END Dollar Amounts in Column B and the corresponding % in Column C.
Sort the table ASCENDING by column B


It basically looks for the Largest Number in Column B that is Less Than or Equal To A1
And returns the corresponding value from Column C
Then multiplies A1 by that #

That's basically it, pretty straight forward.



How do I nest more than 7 IF's
There's really no straight answer to that. But it is almost ALWAYs Possible to find a different approach to your goal without Nesting IFs. But if you must, Heres a way to do it.

Split the formula in two.

In one cell (Say A1), nest your first 7 ifs, and make it return "" if None are true.
In a Second Cell (Say B1), Test A1 for blank and Continue the nest.

so for example
A1: B1:
But I must strongly recommend searching for alternate methods...
Example

If A1 = "Hello" then "There"
If A1 = "What" then "Ever"
If A1 = "Get" then "Over It"
If A1 = "Barney" then "Rubble"
If A1 = "Fred" then "Flintstone"

this can be done with a nested If, sure. But it's much easier and not limited to 7 to do it like this.
Similar to the last section of assinging a rate to a commission..

The first set of values need to be sorted Alphanumerically from left to right.

It can also be done with Lookup Table as in previous section
Column B is the Criteria, Column C is the Results - Sorted Ascending by column B


or more exact to use Vlookup


Using the Vlookup, the data would not need to be sorted.



How to make a range dynamically adjust size according to how much data is in the range.

You have a bunch of formulas referring to 100's of rows of data.
But that data is updated frequently, and the Length (or width) of the data is not always the same.
Sometimes it's 500 rows, sometimes it's 750.

You could write your formulas to use the whole column A:A, or a very large part of it to cover the most it would ever be.

But, that is a good way to decrease the performance of your sheet. The larger the range you refer to, the slower the formula will be.

A solution is to use a Dynamic Range. One that automatically adjusts it's Hight and/or Width based on the size of the data on the sheet.

We'll use an example of Data that changes size in ROWS, not columns. But the same logic can be applied the other way around...It can also be used for Both (hight AND width can vary)

You first need to decide on a specific column to use that will determine how far down the data goes.
And you'll want to decide which Row # the data STARTS on, that is usually pretty consistent.

So for this example, we'll say the data starts in Row 1, and col A will determine how far down the data goes.

This formula will return a range object starting in A1 going down to the last Cell in column A that is occupied.
Provided there are no blanks within the range of column A.


So you could write

You can then use a Named Range for that range.

Click Insert - Name - Define
Type a Name like MyRange
In the Refers To Box, put

It is important to use Absolute Refs in the Named Range, things can get wierd if you don't..
Now you can use the formula


If there ARE blanks in the range, it get's a little more complicated. But I've found this to work well for me.


The $A$1:$A$65535 part cannot be an entire column ref like A:A, as it is an array formula. I'm not sure why this works in a Named Range without CTRL + SHIFT + ENTER, but it does.
If you wanted to use that in a Cell formula, instead of named range, you would have to enter it with
CTRL + SHIFT + ENTER
like

Now these can be transposed to Row Ranges instead of Column Ranges...

And

Now, to do a 2 Dimensional Dynamic Range (multiple Columns AND multiple Rows), YOu have to use OFFSET..
The basic Structure is


We'll again use column A to determine # of Rows, And row 1 To determine Number of Columns.

This will give a range from A1 to the Last Column and Last Row.


That also again, depends on NO blanks in Column A or Row 1
If there ARE blanks, you can adapt the Match part from above...

With each of these formulas, when using them as Named Ranges.
Be careful where you put your formula =SUM(MyRange). If you put that formula in a cell that is within one of the ranges referred to by the named range formula (Column A or Row 1), you will get a Circular Reference Error.



How to lookup value in a matrix with Row and Column Variables

Say you have a matrix of City to City Travel Distances.
You see these type of matrix's on Maps and in Atlas' All the time

A2:A10 = List of Cities
B1:K1 = Another list of Cities
And in B2:K10 you have a table of corresponding distances between the city in column A, and the City in Row 1.

Now you want to lookup the distance by entering 2 cities in other cells..

You basically want the cell in the intersection of the Row and Column of the 2 cities.

This is pretty common, and fairly simple with a combination of Index and Match.

So let's say you enter the cities you want to cross reference in M1 and M2.

M1 = Houston
M2 = New York

So now you want to find the distance from Houston to New York

Now Say
In A7 is Houston
In G1 is New york

so G7 is the value you want to return, The intersection of the column and row of the 2 cities.

You're formula would be




Displayed Value Vs. Actual Value
This is more like a Pet Peave of mine than it is a common question. However, this directly relates to the resolution of many questions/problems on the forum.

When you format a cell to display a value in a certain way, That only changes the APPEARANCE of the Value in the Cell. The ACUTAL Value of the cell remains unchanged.

In other words, if the Value that shows in the Cell appears differently than the value appears in the FORMULA Bar.

That's the key, Appearance in the Cell is just looking at the cell. Value in the FORMULA bar is the ACTUAL value.

This is probably (IMHO) the most important and commonly overlooked rule in Excel. Formulas, VBA code and other functions like Pivot tables use the ACTUAL value of the cell. They IGNORE the Formatted Display of the
Cell.

So for example Say A1's ACTUAL value is 3.46, but you've used the format to only show 1 decimal place.

So the Cell shows 3.5 . This can effect other formulas.

Say you want to do a vlookup
It won't find A1, because A1 ACTUALLY = 3.46, even though it shows 3.5 in the Cell.



How to find the Last Used Row or Column #

This is a Very common obstacle, and easy to overcome.
Pick a column to determine the last used Row #. Say A for my example.


This simulates going to Cell A65536, and pressing CTRL + UP
You now have a variable (LR) that is equal to the last used row # of column A

You can then use that in Range references like this


If you don't know which column will determine the last used row, I.E., Sometimes column B has the most data,
sometimes Column F has the most data...


And the same can be done for the Last Used Column




How to speed up my code

The most common method to speed up code is to turn off Application Calculation, Events and ScreenUpdating.

Calculation - Formulas will recalculate everytime your macro changes something on the sheet.
If it Inserts or Deletes Rows, or Changes certain Values, formulas then recalculate. Slowing down your code.

You can turn it off with

and make sure to turn it back on at the end of your code with

Events - Worksheet and Workbook Events can trigger and run when your macro makes changes to the sheets.

You can turn that off as well like

Make sure to reset to True when done.

ScreenUpdating - When your macro makes changes to a cell - or selects a different sheet, that is updated on screen in the User Interface. So the user can see it happening. That really slows things down. You can turn
that off as well

reset to true when done.

You can do all those things at once like this


And reverse it at the end.

Use these methods at your own discretion. Depending on the situation, it may not be appropriate to turn calculation or events off. If your macro depends on the values of formulas, and your macro changes cells that those formulas refer to, then those formulas won't update and your macro will have the wrong value.

You could get around that by turning off calculation, and then just using the Calculate Command at key points during your macro to make sure you have correct values. Better to calculate just once, then every time something changes.


Now, the Biggest and Most Common reason for slow code is using Select and Activate.

Most of the time code that uses select and activate comes from using the Macro Recorder. The Macro recorder is Fine, it's a great tool for learning syntax for a certain task.

Once you get the resulting code from the recorder, you can then tweak it to remove the select and activate code.

99% of the time it is NOT necessary to select or activate an object (range, sheet, textbox, etc) to manipulate it.

Take this small snippet of code that uses Select

That can all be done in 2 lines
There are 2 very basic Guidelines to converting this.

1. Add Sheets("SheetName"). before any Range or Cells reference. Just look at whatever sheet was last selected prior to the line you're modifying, that is the sheet you add to the range reference. This tells the code which sheet you are wanting to work with.

2. Anywhere you see the word Selection, you can replace that with whatever was previously selected. Paying attention to the last sheet that was selected, and adding that sheet before the range or cells reference.

It can be a very tedious (but not difficult) process to convert code that uses select to code that does not use select.
But it is very well worth it.


How to apply macro to multiple sheets

OK, You've got a macro that works great on a specific sheet, but you want it to run on several sheets.

Either All Sheets, All Except a Few specific, or Only a specific few.

The first thing you have to do, is make sure your code doesn't SELECT sheets or Ranges. If it does, follow the instructions of the previous section (How to speed up my code) to eliminate Select.

Now you can use this simple loop

And anywhere in your code that references a sheet name, like Sheets("Sheet1")., replace that with ws.

So for example, you have this small code that works on a specific sheet

that sets the Interior Color of A1:E10 to red on Sheet1.
to make that do it for all sheets


Now, that does ALL sheets, to make it do only specific sheets you specify


to make it do ALL Sheets EXCEPT a specific few you specify, it's exactly the same but change this line
If Not IsError(X) Then
to
If IsError(X) Then


I protected my sheets but now my macros don't work

This is very easy. You have to know the password (of coarse, Duh...)

You simply add a line of code at the beginning of your macro to UNprotect the sheet, then another line at the end to ReProtect It.

Take your time, mate; this isn't due today. Remember, this is a serious endeavour. One friendly request before I get into the specific Excel-related issues:

Run all of your VBA code through the VBE and please get the capitalization right, and tripple-check your writing, I noticed you had a slight caps issue.

I apologize if I appear to be nit-picking, and I appreciate your effort. However, if this is going to be featured, it needs to be extremely professional, everyone's reputation is on the line, here.

I protected my sheets but now my macros don't work

Could you use UserInterFaceOnly:=True?



This was taken from http://www.ozgrid.com/VBA/excel-macro-protected-sheet.htm