Free Microsoft Excel 2013 Quick Reference

Blank cell vs 0 Results

I have tried every method I can think of to return a blank cell as the result
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.

I am using this code in a sheet, and it works fine. I just have one thing i would like to do with it... often, I am giving a customer a 100% discount, such as throwing cables in with their order, without charging them.

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

When creating a formula, is there a way to differentiate between a blank
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

I have a userform where when a commandbutton is clicked, I want a value pasted into the worksheet based on a combobox text selection. Used this code.

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

Greetings!

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

Ok, what is the formula to return a blank cell vs. a #DIVO! ? My formula
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.

Is there a way to have Excel know the differance between a 0 entry and a
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.

Hello,

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!

I have the following code which I thought worked well however it isn't. I need the lookup value to be dynamic and be referencing the same row as the blank cells it selects. Not sure wether a Match & Offset would work better?! Please help!

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

I'm copying values from one workbook to another. Each value has to be checked, so I do this in two steps.

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 Sub
The 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?

I am using the following formula. The problem I am having is that when I use < it returns a zero value. If is use>= or > it leaves a blank cell. I need the zero because another formula linked to m19 returns #value if m19 is blank.

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

Thank you.

I have read an interesting thread in
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

Currently I have the formula below. When its true, it retuns what I need. When its False (or does not find any matching) it returns #N/A. I want it to be blank cell instead of showing the #N/A. How can i fix that?

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

Thanks... Stumped

Hi,

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!!

HI again all.

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..

I have a large formula, that reads from a different workbook, and depending on the result takes an action. If it returns #N/A, then the IF THEN Statement returns a blank value (""). If it returns a 0 then another IF THEN statment returns a blank Value.

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?

HI,

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

Hello, I'm creating a workbook that has all of the current sales guys' total sales & profit for 2007 vs 2006. Each guy has their own chart with the dollar totals, and I'm trying to make a chart that has each guys' growth over last year.

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?

I have a data drop down list for a cell in my worksheet with the following in cells located in column D:

=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

I have trouble running advance filter with true false criteria results. I have a spreadsheet I started years ago showing criteria fields with 1 and 0, and filtering works great.

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.


No luck finding an answer? You could always try Google.