Free Microsoft Excel 2013 Quick Reference

- #N/A -- The Ever Recurring Dreaded Problem
- Calculate Cell With Null Value For Use With Center Across Selection?
- Auto formatting filled cells
- Vlookup date, but leave cell empty if no date is found?
- Copy text depending on cell value
- Advanced Conditional Formatiing - Excel 2003 - Please Help!
- Solution for charting IF statements that return blank values
- Match result is sometimes #N/A
- Using Date & Time Custom Cell - Need to show blank!
- Remove error value when formula exists for empty cells
- Continuing formula when cells are empty
- Conditioal formatting problem
- Blank Spaces on Sort
- How do I use cond format formula to set cell text and color?
- "BLANK" - need to delete
- Blank cells
- Blank cells: ISBLANK = FALSE, ISTEXT = TRUE
- Cannot delete condional formatting
- Save original data after macro is run again
- IF Statement that's conditional on multiple IF Stmts in other cell

The quotes that follow are from the Help file (of Excel 2000).

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Remarks

If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.

If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.

If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP returns the #N/A value.

LOOKUP(lookup_value,lookup_vector,result_vector)

Important The values in lookup_vector must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent.

Result_vector is a range that contains only one row or column. It must be the same size as lookup_vector.

If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value.

If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP gives the #N/A error value.

LOOKUP(lookup_value,array)

If LOOKUP can't find the lookup_value, it uses the largest value in the array that is less than or equal to lookup_value.

If lookup_value is smaller than the smallest value in the first row or column (depending on the array dimensions), LOOKUP returns the #N/A error value.

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

Remarks

If HLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than lookup_value.

If lookup_value is smaller than the smallest value in the first row of table_array, HLOOKUP returns the #N/A error value.

MATCH(lookup_value,lookup_array,match_type)

Remarks

MATCH returns the position of the matched value within lookup_array, not the value itself. For example, MATCH("b",{"a","b","c"},0) returns 2, the relative position of "b" within the array {"a","b","c"}.

MATCH does not distinguish between uppercase and lowercase letters when matching text values.

If MATCH is unsuccessful in finding a match, it returns the #N/A error value.

If match_type is 0 and lookup_value is text, lookup_value can contain the wildcard characters, asterisk (*) and question mark (?). An asterisk matches any sequence of characters; a question mark matches any single character.

Although kindred, I excluded INDEX and CHOOSE, because they don't return #N/A.

What follows is a quote from a reply of mine at microsoft.public.excel.worksheet.functions to a post entitled "Conditional formatting to hide #N/A results", along with a reply to the same post by Harlan Grove:

It involves a proposal to extend the syntax of the look up functions quoted above with an (extra) optional argument.

QUOTE

A richer syntax for lookup functions would allow us to escape testing what

these functions compute (return), so avoiding the "compute twice" trap we

often see.

My proposal is simple: Add an optional slot to the sysntax of these

functions.

For VLOOKUP this surgery would give:

=VLOOKUP(lookup-value,table-array,col-index-num,{range-lookup},{return-value-when-not-available})

{} means optional; the default value for the 5th argument should be #N/A (in

view of backward compatibilty).

Examples:

