Free Microsoft Excel 2013 Quick Reference

- WANT TO MAKE A FORMULA RETURN A BLANK CELL (VS 0)
- Blank cell vs $0.00
- Blank Cell vs Cell equal to Zero (0)
- Zero value vs blank cells Userform VBA
- Blank vs. 0 when plotting a dated timescale graph
- How Do You Hide a #DIVO! Return in a cell
- 0 entry vs blank cell
- Stored vs. Displayed Values
- Urgent help needed! Vlookup vs match
- Offset(counter, 0)
- Less than vs greater than
- Macro in excel 2000 vs excel 2002
- Need the result to be blank vs #N/A when false
- Area charts and blank cells
- Sumproduct Vs Pivot Table
- Cell based Formula VS. BASIC Coding.
- Zero vs blank in cell
- Formulas in charts?
- Data Validation Drop Down with variable input
- Adv Filter criteria, true false, vs 1 and 0

of a formula

EXAMPLES:

IF($D3>1,D3,"") HOPING "" COULD BE INTERPRETED AS EMPTY STRING (BLANK

IF($D3>1,D3,A2) WHERE A2 IS A BLANK CELL

NO MATTER WHAT I TRY, XL AWAYS RETURNS 0'S ... THIS DOESN'T WORK FOR MY

APPLICATION. I CAN'T SEEM TO FIND A WAY TO HAVE A FORMULA RETURN A BLANK

CELL.

ANY HELP IS GREATLY APPRECIATED !!!

THANKS ... GEORGE ZEIGLER, SAN DIEGO.

When I put 100% in column I, i just get a blank in my cell, and I would like it to say $0.00. the only time i want it blank is if i am not using that row for anything.

G is my unit cost, and I is the discount i am giving them.

hope this makes sense

cell and a cell that's equal to 0?

Here's my formula:

=IF('2005 Sales'!O12>0,'2005 Sales'!O12,"")

I wanted to change this to if O12 is greater than or equal to 0, then

return O12, but I want it to return ("") if the contents of O12 is blank.

Is there a way to do this? ...

TIA!

LavaDude

Private Sub CommandButton1_Click()

Select Case CBPerformance.Value

Case "Exceeds"

ActiveCell.Offset(0, 1).Value = 20

Case "Meets"

ActiveCell.Offset(0, 1).Value = 10

Case "Below"

ActiveCell.Offset(0, 1).Value = 0

Case Else

End Select

End Sub

I have some additional code where I want to convert the numeric values back into text to display in the combobox. This seems to work fine except when I encounter a blank cell. When the cell is blank the combobox is displaying the text associated with the zero value "Below". How do I force the combobox on the userform to display as blank when encountering a blank cell? Any help would be greatly appreciated.

Private Sub CBName_Change()

Range("b8").Select

Do Until ActiveCell.Text = CBName.Text Or ActiveCell.Value = vbNullString

ActiveCell.Offset(1, 0).Select

Loop

Select Case ActiveCell.Offset(0, 1).Value

Case 20

CBPerformance.Value = "Exceeds"

Case 10

CBPerformance.Value = "Meets"

Case 0

CBPerformance.Value = "Below"

Case Else

End Select

End Sub

I have a plot with dates on the x-axis and numbers on the y-axis.

I am importing database data into excel in the first tab, and

referencing that data in the second tab. The problem I run into is

that when the imported cell is blank, the reference in tab 2 displays

a 0 value. This presents a problem for my plot, which is auto-scaled

on the x-axis.

Example case (3x3 table):

x values: 03/01/07, 05/01/07, blank cell

y values: 50, 30, 20

The blank cell is for a future date that has not transpired. The y-

value is known, but the actual date realized is not, until it happens.

(1) Make a simple column chart, single series, with x values and y

values defined accordingly. You will get an auto-scaled plot showing

March, April, and May, and values of 50 and 30 plotted. This is what

I am looking for, so all is good when the data is manually enterred.

(2) Now, I make the cells in the table reference cells in Tab1 with

the values (e.g. ='Tab1'!A3). The blank cell is blank in tab1, but

displays 0 (or 1/0/1900 if cell is formatted as a date). This now

makes the plot graph a value of 20 on the date of 1/0/1900. This is

not what I'm looking for. However, if I select the cell in Tab2 and

press delete, the plot reverts to the correct view, but I lose my

reference.

I want the behavior in (1) when I'm referencing values in the other

tab, as in (2). Is this possible? Can I use an IF formula that's

something like IF('Tab1'!A3="", [leave blank / set blank], 'Tab1'!

A3)? Is there a setBlank() formula of some kind that can be used in

this fashion, whereby still maintaining the reference to the imported

cell value?

Any insight would be much appreciated.

Thanks,

Nate

currently is =1-(C15/G15) however when C15 is 0 my return is #DIVO! I would

like for the cell to remain blank if the c15 cell is 0.

blank cell?

I am trying to mask a blank cell (input data cell) that is referenced in a

formula (output cell) so that it returns a blank, but if a 0 is entered into

the input cell I want the 0 to be shown in output cell.

Here is my situation. I have a table with several rows and columns. The first 3 columns are populated via a vlookup from a pivot table. However, in some cases there are empty cells (meaning that there was no recorded data for that month in that category). I have my pivot table settings set to display a "*" for any blank cells. However, the fourth column is a formula that divides column 3 by 2. But in those cells where there is an * I obviously get an error message as the result for the formula. Is there anyway to store the values of the cells with *'s as "0" but display them still with *'s?

Thanks!

All I need it to do is perform the lookup on blank cells only.

Set AB = ThisWorkbook.Worksheets("All Books")

LastRow = AP.Range("M65536").End(xlUp).Row

AP.Unprotect Password:="Sales"

Application.ScreenUpdating = False

Selection.Value = "=VLOOKUP(M2,'All Books'!$A:$Q,2,0)"

AP.Range("O2:O" & LastRow).SpecialCells(xlCellTypeBlanks).Select

Selection.Value = "=VLOOKUP(M2,'All Books'!$A:$Q,3,0)"

AP.Range("P2:P" & LastRow).SpecialCells(xlCellTypeBlanks).Select

Selection.Value = "=VLOOKUP(M2,'All Books'!$A:$Q,4,0)"

AP.Range("Q2:Q" & LastRow).SpecialCells(xlCellTypeBlanks).Select

Selection.Value = "=MONTH(P2)"

AP.Range("R2:R" & LastRow).SpecialCells(xlCellTypeBlanks).Select

Selection.Value = "=YEAR(P2)"

AP.Range("S2:S" & LastRow).SpecialCells(xlCellTypeBlanks).Select

Selection.Value = "=VLOOKUP(M2,'All Books'!$A:$Q,7,0)"

AP.Range("T2:T" & LastRow).SpecialCells(xlCellTypeBlanks).Select

Selection.Value = "=VLOOKUP(M2,'All Books'!$A:$Q,10,0)"

AP.Range("U2:T" & LastRow).SpecialCells(xlCellTypeBlanks).Select

Selection.Value = "=VLOOKUP(M2,'All Books'!$A:$Q,5,0)"

AP.Range("V2:T" & LastRow).SpecialCells(xlCellTypeBlanks).Select

Selection.Value = "=VLOOKUP(M2,'All Books'!$A:$Q,14,0)"

With AP.Range("N2:V" & LastRow)

.Copy

.PasteSpecial (xlPasteValues)

End With

Application.ScreenUpdating = True

AP.Protect Password:="Sales"

End Sub

Public sWorkbookName As String Sub amplitude_copy() 'hotkey ctrl-f 'save workbook name sWorkbookName = ActiveWorkbook.Name 'create counter Dim myCounter As Integer 'select the first value of the colmn I want to copy from Range("R2").Select 'select cell in next row with myCounter as variable ActiveCell.Offset(myCounter, 0).Select 'when reaching blank cell the reset myCounter and return to other workbook If ActiveCell.Value = "" Then myCounter = 0 Windows("normal-deaf, amplitude vs stimulus.xls").Activate 'else myCounter+1, copy selection, go to other workbook and select next cell in this workbook Else myCounter = 1 + myCounter Selection.Copy Windows("normal-deaf, amplitude vs stimulus.xls").Activate ActiveCell.Offset(0, 1).Select End If End Sub Sub paste_values_amp() 'hotkey ctrl-d 'paste value Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'return to other workbook Windows(sWorkbookName).Activate Call amplitude_copy End SubThe problem is that the cell doesn't offset and stays at "R2". I think the counter doesn't count because when I do: "myCounter = 2" then the activecell is "R4".

What is wrong with the counter?

=IF(C19<TIME(16,0,0),M19,0)

Thank you.

http://groups.google.com/group/micro...ing/browse_frm

/thread/b5e678e531ed5c4c/0670a92b4f4ea81d?lnk=st&q=VBE+EXCEL+2000+AND+EXCEL+

2002&rnum=1#0670a92b4f4ea81d

wherein Jim reach has explained the problem of running a macro prepared in

excell 2000 in office xp I had similar problem

column B has stock symbols(column A is the corresponding full name)

in column C(col B cell.offset(0,1)) I am downloading stock data from a

webpage which has the stock symbol as part of the url

so I introduced a variable in the url so that variable will be corresponding

stock symbol and one by one in each row the stock data can be downloaded

the url inthe code statement is

=============

Set source = Worksheets("sheet1").Range("B5")

do(in range from B5 down to the last entry in col B)

sNWind = "URL;http://in.finance.yahoo.com/d/quotes.csv?s=" & source &

"&m=b&f=sl1d1t1c1ohgv&e=.csv"

Set oQryTable = Worksheets("sheet1").QueryTables.Add( _

sNWind & ";", source.Offset(0, 1))

oQryTable.Refresh False

set source=source.offset(1,0)

loop(until source is blank

================

where the source is the variable , source are the successive cells in

column B

this works ok in excel 2000(windows 98SE)

when I use this in excel 2002(XP) as the new data is downloaded the data in

the previous row shifts to the right cell successively

could this be due to some reference problem in excel 2002 vba.

of course I got out ofthe problem by some tortuous and inelegant codes. I

am not happy with this.

an extract of col B is given below (if needed for testsing) B5 onwards.

CMC.NS

DABUR.NS

520022.BO

FINPIPE.NS

526235.BO

MRPL.NS

MTNL.NS

NATIONALU.NS

NAVBARFER.NS

NIITLTD.NS

=IF(A6="","",VLOOKUP(A6,'Table for Talk Ref'!$E$4:$F$236,2,0))

Thanks... Stumped

I'm trying to plot costs vs. time (month) on an area chart. I want to split

up the costs by time, so I've created a bunch of different data series which

look something like:

Jan Feb Mar Apr May Jun

$2 $6 $1

$1 $3

$3 $8 $4

This is basically just breaking up the original data series... ie

Jan Feb Mar Apr May Jun

$2 $6 $1 $3 $8 $4

I was hoping this would produce a nice, continuous area chart sectioned off

by colour. The problem is that it interprets the blank spaces as zero and so

I get these weird edges at the end of series... like going from $3 in Apr to

$0 in May. What I would like it to do is just drop straight down from $3 to

$0 in Apr (so just a vertical line). Any way to do this?

If there's a way to shade under line graphs, that would also do the trick...

because those don't plot blank spaces as zero. Thanks for your help!!

Ok my problem is this. I have a pivot tanle that check a load of data a filter out what i need. It filter out month, year, severity and client.

The pivot table shows this:

Month: April

Severity: 1

Year: 2009

Client: ABC

Total Count = 3

I am trying to do the same thing with the following formula:

=SUMPRODUCT(--(Data!$AB$2:$AB$65000=2009),--(Data!$AC$2:$AC$65000="April"),--(Data!$I$2:$I$65000=1),--(Data!$K$2:$K$65000="ABC"))

this give me a total count = 0.

The reason for the us eof the formula is that the pivot table compares two years, and only shows blank cells if at least 1 year has data

eg

Jan 2008 = 0

Jan 2009 = 1

the above is shown, the below is not:

Jan 2009 = 0

Jan 2008 = 0

I need to be able to see all not just the stuff with data..

Here is the Formula:

=IF(IF(ISNA(INDEX('[Acct Paper Adjustments.xls]Jan2010'!$A$1:$G$300,MATCH($A87,'[Acct Paper Adjustments.xls]Jan2010'!$A$1:$A$300,0)+3,4)),"",INDEX('[Acct Paper Adjustments.xls]Jan2010'!$A$1:$G$300,MATCH($A87,'[Acct Paper Adjustments.xls]Jan2010'!$A$1:$A$300,0)+3,4))="","",IF(ISNA(INDEX('[Acct Paper Adjustments.xls]Jan2010'!$A$1:$G$300,MATCH($A87,'[Acct Paper Adjustments.xls]Jan2010'!$A$1:$A$300,0)+3,4)),"",INDEX('[Acct Paper Adjustments.xls]Jan2010'!$A$1:$G$300,MATCH($A87,'[Acct Paper Adjustments.xls]Jan2010'!$A$1:$A$300,0)+3,4)))

My question simple. Would it be more efficent to code this into the cell as opposed to building the formula in the cell. Any ideas?

I want to make a condition that says "If cell is exactly 0 make it red." However Excel is making the cells red, which have NO number in it. Does excel assume a cell that is empty to be ZERO?

THis is my problem. I can;t make a cell red that has 0 (which is what I want) without also making the empty ones red too.

Thanks for any help.

Barry

I have the formula as a simple "=P34-B34" where as P34 is 2007 profit %, and B34 is 2006 profit%., to find the difference between the 2 years. Since August - December 2007 hasn't happened yet, it is taking 0 (blank cell) and subtracting 31, to get a -31% change over last year, only because the cell is blank. How can I chart them without the values being negative?

Is there any way to make this work?

=INDIRECT("SHAPE"&MATCH(B23,Shapes,0))

This refers back to obviously column B which is basically a filter column to reduce the number of options in my drop down list. The problem is that sometimes I need to be able to type in those cells vs taking what is in the drop down list.

Can I add something to the filter column (column B) list that I can then reference in my drop down list that would allow me to type in that cell? I'm not sure how the language would look but something like (in column B's drop down: <Vari> and then in column D: IF(CELL IN B) <VARI>, THEN REMOVE DROP DOWN. This would basically make the cell a blank cell that could be typed into.

Thanks in advance for any help.

Jim

When I copy this spreadsheet to new blank worksheet, the criteria field shows up as true and false, and the filtering doesn't work. Does anyone know what determines true/false in cells, versus 1 and 0's? This failure to filter on new spreadsheets is driving me up a wall.