3d reference

Is it possible to suppress the "0" that is returned when referencing a blank 
cell in another worksheet?  I have used an IF function to ensure blank cells 
in the past, but that requires a good bit of labor for even a medium sized 
worksheet. I tried various cell formats, but continue to get "0"s in the 
linked cell.
0
Utf
3/7/2010 4:21:01 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
665 Views

Similar Articles

[PageSpeed] 18

You could use a custom number format or even Format|conditional formatting.

But those will hide the cells that are returning an actual 0.

I'd do that extra work:

=if('sheet 99'!a1="","",'sheet 99'!a1)

=====
You could use a custom number format like:
General;-General;;
(Positive;negative;zero;text)

Or a custom format 
cell value is equal to 0
and use the same font color as fill color.


jbc49 wrote:
> 
> Is it possible to suppress the "0" that is returned when referencing a blank
> cell in another worksheet?  I have used an IF function to ensure blank cells
> in the past, but that requires a good bit of labor for even a medium sized
> worksheet. I tried various cell formats, but continue to get "0"s in the
> linked cell.

-- 

Dave Peterson
0
Dave
3/7/2010 4:28:12 PM
EXCEL 2007

Office Button

Excel Options

Advanced

Display option for this worksheet (on right hand side)

Select / de-select Show a zero in cells that have zero value

If my comments have helped please hit Yes. 

Thanks.






"Dave Peterson" wrote:

> You could use a custom number format or even Format|conditional formatting.
> 
> But those will hide the cells that are returning an actual 0.
> 
> I'd do that extra work:
> 
> =if('sheet 99'!a1="","",'sheet 99'!a1)
> 
> =====
> You could use a custom number format like:
> General;-General;;
> (Positive;negative;zero;text)
> 
> Or a custom format 
> cell value is equal to 0
> and use the same font color as fill color.
> 
> 
> jbc49 wrote:
> > 
> > Is it possible to suppress the "0" that is returned when referencing a blank
> > cell in another worksheet?  I have used an IF function to ensure blank cells
> > in the past, but that requires a good bit of labor for even a medium sized
> > worksheet. I tried various cell formats, but continue to get "0"s in the
> > linked cell.
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
3/7/2010 5:06:01 PM
Sorry, my previous posting was between 2 different Workbooks. 

Between 2 different Worsheets of same Worbook try:-

=IF(Sheet1!A1="","",Sheet1!A1)

If Sheet 1 cell A 1 contains data that will be returned in Sheet 2 cell A1.

If Sheet 1 cell A1 is blank blank will be returned in Sheet 2 cell A1. 

If my comments have helped please hit Yes. 

Thanks. 



"jbc49" wrote:

> Is it possible to suppress the "0" that is returned when referencing a blank 
> cell in another worksheet?  I have used an IF function to ensure blank cells 
> in the past, but that requires a good bit of labor for even a medium sized 
> worksheet. I tried various cell formats, but continue to get "0"s in the 
> linked cell.
0
Utf
3/7/2010 5:22:01 PM
EXCEL 2007

I reckon this work as:-

=IF(Sheet99!A1="","",Sheet99!A1)



"Dave Peterson" wrote:

> You could use a custom number format or even Format|conditional formatting.
> 
> But those will hide the cells that are returning an actual 0.
> 
> I'd do that extra work:
> 
> =if('sheet 99'!a1="","",'sheet 99'!a1)
> 
> =====
> You could use a custom number format like:
> General;-General;;
> (Positive;negative;zero;text)
> 
> Or a custom format 
> cell value is equal to 0
> and use the same font color as fill color.
> 
> 
> jbc49 wrote:
> > 
> > Is it possible to suppress the "0" that is returned when referencing a blank
> > cell in another worksheet?  I have used an IF function to ensure blank cells
> > in the past, but that requires a good bit of labor for even a medium sized
> > worksheet. I tried various cell formats, but continue to get "0"s in the
> > linked cell.
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
3/7/2010 5:27:01 PM
I had a space in the worksheet name.

trip_to_tokyo wrote:
> 
> EXCEL 2007
> 
> I reckon this work as:-
> 
> =IF(Sheet99!A1="","",Sheet99!A1)
> 
> "Dave Peterson" wrote:
> 
> > You could use a custom number format or even Format|conditional formatting.
> >
> > But those will hide the cells that are returning an actual 0.
> >
> > I'd do that extra work:
> >
> > =if('sheet 99'!a1="","",'sheet 99'!a1)
> >
> > =====
> > You could use a custom number format like:
> > General;-General;;
> > (Positive;negative;zero;text)
> >
> > Or a custom format
> > cell value is equal to 0
> > and use the same font color as fill color.
> >
> >
> > jbc49 wrote:
> > >
> > > Is it possible to suppress the "0" that is returned when referencing a blank
> > > cell in another worksheet?  I have used an IF function to ensure blank cells
> > > in the past, but that requires a good bit of labor for even a medium sized
> > > worksheet. I tried various cell formats, but continue to get "0"s in the
> > > linked cell.
> >
> > --
> >
> > Dave Peterson
> > .
> >

