copying formula down

Using Excel 2003

Column A is dates.  These dates plus 7 are being compared to the current 
date to determine whether "overdue" gets displayed in Column D.  I'm using 
the following IF statement:

If A1+7<today(),"overdue"," "

When I copy the formula down, rows that have not yet had data entered into 
them are all displaying "overdue" because Column A is blank.  Do I just need 
to remember to copy the formula down every time I enter a new row of data, 
or is there some way I can modify my IF statement to only do the calculation 
if there is data in Column A? 


0
nospam7515 (2084)
10/26/2006 12:05:26 AM
excel 39879 articles. 2 followers. Follow

8 Replies
289 Views

Similar Articles

[PageSpeed] 38

Try instead in B1: =IF(A1="","",IF(A1+7<TODAY(),"overdue",""))
Copy down as far as required.
The additional front trap: =IF(A1="","",... will take care of the problem.
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
--- 
"BK" <nospam@nospam.com> wrote in message 
news:%23YWgxJJ%23GHA.2180@TK2MSFTNGP05.phx.gbl...
> Using Excel 2003
>
> Column A is dates.  These dates plus 7 are being compared to the current 
> date to determine whether "overdue" gets displayed in Column D.  I'm using 
> the following IF statement:
>
> If A1+7<today(),"overdue"," "
>
> When I copy the formula down, rows that have not yet had data entered into 
> them are all displaying "overdue" because Column A is blank.  Do I just 
> need to remember to copy the formula down every time I enter a new row of 
> data, or is there some way I can modify my IF statement to only do the 
> calculation if there is data in Column A?
> 


0
demechanik (4694)
10/26/2006 2:17:57 AM
BK
    Try this:
=IF(A1="","",IF(A1+7<TODAY(),"overdue","OK"))

HTH  Otto

"BK" <nospam@nospam.com> wrote in message 
news:%23YWgxJJ%23GHA.2180@TK2MSFTNGP05.phx.gbl...
> Using Excel 2003
>
> Column A is dates.  These dates plus 7 are being compared to the current 
> date to determine whether "overdue" gets displayed in Column D.  I'm using 
> the following IF statement:
>
> If A1+7<today(),"overdue"," "
>
> When I copy the formula down, rows that have not yet had data entered into 
> them are all displaying "overdue" because Column A is blank.  Do I just 
> need to remember to copy the formula down every time I enter a new row of 
> data, or is there some way I can modify my IF statement to only do the 
> calculation if there is data in Column A?
> 


0
ottokmnop (389)
10/26/2006 2:21:30 AM
If you are using 2003 then as long as you have in Tools / Options / Edit / 
Checked the 'Extend data range and formulas' option, the formulas should 
automatically be added when you put in data anyway, BUT, personally I like 
belt and braces, so i'd still be there with Max and Otto :-)

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

----------
It's easier to beg forgiveness than ask permission :-)



"BK" wrote:

> Using Excel 2003
> 
> Column A is dates.  These dates plus 7 are being compared to the current 
> date to determine whether "overdue" gets displayed in Column D.  I'm using 
> the following IF statement:
> 
> If A1+7<today(),"overdue"," "
> 
> When I copy the formula down, rows that have not yet had data entered into 
> them are all displaying "overdue" because Column A is blank.  Do I just need 
> to remember to copy the formula down every time I enter a new row of data, 
> or is there some way I can modify my IF statement to only do the calculation 
> if there is data in Column A? 
> 
> 
> 
0
ken.wright (2489)
10/26/2006 11:37:01 AM
Of course!!  I'm just getting used to nested IF statements, and I forgot all 
about it.

Thanks!!


"Max" <demechanik@yahoo.com> wrote in message 
news:eyAT5VK%23GHA.4376@TK2MSFTNGP03.phx.gbl...
> Try instead in B1: =IF(A1="","",IF(A1+7<TODAY(),"overdue",""))
> Copy down as far as required.
> The additional front trap: =IF(A1="","",... will take care of the problem.
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> --- 
> "BK" <nospam@nospam.com> wrote in message 
> news:%23YWgxJJ%23GHA.2180@TK2MSFTNGP05.phx.gbl...
>> Using Excel 2003
>>
>> Column A is dates.  These dates plus 7 are being compared to the current 
>> date to determine whether "overdue" gets displayed in Column D.  I'm 
>> using the following IF statement:
>>
>> If A1+7<today(),"overdue"," "
>>
>> When I copy the formula down, rows that have not yet had data entered 
>> into them are all displaying "overdue" because Column A is blank.  Do I 
>> just need to remember to copy the formula down every time I enter a new 
>> row of data, or is there some way I can modify my IF statement to only do 
>> the calculation if there is data in Column A?
>>
>
> 


0
nospam7515 (2084)
10/26/2006 11:54:21 AM
Thanks so much for the reminder about nested IF statements!!



"Otto Moehrbach" <ottokmnop@comcast.net> wrote in message 
news:exog2VK%23GHA.2340@TK2MSFTNGP05.phx.gbl...
> BK
>    Try this:
> =IF(A1="","",IF(A1+7<TODAY(),"overdue","OK"))
>
> HTH  Otto
>
> "BK" <nospam@nospam.com> wrote in message 
> news:%23YWgxJJ%23GHA.2180@TK2MSFTNGP05.phx.gbl...
>> Using Excel 2003
>>
>> Column A is dates.  These dates plus 7 are being compared to the current 
>> date to determine whether "overdue" gets displayed in Column D.  I'm 
>> using the following IF statement:
>>
>> If A1+7<today(),"overdue"," "
>>
>> When I copy the formula down, rows that have not yet had data entered 
>> into them are all displaying "overdue" because Column A is blank.  Do I 
>> just need to remember to copy the formula down every time I enter a new 
>> row of data, or is there some way I can modify my IF statement to only do 
>> the calculation if there is data in Column A?
>>
>
> 


0
nospam7515 (2084)
10/26/2006 11:55:03 AM
Didn't know about that option in 2003, but I've already changed to the 
nested IF statements.  Thanks so much for the information.


"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message 
news:BBA89F1D-34A0-4E51-938F-5F7DDAD1FE08@microsoft.com...
> If you are using 2003 then as long as you have in Tools / Options / Edit /
> Checked the 'Extend data range and formulas' option, the formulas should
> automatically be added when you put in data anyway, BUT, personally I like
> belt and braces, so i'd still be there with Max and Otto :-)
>
> -- 
> Regards
>           Ken.......................    Microsoft MVP - Excel
>              Sys Spec - Win XP Pro /  XL 97/00/02/03
>
> ----------
> It's easier to beg forgiveness than ask permission :-)
>
>
>
> "BK" wrote:
>
>> Using Excel 2003
>>
>> Column A is dates.  These dates plus 7 are being compared to the current
>> date to determine whether "overdue" gets displayed in Column D.  I'm 
>> using
>> the following IF statement:
>>
>> If A1+7<today(),"overdue"," "
>>
>> When I copy the formula down, rows that have not yet had data entered 
>> into
>> them are all displaying "overdue" because Column A is blank.  Do I just 
>> need
>> to remember to copy the formula down every time I enter a new row of 
>> data,
>> or is there some way I can modify my IF statement to only do the 
>> calculation
>> if there is data in Column A?
>>
>>
>> 


0
nospam7515 (2084)
10/26/2006 11:56:11 AM
You're welcome !
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
--- 
"BK" <nospam@nospam.com> wrote in message 
news:eC1g7VP%23GHA.4468@TK2MSFTNGP05.phx.gbl...
> Of course!!  I'm just getting used to nested IF statements, and I forgot 
> all about it.
>
> Thanks!! 


0
demechanik (4694)
10/27/2006 1:33:33 AM
You're welcome :-)

"BK" <nospam@nospam.com> wrote in message 
news:u8tg8WP%23GHA.3352@TK2MSFTNGP03.phx.gbl...
> Didn't know about that option in 2003, but I've already changed to the 
> nested IF statements.  Thanks so much for the information.
>
>
> "Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message 
> news:BBA89F1D-34A0-4E51-938F-5F7DDAD1FE08@microsoft.com...
>> If you are using 2003 then as long as you have in Tools / Options / Edit 
>> /
>> Checked the 'Extend data range and formulas' option, the formulas should
>> automatically be added when you put in data anyway, BUT, personally I 
>> like
>> belt and braces, so i'd still be there with Max and Otto :-)
>>
>> -- 
>> Regards
>>           Ken.......................    Microsoft MVP - Excel
>>              Sys Spec - Win XP Pro /  XL 97/00/02/03
>>
>> ----------
>> It's easier to beg forgiveness than ask permission :-)
>>
>>
>>
>> "BK" wrote:
>>
>>> Using Excel 2003
>>>
>>> Column A is dates.  These dates plus 7 are being compared to the current
>>> date to determine whether "overdue" gets displayed in Column D.  I'm 
>>> using
>>> the following IF statement:
>>>
>>> If A1+7<today(),"overdue"," "
>>>
>>> When I copy the formula down, rows that have not yet had data entered 
>>> into
>>> them are all displaying "overdue" because Column A is blank.  Do I just 
>>> need
>>> to remember to copy the formula down every time I enter a new row of 
>>> data,
>>> or is there some way I can modify my IF statement to only do the 
>>> calculation
>>> if there is data in Column A?
>>>
>>>
>>>
>
> 


0
ken.wright (2489)
10/30/2006 10:27:21 PM
Reply:

Similar Artilces:

Filtering on a formula
I have a column which has inconsistent formulae running down it. Is there any method of filtering the data dependent on the phrasing of the formula? Or can I somehow refer to the text of the formula in another formula? -- Timmy Mac1 ------------------------------------------------------------------------ Timmy Mac1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15188 View this thread: http://www.excelforum.com/showthread.php?threadid=483607 One quick and dirty way to check for formula consistency is to go into R1C1 reference style. Tools|Options|General ...

Need Formula to renumber column
I have a sheet that contains 20 tasks that have a column that ranks their priority 1 thru 20. I need to be able to take task 15 and change the priority to 3. When the priority changes I want task 15's row to become new row 3; old row 3 needs to become row 4 and the priority in old row 3/new row four needs to be changed to 4; ... and so on until row 14 becomes row 15 with a priority of 15. Any ideas on how to accomplish this? Just sort your table on the Task column. Regards, Fred "crj" <crj@discussions.microsoft.com> wrote in message news:8FD0421C-AAB...

Non-Delivery Report Copy forwarding
Hi I created a 'postmaster' mailbox that receives NDRs and then set it as so that messages to this postmaster mailbox is also forwarded to another address which is a public folder. I think I have the permissions right. The PF is called 'Non Delivery Reports Postmaster' and the mailbox is 'Postmaster General Non Delivery Reports'. The PF is a sub-folder. Using Exchange Server 2003. I get this error.. Event Type: Error Event Source: MSExchangeIS Public Store Event Category: Transport Delivering Event ID: 2028 Date: 06/07/04 Time: 3:30:31 PM User: N/A Computer: APOLLO...

Copy worksheets and save files dynamically
Hello I've been trying to write a macro to copy each worksheet within a workbook and then save each worksheet as it's own file. However I would like to do this dynamically (ideally to keep the macro short and easier to follow) so that the worksheet is selected based on a cell reference and the filename it is saved as is also based on a cell reference. I've searched the forum and tried to use some of the solutions, to similar queries, from there - which led me to try and use called subroutines. This seemed like a neater soloutin but I keep getting various error messa...

In Excel, how do you copy and paste just the subtotals into anoth.
When I have a spreadsheet with subtotals, sometimes I just want to copy the subtotals into another sheet and forget about what is being summed to give me the subtotal. How do I copy and paste only the subtotals. Hi if you're talking about the situation where if rows are hidden (through the data / subtotals function) when you select just the subtotals and copy & paste them onto another sheet all the hidden rows come too, if so: collapse the outline so only the subtotals are visible select the area containing the subtotals and then choose edit / goto and click the special button ...

help on a formula please
Im trying to write a formula that checks to see if a certain cell containing text, matches a range of cells that contain text. So in column Z1:z10 i have the following: "ES", "GE", "ZB", ... In my formula i want to see if the text in cell A20 matches anything in the range z1:z10. If it does, then i do a vlookup of that range to show the corresponding value of the 2nd column in the lookup. However, if A20 is NOT in that range then i just want it to give a value of 1. I tried IF statements and SUM IF statements and cant get it to work. The IF statements only see...

Totalling columns and repeating formulas in new entries
i'm working in excel 2003 i've tried searching this, but have come up with nothing. it seems s basic, i'm sure i must be calling it the wrong thing or something. in my worksheet (that's what a spreadsheet is called now, isn't it?) enter expenses in column "e" and revenues in column "i", with the ne gain or loss for that row (entry) showing up in column "j". i had n problem setting that up. the first problem i'm having is the formulas and formatting bein copied to the next entry. some of the new entries have no value i column "e&...

Error in formula displayed for linear and 2nd order curve fits in Excel 2003
I have created trendlines for some pretty simple data using both 2nd order polynomial and linear fits. In both cases, the displayed formulas have their 1st and 2nd order coefficients wrong by a factor of 10!! (I have read lots of posts about increasing precision but this is not what I am up against). Has anyone seen this? What version of Excel? Could you include the data in a follow up post (not as an attachment), along with the coefficients you've computed? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierT...

folders and contacts did not copy from outlook express to outlook
Hello, I've been using Outlook Express 6. With the new Windows SP3 I decided to try Outlook 2003 SP3. Outlook 2003 copied all emails into the inbox folder (about 900 of them). Within Outlook Express I had the messages in subfolders of the inbox. Also, contacts were not brought over either. Is there a way now to bring the folders over and have the email messages in the correct folders? Is there a way to bring the contact information over? I have both Outlook Express and Outlook on the machine now. If one person prefers Outlook Express and another prefers Outlook ...

macro to insert row and copy previous row + excel
I have a named range First_Table (A5:AB30 ) and a named range End_Table (A30). I would like to insert a row above the named range End_Table (A30). Then copy the contents and formula from cells F29:AB29 into the new row. However every time I run the macro it seems to insert the new row in a different place which is messing up my data. Can anyone help me please? -- Laura Always post your code. HTH Otto "Biffo" <Biffo@discussions.microsoft.com> wrote in message news:3B60286F-5DAD-478A-B98A-417F5EA61A7D@microsoft.com... > I have a named range First_Table ...

How do I insert a formula into chart source data values field box?
My ultimate aim is to build a chart that refers to values in a range of cells held in a row on another worksheet. Normally this would be straightforward by manually editing the source data in the chart and pointing to the range of cells. However, over time the range of cells used to provide the values for the chart will change and I am looking for a method that will do this automatically. I thought if I could produce a formula that identifies the cells when the change I could simply paste that formula into the Chart's source data values field. Of course, I've tried it but it will not ...

Formula to show numbers except two
Hi, I have this data in column A that changes each time. A 10 15 I have only the first 2 numbers that go from 10 to 17. I need a formula that from A3 on throws all the numbers from 10 to 17 except the first two. For example, in this case, I have numbers 10 and 15, the formula should show: A 10 15 11 12 13 14 16 17 If I had 12 and 17, it shoul show 10, 11, 13, 14, 15 and 16. Hope my question is clear. Thanks Will the 2 numbers ever be the same: A1 = 17 A2 = 17 Will the the 2 numbers *always* be listed in ascending order? Will this ever be a possi...

Copying a table to another worksheet
Hi I want to copy a table to another worksheet. The table has lots of lookups etc. When I copy the table top a summary performance worksheet the values in the table do not reference the other worklsheet which I need them to do for the table to work. How do I do this please? Cheers Brian Do you mean that you want to copy the table to another worksheet in a different workbook? And that the pasted formulas refer to the sheets in original workbook? If yes to both, then this is what I do when I want that: Before you copy the sheet, change all the formulas to text. Select all the cells edit|...

help with formula for average
I need assistance figuring out how to add a column to an existing spreadsheet that will give the average of NP's which begins in J4:j2322, this is a pivot table report already and I need to reflect the average for New clients (less than 3 months with company) and the NP's for clients with the program for more than 3 months. The column that reflects new versus old is column d4:d2322. I need to have the average be presented in one of the pivot tables. Hi have you tried to add this a data item in your pivot table (instead of a count/sum item)? -- Regards Frank Kabel Frankfur...

dragging formula copies values instead
hi, i have been experiencing a strange occurence in my workbook which i replete to the hilt with macros. when i enter a formula in a cell and drag it down, it copies the value down the column, instead of the formula. that is, if the value of th formula for a particular row is 225, it copies 225 down the whol column irrespective of what the result of the formula may be for an other row. i have checked and dont seem to find a reason for it. however, when i click F9 to recalculate, it shows the recalculates th formula and shows the correct values. could there be a logical explanation and hen...

#VALUE! on correct formulas
I am using 'vlookup' and 'match' functions that result in #VALUE!, but when I hit function wizard, it shows the correct number value I am looking for and expecting in the result area. Apparently my syntax, etc. is accurate. Why, then, does #VALUE! show up when everything is ok? thanks, mike allen mike allen wrote... >I am using 'vlookup' and 'match' functions that result in #VALUE!, but when >I hit function wizard, it shows the correct number value I am looking for >and expecting in the result area. Apparently my syntax, etc. is accurate. >...

Send copy to active window
I have a vb.net app in which I have setup a "hotkey" where when the user types that key combo, something is supposed to happen. The hotkey part is working and fires perfectly. What this is supposed to do, is copy whatever text is selected, no matter where the user is in the system, either my app, notepad, whatever, and copy that text to the clipboard. I have tried SendMessage API, VB's SendKeys, and It only seems to work sometimes. I would prefer to use SendMessage with a WM_COPY but can't get it to work at all, so I've been using SendKeys, sending "...

SUM formula automatically updating
Hello! I have a spreadsheet that has January in column D thru December i column O. My formula in column P is only for year to date numbers, s I have the formula =SUM(D1:L1) so I only sum Jan - Sept. HOWEVER, when I put in a number in column (October), Excel is AUTOMATICALLY updating my formula to =SUM(D1:M1). How do I prevent this from happening (outside of using $ - I know ho to do that, but there must be some option my spreadsheet has that i creating this automatic update - I have NEVER seen this before toda :) Thanks in advance! Rlola -- Rlola ---------------------------------...

formula works but...
Hi Thanks for your help... I wanted to be able to choose a specific value in column C to decid whether sum was positive or negative. In this case, -1,1 or 2 (but no zero, which should still return a positive value). The following solution appears to work... =SUMPRODUCT((A1:A600>=4)*(A1:A600<=9)*A1:A600*B1:B600)-(SUMPRODUCT((A1:A600>=4)*(A1:A600<=9)*(C1:C600={-1,1,2})*A1:A600*B1:B600))*2 ... but i have one more problem. Column A not only contains digits, but also X's. If the X's ar removed, the above formula works fine. Unfortunately, as soon as an is introduced th...

Pivot Table Grand Total wrong when using Formulas
I used a Formula in a Pivot Table and the the total for the formula Column is INCORRECT as it reflects only one portion of the formula. My formula is an "if statement" that if one field is "0" use the other preexisting field. Is there a way to have the total reflelct all amounts and not just the total of certain amounts?? Perhaps you could add a column to the source data, and do the calculation there. Then, add that field to the pivot table. Or, try changing the pivot table formula so it adds the two items, instead of using an IF formula. John P wrote: > I...

Formula for time?
I am trying to create a spreadsheet that calculates difference in times. however if I have someone come in early it says in the box ###Num###. Ex.. Clocked In Time of Run 7:54 8:00 8.:03 8:00 7:52 8:00 8:06 8:00 I need to run the same formula if there is overtime or if they are late. Can someone help me please...... Thanks.... Hi maybe check out: http://www.cpearson.com/excel/overtime.htm -- Regards Frank Kabel Frankfurt, Germany "shaggy78" <shaggy78@discussions.microsoft.com> schrieb im Newsbeitrag news:D912FD30-8E11-42C9-B627-71E0A64B9ABF@microsoft.com... >I am try...

Copying data from previous record
I have a form that has a combo box (cmbmonth) where the value is stored in a field txtmonth. The users normally input a batch of records at a time. So what I would like to happen is that they select a value from the combo box on the first record but as they move to a new record the value is carried over to the same control on the new record. This should happen with every new record until they decide to select a new value and then that new value is carried forward until they decide to select another new valu and so on. Can anyone help with this? Thanks Tony -- Why don't my gre...

what is ^ in a formula for
full formula below where CJ 5 is a %age. Could do with knowing what symbol is? =($CJ$5+1)^(1/6)*CI7 -- DP The "^" operator can be used instead of POWER() to indicate to what power the base number is to be raised, such as in 5^2. This returns the result of a number raised to a power =POWER(5,2) -- Jacob (MVP - Excel) "DP" wrote: > full formula below where CJ 5 is a %age. Could do with knowing what symbol is? > > =($CJ$5+1)^(1/6)*CI7 > -- > DP hi the caret symbol(^) mean "raise to the power of" 2^3 means 2 cubed ...

Formula is....
I currently have the following conditional formatting in a cell range: COUNTIF(D4,D:D)=2 Now, I want to edit this formula so that it actually works out, if the same values appear two times or more in the column. At the moment, the cell will only change colour if same value appears twice. What I want it to do though is change if same value appears twice or more Thanks Further to the above, it doesn't seem to be working. Here is what I am using: Formula is=COUNTIF(D4,D:D)=2 Now, if I type in an address in D32, I want that one to be formatted too. So, do I have to conditional fo...

Copying Business Contact Manager data files
Copied the Business Contact Manager mdf and ldf files from my desktop to my laptop - a process I do with Outlook pst files when I travel and it works fine. However, with the BCM data files, the laptop seems connected to the BCM data files but all business accounts and contacts are empty. Am I missing something? ...