Would like to show a Blank cell on a #value! error

I am entering a date in one cell, then in another cell I am using =sum(a1-7) to sub 7 days from the date entered in cell A1. The problem is that if a date has not yet been entered in A1, then the formula cell displays #value

I would like to hide this error or just show a blank cell. 
0
anonymous (74717)
5/4/2004 2:41:02 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
396 Views

Similar Articles

[PageSpeed] 47

Rick, =IF(A1<>"",A1-7,""). Also note that in your original formula the SUM
was not necessary -- A1-7 is all you needed so far as the arithmetic was
concerned.
-- 
DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com


"Rick" <anonymous@discussions.microsoft.com> wrote in message
news:5CAE8EC4-9A8A-4B3F-8E8A-DA2F486A6FE7@microsoft.com...
> I am entering a date in one cell, then in another cell I am using
=sum(a1-7) to sub 7 days from the date entered in cell A1. The problem is
that if a date has not yet been entered in A1, then the formula cell
displays #value!
>
> I would like to hide this error or just show a blank cell.


0
DDMara (266)
5/4/2004 2:54:12 AM
You'll need to do an if or statement:

=IF(A1="","",(A1-7))

This simply says if A1 is blank, then make this cell blank, if not sub 7
from A1.

"Rick" <anonymous@discussions.microsoft.com> wrote in message
news:5CAE8EC4-9A8A-4B3F-8E8A-DA2F486A6FE7@microsoft.com...
> I am entering a date in one cell, then in another cell I am using
=sum(a1-7) to sub 7 days from the date entered in cell A1. The problem is
that if a date has not yet been entered in A1, then the formula cell
displays #value!
>
> I would like to hide this error or just show a blank cell.
>


0
mivey001 (1)
5/4/2004 3:06:23 AM
Reply:

Similar Artilces:

I would like to
I would like to have the Sales rep printed on the receipt, how do I accomplish this? Craig, Download the Receipt - 40 column with Sales Rep from here; http://tinyurl.com/4gdbu You must have access to CustomerSource. -- * Get Secure! - www.microsoft.com/security You must be using Outlook Express or some other type of newsgroup reader to see and download the file attachment. If you are not using a reader, follow the link below to setup Outlook Express. Click on "Open with newsreader" under the MS Retail Management System on the right. http://tinyurl.com/75bgz ********** &qu...

WLM has encountered an error & must close
Using XP -SP3 WLM Ver 2009 Build 14.0.8089.0726 Had no problems until I installed updates today, then WLM started having the above error. Has to be one of the below updates. Security Update for Windows XP (KB2229593) & Security Update for Microsoft Outlook 2002 (KB980371) Anyone have similar problem or an answer - it dies every time I launch it. Does the problem disappear if the updates are uninstalled or XP returned = to a prior pre-udpated conditon via System Restore ? -- ....winston ms-mvp mail "Bill R" wrote in message = news:2BA7AD0F-1E03-4...

Can I copy a table from Word into one cell in Excel without losing data? (Office 97)
I would like to copy a small table from Word into one cell in an Excel worksheet. The first column of the table is a list of numbers. I tried converting the table into text with manual line breaks and tab stops to divide columns and rows, but that didn't solve my problem. Excel pastes the data into several rows. When I try to merge them, I get a warning that the selection contains multiple data values, and merging into one cell keeps the upper-left most data only. What I tried that didn't work: * Formatting the Excel cells as text before pasting the data. * The various options for ...

Is that a blank in your TextBox ...
.... or are you glad to see me? (Sorry, an oblique reference to an old Mae West line.) But seriously ... sometimes a blank in a TextBox means something very much different than nothing in the TextBox, but there is no visual clue to the end user which permits him to distinguish between the two cases - that I know of. So that's my question. Is there anything I can do with a standard TextBox which would make it visually obvious whether a string ended with the last visible character or had some number of trailing blanks? I suppose I can develop my own class which inherits ...

How do I transpose Comma Separated Data in each cell and delete t.
I need to switch a names list in each cell: [LAST_NAME, FIRST_NAME] to [FIRST_NAME LAST_NAME] (no comma), preferably into two cells: [FIRST_NAME] [LAST_NAME] Last Name: =LEFT(A1,FIND(",",A1)-1) First Name: =MID(A1,FIND(",",A1)+2,999) HTH Jason Atlanta, GA >-----Original Message----- >I need to switch a names list in each cell: >[LAST_NAME, FIRST_NAME] >to >[FIRST_NAME LAST_NAME] (no comma), >preferably into two cells: >[FIRST_NAME] [LAST_NAME] >. > You may be able to just select the column of names and use Data>Text to Columns. Specif...

HELP Error: Trying to populate userform fields from access databas
Hi All, I have been struggling with this since long, I would appreciate if anyone can help me with getting this done. Basically I want to pull information from access database and populate my excel userform fields. For eg: If I input a Student Id field I want to populate the Name and Phone number for that student from access database, Here is the code that I have so far, But it gives me errors.. Please guide me through this Private Sub StudentId_AfterUpdate() Dim cnt As ADODB.Connection Dim rst As ADODB.Recordset Dim strSQL As String strSQL = "SELECT Name, Phone FROM...