=VLOOKUP(x,Table,c,0) [ return #N/A by default upon failure ]

=VLOOKUP(x,Table,c,0,"") [ return blank upon failure ]

=VLOOKUP(x,Table,c,0,0) [ return 0 upon failure ]

=VLOOKUP(c,Table,c,1) [ return #N/A by default upon failure; although in

most [cases] avoidable by approriate structuring of Table ]

=VLOOKUP(c,Table,c,1,0) [ return 0 upon failure; although in most [cases]

avoidable by approriate structuring of Table ]

PS. This is a renewal of a thread I was involved with at

http://www.mrexcel.com/archive2/messages/13513.html

What do you think? Am I overlooking something that would stand such a change

in the way?

Aladin

"Harlan Grove" wrote in message

news:xOuG8.27486$D41.1032708@bgtnsc05-news.ops.worldnet.att.net...

> Peo Sjoblom wrote...

> >One possible way would be to use a white font and the formula

> >

> >=iserror(a1)

> >

> >assuming you want to hide it in A1

> >

> >You can of course trap the error using if and isna in the formula as

well..

> >

> >=if(isna(formula),"",formula)

>

> But it involves evaluating formula twice, which can seriously slow down

> Excel when formula is long and/or complicated. There are times when VBA

> user-defined functions can actually speed up Excel.

>

> 'trap errvals and return specified value or "" instead - return v if

> 'it's not a marched errval

> 'args: v is the *scalar* value to check

> ' e is an optional list of additional args used as follows

> ' - if 1st arg after v isn't an errval, use it as the return value

> if v is a

> ' matched errval; otherwise, use "" as the return value

> ' - all remaining args are treated as errvals to match v against,

so

> if

> ' no remaining args, match all errvals

> 'note: nonerrval args after 2nd arg effectively ignored

> '

> Function errortrap(v As Variant, ParamArray e() As Variant) As Variant

> Dim i As Long, m As Long, n As Long, t As Variant

>

> errortrap = v

>

> If Not IsError(v) Then Exit Function 'return quickly when not errval

>

> n = UBound(e)

>

> If Not IsError(e(0)) Then

> m = 1

> t = e(0)

> Else

> m = 0

> t = ""

> End If

>

> If n < m Then 'no more args, so matches all errvals

> errortrap = t

> Exit Function

> End If

>

> For i = m To n 'check specified errvals

> If v = e(i) Then

> errortrap = t

> Exit Function 'can return now

> End If

> Next i

> End Function

>

>

> In this case, use as =errortrap(formula,#N/A) or

> =errortrap(formula,"",#N/A) (to be explicit). More generally, to pass

> #VALUE!, #NUM! and #DIV/0! errors but replace #N/A, #NULL!, #REF! and

#NAME?

> errors with, say, -1, use =errortrap(formula,-1,#N/A,#NULL!,#REF!,#NAME?)

.

>

> If formula is simple, this will likely slow Excel down. However, if

formula

> involves 6 levels of nested fucntion calls, this will likely speed Excel

up.

>

> >another way assuming

> >

> >=if(countif(d2:d100,b1)=0,"",vlookup(b1,d2:e100,2,0))

>

> Better than evaluating the VLOOKUP twice.

>

> >or

> >

> >=if(countif(d2:d100,b1)=0,"",index(e2:e100,match(b1,d2:d100,0)))

>

> No benefit in this case to using INDEX(.,MATCH(.,.,0)) vs

VLOOKUP(.,.,.,0).

>

UNQUOTE

As long as Microsoft has not taken up the above proposal, we should at least live by a set of reasonable rules shown in the figure below (I posted this figure a while ago at this board, but I can't give here the hyperlink, simply because I couldn't find it via the board's search facility, hence this re-post.)

Microsoft Excel - VLOOKUP.xls___Running: xl2000 : OS = Windows (32-bit) NT 5.00(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elpF6F7F8F9F10F16F17=

ABCDEFGHIJKLM1IDLast NameSalary

2010008Smith46,223.00

EXACT MATCH (0 or FALSE, both means the same thing to Excel)

3010002Miller70,000.00

VLOOKUP(lookup-value,lookup-table,table-column-to-look-in,0)

4010007Young46,223.00

What is better?

5010005Thomas44,662.00

Look UpSalary?

6010001Smith70,000.00

01000270,000.00this

7

70,000.00this, if #N/A must be avoided, because efficient

8

70,000.00inefficient

9

70,000.00inefficient & bad practice

10Commission Table

70,000.00inefficient & bad practice

11Sales%

1200.00%

APPROXIMATE MATCH (1 or TRUE or nothing, all means the same thing to Excel)

1325000.20%

VLOOKUP(lookup-value,lookup-table,table-column-to-look-in)

1450000.40%

What is better?

1560000.60%

Look UpCommission

1670000.80%

7,400.880.80%this

1780001.00%

0.80%this, if #N/A must be avoided, because efficient; seldom needed

1890001.20%

As above, no ISNA, ISERR, or ISERROR.

19

20SPECIAL NOTE: INDEX/MATCH is faster than VLOOKUP, in particular when the formula must be copied to a huge number of cells.Sheet1

To see the formula in the cells just click on the cells hyperlink or click the Name box

PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The above image was automatically generated by [HtmlMaker 2.0] If you want this FREE SOFT, click here to download

This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-05-29 16:12 ]

If the context is useful, I am trying to highlight when periods occur in a project plan. For some efforts, there may be 12 month periods, 6 month periods, etc. In the below picture, I have conditional formatting working above the text to show the bounds of each 5 month period, and I have the number of the period in the first month of that period. I would like to change the text to "Period "&#. If I do this, a good portion of the text will be truncated, but if I could center across selection, the formatting would be improved.

Any help is much appreciated!

It dosent appear that any conditional formating is set and I would like to be able to replicate this type of formatting. (I would also like to edit which colour is used)

I cant see that anything is done in VBA either but I not a whiz with this.

The colour change dosent seem to work on the whole worksheet but I cant see any ranges that have been created that it may apply to.

Is this something in the auto format or styles that I havent come across yet?

I have a spreadsheet that I am setting up to track training dates. Using Vlookup, it will return the date of training in each of many categories for each employee. Some employees will not require some of the training, so the date cell will be blank. But on my tracking sheet, if the Vlookup finds no date it fills the cell with 1/0/00, and I need to be rid of that.

I considered using conditional formatting, but I am already using that to identify training that is about to expire. If the date returned is within a month of expiring (annual training) then it will change the font to purple, and if it is within two weeks of expiring then the font will be red. So, when I tried to make any 1/0/00 entry change to a white font to make it invisible, it just doesn't work. This is the Vlookup I am using:

=VLOOKUP(J4,'[Required training by position PII presentation.xls]Employee Dates'!$A$4:$AN$16,4,FALSE)

Any assistance with this would be greatly appreciated. Thank you!

-I have a drop down box with 2 options... "Yes" and "NO" in cell A1

-When you select "NO" I want cell B2 to say "N/A" and the background turn grey

-When you select "YES" I want cell B2 to be blank and ready to type text

any ideas.. im guessing conditional formating on this one but I can't figure out the formula... Please Help

It's pretty hard to put into words as an example so I have uploaded my spreadhseet here: http://rapidshare.com/files/149674609/Book1.xls

Basically I have a couple of lookup functions that get data from the 'Data' sheet. The look up is searching based on the data in cell B4.

This then returns the results in Cells B13:B16. Cells D13:D16 I have some conditional formatting rules.

I want certain cells to appear with data and displayed with conditonal formatting based on the selection from a drop down list.

For example:

I have conditional formating on B13 so it appears blank until a selection is made in Cell B4. Then depending on what data is chosen in B4 different information will be displayed in cells B13:B16 and different formating in D13:D16.

Conditional Formating on B13 is:

Cell Value = 0 (Format - white text with no borders)

=ISERROR(B13) (Format - white text with no borders)

Conditional formating on D16 is:

=ISERROR(B13) (Format - white text with no borders)

=NOT(ISBLANK(B13)) (Format - black text with a black border)

The Scenario:

Cell B3 contains the Text "New_User" (selected from a drop down list)

Cell B3 contains the Text "Sales" (selected from a drop down list)

Cell B4 contains the Text "BS" (selected from a drop down list)

The lookup function in B13: =LOOKUP(B4,Data!A12:B15)

This would return "Sales Rep Postal Codes:" in Cell B13. B16 should then use conditional formating to place a black border around the cell with black text.

This all works fine. However, if the selection made from the drop down list in B4 is "T" then this returns a value of "0" in B13, as a result the conditional formating on B16 is black border and black text when i want it to be no border with white text.

If the selection in B4 is "BS" or "O" then I want cell B16 to have black border and black text. If no selection has been made in B4 or if "T" is selected then I want B16 to have no border with white text.

I hope somone understands what I'm saying and can help me!! I have tried everything I can think of to get it to display teh way I want.

I have uplaoded the spreadhseet on the link above because it will probably be easier for you to understand what I'm trying to do if you look at the spreadhseet, I tried to use the HTML maker but it doesn't display correctly.

Please help! Thanks in advance!

John

I know a big issue for people is that when you use an IF formula to

return a blank cell and graph it, it returns a zero value, for example

if(a1>0,a1,"") returns a blank cell that charts as a zero.

There is a solution to this problem that does not involve the more

common returning of an NA() value and conditionally formatting it out.

The solution is the use of dynamic ranges to set the chart values. It

is relatively easy and good practice as you can easily update and

reference data on other sheets. What you do is define a named range

for your data which dynamically extends with your information.

Step 1:

>From the top file menu's select Insert > Name > Define.

Step 2:

Give it a name that you will remember, that cannot start with a number.

Step 3:

Define your range using the following formula:

=$b$17:INDEX($b$17:$m$17,1,MAX(IF($b$17:$m$17"", COLUMN($b$17:$m$17)))-COLUMN($b$17)+1)

But replace $b$17 with the FIRST cell in the row, and replace $m$17

with the LAST cell in the row. (Make sure you use absolute references

($) otherwise it will not work!).

NB: If your data is in columns, not rows, you can reverse the last two

formulas in the INDEX formula and use the ROW function instead of

COLUMN. (ie, the INDEX formula uses row and then column -

INDEX(array,row_num,column_num)). Please post if you would like a ROW

formula posted.

Step 4:

In your chart, right click, select Source Data, select the series you

wish to set dynamically, and in the Values box enter ='[Sheet

name]'![Named range from Step 2].

Good luck!

my value isn't found and I'm not getting a blank but rather #N/A.

=IF(MATCH("POS*",B$83:B$782,0),"Please be advised the POS LOBs have been

converted to PPO LOBs", "")

1. Is there a fix to my formula? or

2. In the cell that results #N/A - I could set a conditional format to make

a white background and white font. However, when I try that - it's not

working either.

Cell format = #N/A doesn't do what I want.

also a formula that adds a day if certain conditions are met. When I copy

this formula down so that every cell has it, the cells on rows that haven't

been used yet show as 01/01/1900 00:00. How do I show these as blank cells -

the sheet is quite confusing otherwise!

eventhough cells are blank. I keep getting error values. I understand why

the error values, but I do not want them to display.

My formula is: =(B6-B5)/ABS(B5)

If tired:

=IF(ISERROR(B7-B6)/ABS(B6),"",(B7-B6)/ABS(B6))

and

=IF((B8-B7)/ABS(B7)=0,"")

but #DIV/0! keeps displaying.

Is there an option or conditional format that will give me the option to not

display error values? I have to create worksheets for several other formulas

and I do not want the error values to display when cells are empty or when

dividing by zero.

Thanks,

Cyrus

worksheet, even though cells are empty.

My formula is =(B6-B5)/ABS(B5)

I get the error message "#DIV/0!"

I have tried the following and the cells still inputs error value. I want

the error value not to display. I want the cell to remain blank.

=IF(ISERROR(B7-B6)/ABS(B6),"",(B7-B6)/ABS(B6))

and

=IF((B8-B7)/ABS(B7)=0,"")

Please help. I'm looking for this function for several other worksheets as

well. Is there an option or conditional format I can use to eliminate the

error values from being displayed?

Thank you!

Cyrus

cell E4.

The column shows the number within a processing cycle which need

warning traffic lights when certain days are reached so far I have set

up:

Condition 1 shows orange if the cell value is between 9 and 11.

Condition 2 shows red if the cell value is greater than or equal to

12.

My problem is that I need a 3rd condition to grey out cell E4 if a date

is entered into cell G4. I've tried all sorts but the formatting will

not work.

Firstly can someone suggest a formula for condition 3.

Secondly do I actually need formulas in conditions 1 and 2 to state

apply formating as per the criteria above , but only if G4 is blank?

any guidance would be greatly appreciated.

Thanks

--

HDV

------------------------------------------------------------------------

HDV's Profile: http://www.excelforum.com/member.php...o&userid=26299

View this thread: http://www.excelforum.com/showthread...hreadid=469842

list people who have license agreements are highlighted green. That was

easy. I need to pull out ONLY the people who have been highlighted and

sort them in another column.

The way I do this is in another column like "M" and "N" I have the

conditional formatting equation which is

=IF(A3="","",ISNUMBER(MATCH(A3,B:B,0)))

that will show TRUE or False. In the next column over I have an

equation to pull the name from the original row I am sorting.

=IF(M3="","",IF(M3=TRUE,A3,""))

Then I tie it to a "Sorting" button to paste special, values only, into

Column C and automatically sort it.

Private Sub sortuser_Click()

Dim deleter As Integer

Range("N2:N30").Copy

Range("C2:C30").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,

SkipBlanks _

:=True, Transpose:=False

Range("a1").Select

Range("C2:C30").Sort Key1:=Range("C2"), Order1:=xlAscending,

Header:=xlGuess, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,

_

DataOption1:=xlSortNormal

End Sub

but whenever it sorts there are blank spaces it copies and sorts them

to the top. but If i delete the contents, even though there isn't

anything in the cell, and sort it, it will sort will the first name in

Cell C2(where is should be) Even when I paste special with ignore

blanks it still does it, any Ideas?

--

Xiazer

------------------------------------------------------------------------

Xiazer's Profile: http://www.excelforum.com/member.php...o&userid=31581

View this thread: http://www.excelforum.com/showthread...hreadid=517234

As and example, I entered into Conditional Format for cell B1:

"Formula is", IF(A1="","Test",""). For true condition, the format is set to

fill Yellow and font color red.

I'm expecting cell B1 to display, "Test" in red text with yellow background

when A1 is blank, but it does not work. No errors are displayed. Any ideas?

has a blank area...

How can I get that to be just blank so that I can use that cell in a

conditional formatting scenario?

I want to say...

=IF(Q8="blank")

if Q8 is blank, then turn red

if Q8 has a value, then turn green

Angela

--

anjgoss

------------------------------------------------------------------------

anjgoss's Profile: http://www.excelforum.com/member.php...o&userid=26602

View this thread: http://www.excelforum.com/showthread...hreadid=398765

for e.g. cell A2 is blank. I confirm whether A2 is blank by going to

another cell and typing which gives . But when I go to

another cell and click edit-clear-all (to celar any formats etc) and type

it does NOT give a blank cell but gives (zero).

If I copy and paste a blank cell to another cell it gives blank.

the above descrepancy gives problem in creating formulas. for e.g.

A2 is 2, A3 is blank

If I type in some other cell the formula it gives 0 and

not Blank which A3 is .

is it because if the condition is true it is blank and even if it is false

it is blank.

this formula may be trivial but I have a formula which gives this type of

problem.

Elucidation please.

Depending on the values in column U, I want column V to have a value.

I am using an IF formula for this.

There is one problem. Some cells in Column U (eg U26) are blank(have

no values). However if I user U26 ="" for testing the condition, this

is resulting in FALSE. If I use =ISTEXT(U26), this is resulting in

TRUE. Similarly, ISBLANK(U26) is returning FALSE. There is no formula

in the cell. The column (U) is formatted as General. However, when I

press "Delete" in U26, ISBLANK returns TRUE and ISTEXT returns

FALSE. What could be wrong and how can I handle this?

One solution is to "Delete" the contents of the Blank cells. This is a

bit cumbersome as such blank cells are scatterred across the column. I

am posting this as the knowing the cause of the problem and the

solution would help me on future occasions also.

Thanks in advance for all the help.

Regards,

Raj

At this point, I would like to delete all conditional formatting and start

over. When I go to edit > go to > conditional formatting to highlight all or

same formatting, then go to format > conditional formatting, it is blank. It

does show my previous conditional formatting settings if I only select one

cell of the sheet that I know that it was applied to, so it is there. It is a

huge sheet, so I would like a way to identify and remove it all at once.

It almost seems that the "go to" method of highlighting is picking

additional cells that were not formatted.....

thanks in advance for any help,

Robert

Experts

I have developed a macro which imports data from another excel file.

Ex: consider I have 2 excel files A & B.

I run macro in file A today, it copies whatever data from file B.

say 20 rows have been filled in file A(starting from A5 to A25). I do some

changes on the file A which has few conditional formatting and forlumas.

I re-run the macro after 8 days in file A to get the new updated data from

file B.

The problem I am facing is >>>

"If I re-run the macro, it deletes the data from file A. It pastes

everything starting from top left cell.

When I generated macro in file A, I have selected top left cell while

recording macro.

My requirement::::::

Now I want the macro to put the data from the cell A27 onwards and also

retain the changes to the data from Cell A5 to A25.

So everytime I run the macro it should add data to the excel file from the

available blank cell and not copy over the same data.

Thnanking in anticipation

-I have an IF Stmt in Cell G97 that reads: =IF(M96=0,"",G96+1)

-M96 also has an IF Stmt that reads: =IF(G96