How do I have a cell update itself only if it's currently blank?

I need to track to retain the first value of a date field.  Each time
I load the worksheet the date field has the potential to change, I
need to keep track of the initial date in addition to the current date
that is in the field.

I have date columns called ASSIGNED and 1st ASSIGNED.  The initial
value of both will be blank.  At some point the ASSIGNED will be
populated with a date.  When it is populated for the first time I want
to save this value in the 1st ASSIGNED column.

I need something like:
   IF ( ISBLANK(ASSIGNED), "", ASSIGNED )
in my 1st Assigned cell, however, I do not want every value of
ASSIGNED.  I only want to update 1st Assigned if 1st Assigned does not
yet have a value.  I can't figure out how to put this kind of logic
together without creating a circular reference.

Can someone help?

0
7/30/2007 7:58:02 PM
excel 39879 articles. 2 followers. Follow

1 Replies
365 Views

Similar Articles

[PageSpeed] 17

> without creating a circular reference.

Go ahead and use a circular reference. Used properly and carefully, they can 
be quite useful.  On the Tools menu, choose Options, then the Calculation 
tab. There, check the Iteration box and set Maximum Iterations to 1. Then 
use a formula like the following in B1.

=IF(A1="",B1,IF(B1=0,A1,B1))

See http://www.cpearson.com/Newsletter/Content/2007_07_23.htm for more about 
circular references.


-- 
Cordially,
Chip Pearson
Microsoft MVP  - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


<reservations@alaskanchalet.com> wrote in message 
news:1185825482.913553.159690@z28g2000prd.googlegroups.com...
>I need to track to retain the first value of a date field.  Each time
> I load the worksheet the date field has the potential to change, I
> need to keep track of the initial date in addition to the current date
> that is in the field.
>
> I have date columns called ASSIGNED and 1st ASSIGNED.  The initial
> value of both will be blank.  At some point the ASSIGNED will be
> populated with a date.  When it is populated for the first time I want
> to save this value in the 1st ASSIGNED column.
>
> I need something like:
>   IF ( ISBLANK(ASSIGNED), "", ASSIGNED )
> in my 1st Assigned cell, however, I do not want every value of
> ASSIGNED.  I only want to update 1st Assigned if 1st Assigned does not
> yet have a value.  I can't figure out how to put this kind of logic
> together without creating a circular reference.
>
> Can someone help?
> 

0
chip1 (1821)
7/30/2007 8:22:25 PM
Reply:

Similar Artilces:

Lookup up in cells with formulas
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C4BC42.06922140 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I'm having a problem with the Lookup function. The cells that the lookup function is searching have a formula in them. = (SUM) This is buggering up the Lookup function. Is there a way for the Lookup = function to overlook the formulas and just search the results or hide = the formula in some way so the Lookup doesn't read them? Thanks in advance=20 Jen ------=_NextPart_000_0008_01C4BC42.0692214...