Show sum based on selection from Combo Box
I have a form, on the form there are 4 combo boxes for selection and the result are showed in a subform. I created a table (tblHours_worked) that contain the number of hours worked by each in employee. Now based on the selection from the combo box(s) how do I get the total hours worked to calculate and show in a field on the main form? Hopefully, this makes sense. -- tmdrake Add the textbox to the main form, I'm going to call it txtHourSum. For the control source for the txtHourSum put in something like this: =3DDSum("WorkHours","tblHours_worked", "Employ...

receive email error
Hello, my problem it's that I can't receive external emails in my mairelay. yes I can sent but a can't receive. I don't know tecnical commands to test my Exchange server. My supplyer from the Internet connection told me that it's a problem from my fireWall server. How can I test my exchange from outside. EVIANA wrote: > Hello, > > my problem it's that I can't receive external emails in my > mairelay. > yes I can sent but a can't receive. > > I don't know tecnical commands to test my Exchange server. > My supplyer from t...

How to capture Max cell value (High Water Mark)
I have a spreadsheet that uses DDE to repeatedly refresh data. Of course, upon each refresh all formulas are calculated. Is there a way to capture the Max value that a particular cell has contained through all the refreshes? I guess you could call this a high water mark function of some kind. I am hoping a clever formula exists to do this. Although I am not very savvy on VB I could probably muddle through it if necessary. My big problem would be how to invoke the VB code automatically with every refresh. I think the VB code has to be linked to something called event processing. I have...

expanding cells
Is there a setting that will automatically allow a cell (or row) to expand as information is entered? Alan Investigate Format>Cells>Wrap Text and Format>Row>Autofit. Also Format>Column>Autofit. Gord Dibben XL2002 On Fri, 7 Nov 2003 11:46:19 -0800, "Alan" <aeckhoff@phlyins.com> wrote: >Is there a setting that will automatically allow a cell (or row) to expand as information is entered? About the closest I can find is the "Wrap Text" function under "Format Cells->Alignment" It won't re-size the cell but "stacks" ...

I want to make a simple ledger showing expenses and income
I need a ledger to keep track of expenses and Income on some real estate I own. I have microsoft office ,student and teacher edition,which includes word, excel,outlook,and powerpoint 2003. Try here, Finance and Accounting section http://office.microsoft.com/en-gb/templates/default.aspx?DPC=%7B90110409%2D6000%2D11D3%2D8CFE%2D0050048383C9%7D&DCC=%7B5572D282%2DF5E5%2D11D3%2DA8E8%2D0060083FD8D3%7D&AppName=Microsoft%2520Excel&HelpLCID=1033&CLCID=1033 -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ...

to show to hide, Drop Down
Dear Amigos: It wanted to see like hiding or to show a Drop Down, which I need is that if in the cell a1 the value is of 1, shows drop down 1, and if the value a1 is equal to 2, then drop down 1 is hidden, I wait for is possible, I wanted to modify the code down but I could not, logically the code is shot when the values in a1 are fulfilled, Greetings Isaac Private Sub hidden_DropDown() Me.Shapes("drop down 1").Visible = DropDown.Value = -1 SendKeys "{esc}" End Sub I used the worksheet_change event (A1 was changed by typing) to hide dropdown 1: Option Expli...

Delete Blank Rows when Pivot Table included on sheet
Hi, I've run code to delete blank rows which works fine. That is, it works fine until I'm deleting blank rows on a sheet which also contains a few pivot tables. I encounter an error message regarding not being able to change pivot table. Here is my code: Thanks! Dim x As Long With ActiveSheet For x = .Cells.SpecialCells(xlCellTypeLastCell).Row _ To 1 Step -1 If WorksheetFunction.CountA(.Rows(x)) = 0 Then ActiveSheet.Rows(x).Delete End If Next End With You can't delete or add rows to a Pivot Table. Delete...

Advance Filter can be based on cell Color in Microsoft Excel
In MicroSoft Excel, Advance Filter (Data->Filter->Advance Filter) feature can be enhanced. New feature that we can introduce is to do Advance Filter based on Font/Highlighted Color. e.g. If in excel I have 10 Rows and 3 rows font color is RED and 7 rows font color is GREEN then using Advance Filter option we can apply filter based on Font Color or Highlighted Color. ---------------- 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 ...

Missing import filter for Word! Like "WordPerfect 5.1 (UNIX)" ...
I'm missing much import filter on Office-Mac (specially WordPerfect 5.1 UNIX for Word.mac). Office 95/97/2000/XP for windows have much more useful import filter. Why all this filter are missing in Office.mac????? Or are all them included in an additional package????? Hi Dieter, This question has been answered over and over again in the Macintosh Word newsgroup. Take a gander in that newsgroup for your answer. Office does not ship with a converter because a converter has not been built that does a good job with converting. Details are here: http://www.mvps.org/word/FAQs/General/WordP...

