Return BLANK is cell value sought is blank

F

frankjh19701

I have a INDEX and MATCH formula set to look for certain values in on
sheet and return the data from adjacent cells. But, if the cell th
formula refers to is blank, it returns a "0" instead of just leaving th
cell blank. Does anyone know how I can change my formula:

=INDEX('Sheet1'!D$2:D$192, MATCH(A29,'Sheet1'!C$2:C$178,0))

to return a "BLANK" ??

Any and all assistance will be greatly appreciated.

Thank yo
 
C

Claus Busch

Hi Frank,

Am Mon, 21 Oct 2013 13:29:51 +0100 schrieb frankjh19701:
=INDEX('Sheet1'!D$2:D$192, MATCH(A29,'Sheet1'!C$2:C$178,0))
Click to expand...

the formula then is very long :-(
=IF(INDEX(Sheet1!D$2:D$192, MATCH(A29,Sheet1!C$2:C$178,0))=0,"",INDEX(Sheet1!D$2:D$192, MATCH(A29,Sheet1!C$2:C$178,0)))

But you could avoid the showing of 0 by using custom numberformat e.g.
#,##0.00;-#,##0.00;
Or you use Conditional Formatting: If cell value = 0 font color is white


Regards
Claus B.
 
G

GS

I have a INDEX and MATCH formula set to look for certain values in
one sheet and return the data from adjacent cells. But, if the cell
the formula refers to is blank, it returns a "0" instead of just
leaving the cell blank. Does anyone know how I can change my formula:

=INDEX('Sheet1'!D$2:D$192, MATCH(A29,'Sheet1'!C$2:C$178,0))

to return a "BLANK" ??

Any and all assistance will be greatly appreciated.

Thank you
Click to expand...

Change your last arg from 0 to "", perhaps?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
F

frankjh19701

Claus said:
Hi Frank,

Am Mon, 21 Oct 2013 13:29:51 +0100 schrieb frankjh19701:
-

the formula then is very long :-(
=IF(INDEX(Sheet1!D$2:D$192
MATCH(A29,Sheet1!C$2:C$178,0))=0,"",INDEX(Sheet1!D$2:D$192
MATCH(A29,Sheet1!C$2:C$178,0)))

But you could avoid the showing of 0 by using custom numberformat e.g.
#,##0.00;-#,##0.00;
Or you use Conditional Formatting: If cell value = 0 font color i
white


Regards
Claus B.
Click to expand...

Thank you Claus for the reply. When I tried the formula, it worked o
the blanks, but when it encountered a cell that had a value in it, i
came back as N/A.

I want the formula to return either a BLANK if the cell is empty or th
value of the cell.

Any suggestions

+-------------------------------------------------------------------
+-------------------------------------------------------------------
 
C

Claus Busch

Hi Frank,

Am Mon, 21 Oct 2013 19:08:54 +0100 schrieb frankjh19701:
Thank you Claus for the reply. When I tried the formula, it worked on
the blanks, but when it encountered a cell that had a value in it, it
came back as N/A.
Click to expand...

if you get a #N/A the value from A29 is not available in Sheet1.
If you use xl2007 or later you can try IFERROR:
=IFERROR(IF(INDEX(Sheet1!D$2:$D$192,MATCH(A29,Sheet1!C$2:C$178,0))=0,"",INDEX(Sheet1!D$2:$D$192,MATCH(A29,Sheet1!C$2:C$178,0))),"")


Regards
Claus B.
 
F

frankjh19701

O.K. I tried the new formula and it leaves a BLANK cell BLANK, but i
also returns a BLANK cell when there should be a value in it.

How can I fix this

+-------------------------------------------------------------------
+-------------------------------------------------------------------
 
C

Claus Busch

Hi Frank,

Am Tue, 22 Oct 2013 14:10:39 +0100 schrieb frankjh19701:
O.K. I tried the new formula and it leaves a BLANK cell BLANK, but it
also returns a BLANK cell when there should be a value in it.
Click to expand...

the formula is tested and works.
If your value of A29 is into column C of sheet1 but the cell stays
blank, then the values have different formats, e.g. one is number the
other is text or you have in A29 or column C non visible characters like
spaces.
Can you upload the file and post the link here?


Regards
Claus B.
 
F

frankjh19701

THANK YOU!!!
That worked out perfectly.

I greatly appreciate all of your efforts!!


Now....do you know how to merge spreadsheets...

+-------------------------------------------------------------------
+-------------------------------------------------------------------
 
C

Claus Busch

Hi Frank,

Am Wed, 23 Oct 2013 13:30:09 +0100 schrieb frankjh19701:
Now....do you know how to merge spreadsheets...?
Click to expand...

if the layout of both sheets is the same, then copy the second sheet
without headers and paste it to the first empty row of sheet1.
For better information explain your layout and how the merged sheet
should look like.


Regards
Claus B.
 
F

frankjh19701

Claus,
The Merged sheet should have the following headers:
Date, Tractor #, Driver Name, Gallons purchased, Fuel Location

The trick is to merge the two sheets of data and have it formatted in
way that merges the dates with each vehicle as if the fuel purchases ar
in date order for each vehicle.

I've uploaded a sample here.

Eventually, what I want to be able to do is get all of the fue
purchases assembled and then get an average M.P.G. per vehicle.

Please let me know what you think.

Thank yo

+-------------------------------------------------------------------
|Filename: Final Product Sheet Sample.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=925
+-------------------------------------------------------------------
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Create if partial match then copy and paste data from one cell to another 1
Formula help, please. 3
Function Help - Forecast Date past due, adjacent cell is blank. Needto highlight the blank cell. 2
Return last non-blank cell in column 3
Blank Spaces on ComboBox 2
Averageifs - criteria is cell is not blank 1
Find matching cell and return value of a different cell 5
Vlookup to return 0 instead of NA 1

Top