-- 

Dave Peterson
0
Dave
3/7/2010 5:41:15 PM
Well, you didn't tell us which "various cell formats" you tried.
Was one of them  "General;-General;"  ?
--
David Biddulph


"jbc49" <jbc49@discussions.microsoft.com> wrote in message 
news:681F150B-D952-415A-B0E5-CE3FC39E24D1@microsoft.com...
> Is it possible to suppress the "0" that is returned when referencing a 
> blank
> cell in another worksheet?  I have used an IF function to ensure blank 
> cells
> in the past, but that requires a good bit of labor for even a medium sized
> worksheet. I tried various cell formats, but continue to get "0"s in the
> linked cell. 


0
David
3/7/2010 7:58:44 PM
Reply:

Similar Artilces:

Offset() returns reference, first not value (proof)
FWIW: In cells B10:D10 I have 111, 222, 333 In Cell D4 I have 3 In cell D6 I have =sum(b10:Offset(b10,0, D4-1)) which displays 666 << which is correct. But if in the formula bar (on cell D6) if I highlight (evaluate) the portion Offset(b10,0, D4-1) and press F9 it equates to 333 Showing =sum(b10:333) << Which of course is not a valid formula So "D10" is being returned, versus the Value of D10 Just thought I'd pass along this recent (todays) enlightenment to those in the group that didn't...

Description and reference charactors not enough
Description and reference charactors are not enough. With more pressure on finance departments to deliver accurate and complete information this is a big problem within GP. Especially when journals and othertransactions are captured ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the m...

Pivot Table VBA Reference Book & Blank Cells
Hi Everyone, I have a Pivot Table in Access 2003 and need the blank/null cells on the data axis to display "0". This is easy to do in Excel but I have searched through all of the options in Access and cannot find it. What is the VBA code required to set this option? Can anyone suggest a good reference book or website for coding Pivot Tables with VBA? Many thanks, David ...

dynamic reference to data in multiple closed workbooks
Hi, I'm working with a job list (generated out of a different program) and data in associated closed workbooks. For example, the job list has names like HD-100311-TA031110, SHT-100312, 032110Mag and I can generate a list of these jobs along with other information I need. For every job there's an associated workbook and, without opening the workbook I want to pull, for example, '[\\de-mt1\clients\QC Reports\Proofs Adjusted\HD-100311-TA031110 proof adjusted.xls]'Upload'!A$2 where the only information that will change is the job name. I'm novice enough not to...

Circular Reference Warning ???
Hi I receive a this message 'Circular Reference Warning'...One or more formulas cantian circula reference and may not calculate correctly. How is this happened? I checked all the formulas I think they're okay. Am I missing something? How I do fix it? and how do I clear this message Circular Reference warning? Your help is appreciated? Soth When the warning comes up it gives you some tools to trace the error. Circular reference error are no always obvious to find but if the warning came u pthe error is there somewhere. -- HTH... Jim Thomlinson ...

Worksheet reference ?
Hello, Is there any way when copying a worksheet to update the reference from another worksheet ex: sheet2 as a formula =Sheet1!A1+1, when i copy the sheet2 in the new worksheet sheet3 the formula is the same =Sheet1!A1+1, can we makem a reference to the last sheet or update the sheet +1 to be automatic =Sheet2!A1+1 Thank Try this: =INDIRECT("Sheet"&MID(CELL("filename",A1),FIND("]",CELL ("filename",A1))+6,255)-1&"!A1")+1 Note - wb must be saved for this to work. HTH Jason Atlanta, GA >-----Original Message----- >Hello, ...

Indent/Outdent Circular Reference Error
My indent/outdent function is not working for new or existing tasks. When I select indent / outdent I receive the following error: "This outline change would create a circular relationship. Indenting these taks would create an illogical relationship with other tasks. Check the task dependencies for the tasks from which you are indenting, and then try again." I am confused because this error will even impact new tasks which are not linked. Linking (predecessors/successors) does not seem to be a factor. I am not linking to summary tasks in the .mpp. Thanks, ...