Conditional formating an entire row vs. only one cell
I have 400 rows of data. There are four columns that contain any one of five different key letters (R, NR, O, U, NA). What I want to do is that everytime a letter such as NR shows up in any of the four columns, the entire row has a conditional format applied. When I do "Cell Is", the conditional format is only applied to the one cell that meets the condition even if I have the entire row(s) highlighted when I create the conditional format. I tried to find similar posts to help but have not been successful. Using Excel 2003. Highlight all your data (assuming you have start...

Cell formating
How do I format a cell such that I see a number say 40000000.00 as 4,00,00,000.00 ? -- IndianGuru ------------------------------------------------------------------------ IndianGuru's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27382 View this thread: http://www.excelforum.com/showthread.php?threadid=469097 Click Format then Cells. Select the Number tab. Click on Custom in the category box and in the Type box type #,###.00 "IndianGuru" <IndianGuru.1vngya_1127207106.465@excelforum-nospam.com> wrote in message news:IndianGuru.1vngya_11272...

PivotChart, How to show Grand Totals in the chart?
I want my grand totals to show in my pivotchart, how can I get this accomplished? Thank you I don't know of a way to include the grand total in a PivotChart. You could create a normal chart, based on the pivot table, and include the totals in that. On Jon Peltier's site, there are instructions for creating a normal chart from pivot data: http://www.peltiertech.com/Excel/Pivots/pivotcharts.htm Kristin wrote: > I want my grand totals to show in my pivotchart, how can I get this > accomplished? > > Thank you -- Debra Dalgleish Excel FAQ, Tips & Book L...

Making Excel act like a database.
I need some help on a common problem in our company. We have several clients that we have agreed to do some Excel 2003 spreadsheets for. The spreadsheets analyze our performance for them. These spreadsheets follow a very specific formatting that the clients have gotten use to and are unwilling to change. When we have to update the spreadsheets once a month, it can take all day to do the updating for each client because we are currently using copy and paste to get the data in the right cells. I'll explain more in a second, but what I am hoping to find out from this posting is alternate...

macro problem. Upgraded to Excel 2002, now getting error message
Hello, this is my first time posting so please go easy on me. I am running a macro that pulls data from several files an interpolates information; the process takes about 20-25 mins. upgraded to excel 2002 and now the when I run a large input file i runs about 2/3 of the way then gives me the standard error messag saying the program needs to shut down. If i cut the file up in smalle parts the program will run through, but it's a pain stitching th output back together. I have never had this problem before when I wa running Excel 2000. Is there a setting I have to change? -Thanks :conf...

My Headers are Spam Like
I have an Exchange 5.5 server on NT domain, the headers on messages say: received from <machine name>.<domain name> which is SERVERA.COMPANYB, and some sites are rejecting our mail as spam. I think it's a dns problem because SERVERA.COMPANYB doesn't resolve to anything. Is there any way I can get a .COM into that header? dlw wrote: > I have an Exchange 5.5 server on NT domain, the headers on messages > say: received from <machine name>.<domain name> which is > SERVERA.COMPANYB, and some sites are rejecting our mail as spam. > I think it's a...

chart>display value axis values?
In simple Line Chart; how to display ACTUAL data vals. on (verticle) value axis? Try right clicking the data series on the chart then format data series, select the data labels tab and check the value box. "Boswell" wrote: > In simple Line Chart; how to display ACTUAL data vals. on (verticle) value > axis? ...

Value Pack updates
I have recently (and apparently successfully) updated Office X:mac by 1 Remove Office 2 New install from CD 3 updated to 10.1.2, 10.1.4 then 10.1.5 However I now need to add more Value Pack s/w Do I have to start again with another remove+install+updates or can I add the VP s/w to 10.1.5 Office then just run the updaters again? I'm a bit wary of remove+install since someone wrote of a limit to the number of times this could be done (and don't remember the outcome or the thread) (MacOS 10.2.3) -- Roger In article <1g1p0zk.ckhf6i19kss4gN%rm@rmfsnewsXL.fsnet....

Fixed Asset
I am trying to run an integration using Integration Manager to import fixed asset records, but I am running into the following error: Source: Microsoft.Dynamics.GP.IntegrationManager.IMGPeConnect.eConnectObjectManager.UpdateObject Status Code: 0xC0040010 (-1073479664) Log Text: DOC 1 ERROR: eConnect document error. Thanks, Craig First of all, verify that the eConnect adapter is reading the entire import file...in the IM results window, scroll down and make sure it sees all the records in the file (it will tell you how many records it found). Assuming the entire file is read, you know ...

data from internet not showing up
I copied and pasted a table from the internet that has "*Bobby Abreu" in cell B3. In cell AF3 I have the formula: =VLOOKUP(IF(LEFT(B3, 1)="*", MID(B3, 2, 100), B3), draft, 2, FALSE), resulting in an error (#N/A). If I manually type "*Bobby Abreu" in cell B3, then the correct result, "65", turns up in cell AF3. I've gone through evaluate formula and both times it shows that vlookup is searching for "Bobby Abreu" in the draft table, however, when I have the internet data in, it keeps showing the error. I don't want to...