Finding Merged Cells
Is there any (easy) way of finding a cell or cells that have been merged? I am trying to sort a (very large) spreadsheet but can't because Excel "...requires the merged cells to be the same size..." I can always select all and remove the merge - but I am wondering why there are merged cells - hence the reason for trying to find them! Many Thanks - I like the macro solution - it certainly works on a test setup - I'll try it on the real spreadsheet tomorrow >-----Original Message----- >Manually, you could divide and conquer. Select half the range, hit ctrl-1 (t...

Quotes and Bank Updates over the Internet
I am running XP for Home. I upgraded from Money 2003 to 2004. While running Money 2003 I would get the latest stock prices for my holdings and all bank transactions from my bank. Since I upgraded I cannot. I cannot find where to point to my ISP (Comcast via Ethernet/Cable Modem). I can hot link from Money to the Web for Help. Help ...

Problem with Chart drawing blank cells as zero
Hi I am currently trying to create an excel line chart showing cumulative baseline vs Cumulative Actual vs forecast actual lines (the forecast line is a continuation of the Actual to date line) which I have done - but am trying to automate it so it is 'more user friendly' and can be created at the touch of a macro. The problem I am having is that one of the formulae i am using looks up a date in the timescale and if it is in the future I want it to leave the cell bank (using the "" option" - problem is the chart then decides this is not a blank cell it is a zero ...

pasting of variable cell value into macro
I have written a macro that runs an autofilter which grabs cell info from a different worksheet within that workbook (an entered date). It then uses that date to autofilter. It works fine the first time, but aparently plugs that info permanently into the macro and will not work on subsequent runs. I need a macro that will grab variable info from a certain cell each time and run with that new cell value each time. Any ideas?. (& yes, I am a newbee) sorry.. Your problem is refreshing the autofilter. It is best to clear the old filter before applying the new filter. Here ...

Updates are temporarily unavailable
I know many are having this problem with Yodlee, and I've asked before with no response. I cannot update 2 of my accounts that use Yodlee today. BUT I am able to occasionally. I've given up spending hours of creating new accounts, clearing Web site, etc etc... as instructed by tech. Now I find that on certain days it does indeed work. Can it be that Yodlee is shutting down? I find this error message listed under Money Sychronization. Both accounts cannot be synchronized. I was settling for doing my work manually, but when it works off and on like this I just wonder. I have a...

Multiplying cells
I am creating a spread sheet that with each Monday in a calendar year, I am needing to multiply 8 weeks, 13 weeks, and 26 weeks out. Please help =if(weekday(Today()) = 2, date+(8*7),"") format as date Change 8 to 13 and 26 for your other dates. if you need it to show these dates for the remainder of the week =Today()-(weekday(Today())-2)+(8*7) format as date. Again, use this formula and change 8 to 13, then 26 for the other dates. the date would actually advance a week on Sunday. -- Regards, Tom Ogilvy "jamie" <tscharbrough@quest-grp.com> wrote in message...

Automatic updates crippling legacy machine
I have a legacy 866MHz, 384Mbyte, XP/SP3 machine that has always run "well enough" however sometime in the last several months its responsiveness became awful most of the time. Using performance monitor I determined that "automatic updates" was driving it into a condition of more than 100 paging transactions/second rather continuously. When I disabled automatic updates the machine returned to its usual condition which is to say I can have a number of apps open and get reasonable performance. Has automatic updates changed in a significant way (either intenti...

Check the security update from MS Corp.
--wadrggrtzyh Content-Type: multipart/related; boundary="ipwfliboy"; type="multipart/alternative" --ipwfliboy Content-Type: multipart/alternative; boundary="adodlupkrh" --adodlupkrh Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Customer this is the latest version of security update, the "October 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to help maintai...

What is the warning triangle in a cell and how do I get rid of it.
I'm using the new Office 2004 for the Mac and first, I hate it! But, how do I get rid of the green (warning?) triangle in the cells. I haven't entered a equation and I have no idea where it came from. Please help. I don't have a Mac but you may be seeing the background error checking flag. If so, you can turn off this feature (in Windows versions at least) under Tools, Options, Error Checking. -- Jim "confused" <confused@discussions.microsoft.com> wrote in message news:71CF7B27-CD53-4E18-805A-7F8FFE79FB27@microsoft.com... > I'm using the new Office 2...

Locking cells #3
hi there, I was wondering if it is possible to Lock a cell so that no-one ca enter data into it, the reason behind this is because the cell i update from a different worksheet. Thank you for your time Kind Regards Swmasso -- Message posted from http://www.ExcelForum.com Hi It's possible. Simply protect the sheet - by default all cells are locked then. When you want to allow users to access some cells, unlock them before protecting the sheet (Format.Cells.Protection). When you want to restrict the access to some cells only, and to allow for rest of worksheet, then unlock all cells ...

Return a number in one cell to long hand text in another.
Ie. 123.65 to One hundred, Twenty Three & Sixty Five Cents Take a look at http://www.xldynamic.com/source/xld.xlFAQ0004.html -- HTH RP "CP" <CP@discussions.microsoft.com> wrote in message news:C0D9B8E3-3D66-4EA0-9184-57762BD663D6@microsoft.com... > Ie. 123.65 to One hundred, Twenty Three & Sixty Five Cents Go to Google and search the newsgroups for "spell number" On Wed, 20 Oct 2004 15:07:02 -0700, "CP" <CP@discussions.microsoft.com> wrote: >Ie. 123.65 to One hundred, Twenty Three & Sixty Five Cents ...

Blank e-mails #3
we have been receiving e-mails that have no date, no subject and no content. Our ISP says that this is an Outlook Express problem and to search support. We can't find anything? Dick <anonymous@discussions.microsoft.com> wrote: > we have been receiving e-mails that have no date, no > subject and no content. Our ISP says that this is an > Outlook Express problem and to search support. > We can't find anything? Ask in an Outlook Express newsgroup. Try REGSVR32 INETCOMM.DLL at a command line prompt. -- Brian Tillman ...

Cell Value as Named Range Reference
Little bit of a quirky question... Trying to use a cell value as a reference in a formula, where that cell value is the name of a named range. So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2. I want to get the correlation vale for A1:A3 and B1:B3 So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the formula: =correl(D1,D2). But I get an error. Have also tried using Indirect to no avail. Any help would be hugely appreciated. Thank you. =CORREL(INDIRECT(D1),INDIRECT(D2)) -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "ste...

automatically update budget list from source
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I created a checkbook worksheet similar to that of the ledger template given in excel. This is because I want to keep track of my checking account transactions here, but then separate them by the categories I give them. For example, if I have these categories: Car, Food, Supplies, Housing, then I want four separate budget lists (one for each category) to keep track of the expenses categorically. I assume these would be on four separate worksheets in a workbook, concluding with five worksheets (one for the sou...

How do I run a macro when a value occurs in a cell
I want to run a macro when a value appears in a cell. How can this be accomplished? Hi! This is a trivial example but you can build on it: Private Sub Worksheet_Change(ByVal Target As Range) If Range("D3") = 36 Then Range("D3").Interior.ColorIndex = 6 Else Range("D3").Interior.ColorIndex = 2 End If End Sub Put =A3*3 in D3 Try values such as 12, 10 in A3 Al -- Alf ----------------------------------------------------------------------- AlfD's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=478 View this thread: http://www.excelforu...

Office 2001 update 9.0.5
anyone...? -- ...rotinom siht edisni deppart m'I !!esaelp em pleH What's your question? On 10/27/04 12:57 PM, "Axel Hammerschmidt" wrote: > anyone...? -- Daiya Mitchell, MVP Mac/Word Word FAQ: http://www.word.mvps.org/ MacWord Tips: <http://www.word.mvps.org/MacWordNew/> What's an MVP? A volunteer! Read the FAQ: http://mvp.support.microsoft.com/ ...

Inventory Update. Purchase Order
Hi guys. We are implementing POs to our main product inventory. What I can figure out, is how the inventory is updated using POs. At the moment, all our inventory quantities are on order. How can I change these quantities to real existencies? Daniel: You have to perform receiving transactions on the POs. The receiving transaction updates inventory (both quantity and value) and creates a liability for the goods received. Refer to your GP Help guide as to how to perform these transactions. It's a bit of a lengthy process to go into here. Frank Hamelly MCP-GP, MCT, MVP East Coast Dyn...

IF formula & COLOURED CELL
I would like to use a formula if A1 (e.g.$15) is filled in yellow then A1*2%, if in green then A1*1%.....Is there any way to do that. TIA. -- littleps ------------------------------------------------------------------------ littleps's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25482 View this thread: http://www.excelforum.com/showthread.php?threadid=391785 Hi, Check out this site: 'OzGrid' (http://www.ozgrid.com/VBA/ReturnCellColor.htm) Regards, -- aristotl ----------------------------------------------------------------------- aristotle...

cell variables in IF formula
i have this formula (Thanks Biff) =IF(J13="","",IF(J13=0,"Stock not rated",IF(OR(J13={1,2}),"Sell",""))) now i want to add in further variable - if J13 has "cmt" or "divs" then the cell is blank, then if any of the above... thank you =IF(OR(J13={"","cmt","divs"}),"",IF(J13=0,"Stock not rated",IF(OR(J13={1,2}),"Sell",""))) -- Return email address is not as DEEP as it appears "Micayla Bergen" <MicaylaBergen@discussions.microsoft.com> wro...

How do I get my text to wrap in a single cell in exel?
I can't get all my text in a single cell, it starts across in to the cell next to it instead of starting a new text row below? Thanks Hi DJz, Select your cell and go to Format-->Cell. On the Alignment tab, check the checkbox labelled Wrap Text at the bottom of that dialogue window. HTH, Katherine "DJz" <DJz@discussions.microsoft.com> wrote in message news:97B925D0-FF0C-4CC9-AB23-9CB2D9AC2597@microsoft.com... > I can't get all my text in a single cell, it starts across in to the cell > next to it instead of starting a new text row below? Thanks Hi Kathe...

Finding cell text corresponding to the MAXimum figure in a spreadsheet?
Please can anyone help me with this... :) I've made a spreadsheet with the following attributes: Cells A1 to A100 contain a figure from 1-100 (a 'desirability' rating), sorted so that the highest figures are at the top of the spreadsheet and descend to the lowest figure in cell A100. Cells B1 to B100 contain the title of a CD album. Cells C1 to C100 contain the price of a CD album. I would like to make a formula that searches for the row that produces the highest result given from multiplying the figure in column A with the corresponding figure in column C and returns the co...

automatically import data from a cell in one workbook to another workbook
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel How do arrange for workbook A to import the data from a cell in workbook B? In other words, what formula (address?) should I enter in a cell in workbook A so that it will import the data in a cell from workbook B? This would be just another formula; start with =+ and then just use the mouse/trackpad to click on the referenced cell (assuming you have both wordbooks open. Be careful of relative/absolute references. Thanks, Mungo <br><br>Cheers <br><br>Karshish 1) Make sure both workbooks ...

Compare two files and update data from another file base on words in a cell separated by commas
I have two file with several colomns. I need to compare two Col B fileA Col B of FileB as shown in example. http://spreadsheets.google.com/ccc?key=0AgUVfFOnkiaKdFBiNDFLamcybXdhW... Each col have about 1000 rows. Each row contains thousands of words and phrases separated by Comma. As you can see from example, my data has soo many words and phrases separated by comma in each row of two colomn A and B. ============== i Need to merge data of corresponding row from COLA$FileB TO corresponding row of COLA$FileB Also merge data of corresponding row from COLB$FileB TO correspondin...

Password protect cells
I have a sheet that people need to enter data into, but I have a problem with people deleting the formulas in other cells. Does anyone know if there is a way to password protect one or several cells without protecting the entire sheet? You can lock cells or unlock cells (format|cells|protection tab). After you lock/unlock cells, you can protect the worksheet (tools|protection|protect sheet) When the worksheet is protected, the user won't be able to changed the locked cells, but will be able to change the values in the unlocked cells. There are other things that are disabled when the...