How to refer to active cell on spreadsheet?
Excel 2000 on Windows 2000 I'm looking for functionality similar to "ActiveCell" in VBA. I have week-ending dates in Column A. Columns B through H are the days of the week, Mon - Fri, so cell B1 = "Mon", C1 = "Tue", etc. I want B2 to be whatever the date value in Column A in the current row is, minus 6 (my weeks end on Sunday.) So if my active cell is B6; A6 = 1/19/03; and then I want B2 to read 1/13/03 (Monday's date), or 1/19/03 - 6. A B C D E F G H --------------------------------------------------...

relative references when copying Sheets containing form controls
I've been given a spreadsheet that I need to take copy various sheets from, the sheets contain form controls, when I copy the sheet to a new workbook, named ranges and so on come across fine, but the references to the input ranges of for example the drop down box controls become hard coded to the original sheet, I don't want this.. Is there anyway to alter the way excel copies so that the references remain relative rather than absoloute???? What did you include in the addresses for those references? I put a listbox from the forms toolbar on a worksheet. If I used a range like: $b$...

help with previous sheet reference formula
Subject: excel formula From: "TJ" <anonymous@discussions.microsoft.com> Sent: 4/28/2004 11:30:51 AM trying to create a payroll spreadsheet using "sheets" as payperiods. Having trouble creating incrimental increase of accrued Vacation Sick and Comp without re-entering data on each sheet. need a formulas for the following: "previous sheet value" + or - "Accrued time" equals new value I have 26 Sheets representing pay periods. I need a "previous sheet reference" instead of a specific sheet reference. Please help t...

Creating a Bar Graph with a reference point
Hey guys, I would like to create a Bar Chart/Graph (horizontal) that would have two sets of values and one category group. I would like to display this as followed: value set 1 to be a bar and the value set 2 to be a reference dot for value set 1. Say my Data looks like the following: CatergoryGroup Val1 Val2 Month1 1 4 Month2 2 3 Month3 7 5 Again, I want Val1 to be bars. Val 2 I want to be dots on the same graph. I looked at the Column Charts and there is the "Plot Data as Li...

Named range row/column reference
Hello, I am using named ranges in a VBA macro StudentNbr is A26:A45 TestWeek is E25:X25 TestScores is E26:X45 As you can see this is a grid of data with student numbers down th left side, test week numbers across the top and the grid containin test scores. I would like to update the cells in TestScores based on the positio of the values in the StudentNbr and TestWeek named ranges using: Range("TestScores") (vStudentNbr,vTestWeek).value = vTestScore *Is there a way to get vStudentNbr and vTestWeek as relative reference to the beginning of their respective named ranges? *...

Apache Axis and Visual Studio .NET Web Reference
Hi all, I noticed that adding in Visual Basic .NET a Web Reference to an Apache Axis web service results in a URI in the generated code that no longer has a port number. I tracked down the problem to the WSDL file created by disco.exe from Axis WSDL URL: even though the Axis WSDL contains the port number in the soap:address, the WSDL created by disco.exe no longer contains the port number in soap:address. Is this a known problem? Has a fix been issued that solves this problem? Thanks you for your assistance in this matter. Georges Georges, what version of VB.NET are you using? I ...

Absolute absolute cell reference
Is there a way of defining a cell to reference (eg. just equal) another cell absolutely, even if rows or columns are inserted in between. For example: Cell A1 you define =C1 If you insert a column between A and C I still want A1 to reference the new C1 not D1 ($s don't do this) jon Use the OFFSET function. Try this in A1: =OFFSET(A1,0,2) Andy "jon west" <westjc1@yahoo.co.uk> wrote in message news:0bff01c38e68$bd0573e0$a401280a@phx.gbl... > Is there a way of defining a cell to reference (eg. just > equal) another cell absolutely, even if rows or columns > ...

cross reference two
I'm need to write a formula based on information in a table - as an example: High Performing Location Market Area Non-Market Area New Location 1 $1,000,000 $750,000 $500,000 $200,000 2 800,000 600,000 300,000 100,000 3 550,000 400,000 200,000 50,000 I want to lookup the information in the top row and on the left column, then multiply by a factor referenced in ...

Can't open file: "The .VBP file for this project contains an invalid or corrupt library references ID"
I'm running Microsoft Excel v.X on a Mac with Panther (OS X). An Excel file which I used a lot now cannot be opened - it says "The .VBP file for this project contains an invalid or corrupt library references ID". I have no idea what this is - it's a single file with just 2 sheets in it, and is fairly simple - no links to outside objects, no graphics, etc. Just a single file. What can I do to get it opened?? Microsoft Word can read it, but I'd like to keep working on it in Excel... Thanks in advance! Mike Levin ...

References
I have a worksheet for each month, and reference my ending balances in the previous month in the current month. I now want to create a summary worksheet and need to reference the current months balances. I'd like to create a reference whereby I can replace the "month" portion of the name, using a cell on the summary sheet. example: tab names are AB_nov_2009, AB_dec_2009 etc. my reference would be: =AB_nov_2009!b4 I'd like to use a cell to change the nov to dec without having to go to each cell and change the month. Possible? Do you want to start with A...

Don't want to type domain reference in OWA @ log on
Is there a way to not type in the domain reference in OWA when the user is logging in? My users are confused when logging on from 2000 to XP. Thanks. Yes, You can!! ESM\Frist Administrative Group\Server\Exchnge Server\Protocol\HTTP\Exchange Virtual Server\Exchange right click proterties\Access\Authenticate in the Default Domain,pls type your domain name after restart your default web site(using IIS Admin MMC Tool) -- Jammyù�ٴ� "ctrlgrid" <jkit001@yahoo.com> ���g��l��s�D :4f9d64d8.0410260000.51e66dfd@posting.google.com... > Is there a way to not type in the domain re...

cell reference of a formula
I am trying to get Excel to "know" which cell a formula is in and then count a certain number of rows and columns to find where the data that needs to be used in the calculation is. For example if I have data that needs to be used in the calculation in C2:C6 and the formula is in B7, I need Excel to count one column to the right and 4 rows above from B7 to find the data and work out the result of the formula. I can do the part about counting the rows and columns, but I can't find how to get Excel to work out the cell reference for the formula cell. Is this poss...

XML Document reference between asp.net pages
Hi all, I have just started working with XML documents. I have just built a form that creates an xml document and it sends it to biztalk where i get an xml document as a response. i read a node value from the xml document and depending on the result i open a new page. I was wondering what the best method is for referencing the response xml document created on the previous page??? i.e. should i save it or cache it or what ??? As i say im quite new to this so any help greatly appreciated. Regards CG ...

Formula to reference another worksheet, locate data, then record i
Hi All, It has been over 10 years since I did my Excel studies and I've unfortunately forgotten everything I haven't used regularly. My office has recently upgraded to Office 2007 (upgrade not being the descriptor I'd have chosen!) and I'm struggling with Excel. I've found my way around most issues, but I'm REALLY stuck now and suffering Friday-itis on top of it all! Essentially I have a multi-sheet workbook for my debtors ledger. Each page has separate columns for the customer numbers, names, total debts in each age range (7 days, 14 days etc), totals and lastl...

data refresh cell reference
Hi, I have a workbook that has several different spreadsheets, each with different query that gets refreshed through ODBC when the workbook i used. Each query requires that a date range be entered. It will always be the case that the same date range will be entered fo each of the 6 queries. Question: is it possible to have the query reference specific cells i the workbook, so that my users don't have to enter the same "To" an "From" dates six different times? In Access I'm able to referenc values on forms, but the same logic doesn't seem to apply with ...

How to reference to a part of the XML doc?
Hi, I have this structure: <a> <repeated_part> <just_the_same_thing_and_more/> </repeated_part> </a> <b> <repeated_part> <just_the_same_thing_and_more/> </repeated_part> </b> Is it possible to refactor this at text doc level to something like this: <repeated_part> <just_the_same_thing_and_more/> </repeated_part> <a> <reference to the repeated_part> </a> <b> <reference to the repeated_part> </b> Pls note: I want parse this DOC and...

Access VBA Object Reference
The problem that I am having is that some computers in our office have office xp and others have office 2003. I made and access database that has a bunch of vb behind it in office 2003. When I got to run it in office xp it blows up. Under the references tab it says that it is MISSING:Excel 11.0 Object Reference Library. Is there a way to use that library under office xp. Thanks in advance. Chris Excel11.0 Object Library means Excel2003. The same library for OfficeXP is 10.0. If you want to have your access application to work with multple version of MS Office, then you need to devel...

Help, change range names back to cell references?
Hi: Created a worksheet and named many cells for formula purposes, but now I want to duplicate the worksheet for other jobs. When I create the new worksheet, the formulas retain the range names and refer back to the originating worksheet not the current one. What's the easiest way to change all the formula's to refer to the urrent worksheet. Deleting the name screws up the formulas. Thanks... I think you just think it is referring to the same name. If you used a regular copy|move sheet to make a copy of the sheet in the same workbook, it also kept the named ranges on tha...