Need help with formula 01-13-10

```I am trying to adapt a formula in I2 from another spreadsheet that works
well, but won't in mine.  I've traced the error, but I would need help to
understand the help it gives!  My formula is this:  =IF(J2="0-Jan-00","To be
advised",WORKDAY(J2,1,NWD)).  I have a worksheet in the same workbook with a
list of non-workdays, and defined the column of dates with the name "NWD".
What I expect the formula to do is this:  If J2 is Feb. 4, it would give Feb.
5 in cell I2 because Feb. 5 is NOT a non-workday in NWD.  But if J2 is Feb.
5, and Feb. 6 and 7 are non-workdays in NWD, then it would give Feb. 8.  I
don't know how this formula works in the other spreadsheet, but it does.
Can anyone help?  Connie
```
 0
Utf
1/13/2010 6:26:02 PM
excel.worksheet.functions 4936 articles. 2 followers.

8 Replies
730 Views

Similar Articles

[PageSpeed] 20

```The first part of formula looks fishy. You ask it to check if the cell equals
the text string "0-Jan-00". I think what's actually happening is the cell is
formatted as a date, and it has a value of 0. In which case, the cell
actually has a value, not text. (as an example, try typing in a cell
(=J2="0-Jan-00"). You'll see that it says "FALSE").

Try this:
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

"Connie Martin" wrote:

> I am trying to adapt a formula in I2 from another spreadsheet that works
> well, but won't in mine.  I've traced the error, but I would need help to
> understand the help it gives!  My formula is this:  =IF(J2="0-Jan-00","To be
> advised",WORKDAY(J2,1,NWD)).  I have a worksheet in the same workbook with a
> list of non-workdays, and defined the column of dates with the name "NWD".
> What I expect the formula to do is this:  If J2 is Feb. 4, it would give Feb.
> 5 in cell I2 because Feb. 5 is NOT a non-workday in NWD.  But if J2 is Feb.
> 5, and Feb. 6 and 7 are non-workdays in NWD, then it would give Feb. 8.  I
> don't know how this formula works in the other spreadsheet, but it does.
> Can anyone help?  Connie
```
 0
Utf
1/13/2010 6:36:02 PM
```Hi Luke,
I tried the first thing you mentioned and it gives #VALUE!.  Then I tried
the formula you gave and it still gives #NUM!.  I tried then by leaving the
IF statement out and putting simply =WORKDAY(J2,1,NWD) and it still gives
#NUM!.  Connie

"Luke M" wrote:

> The first part of formula looks fishy. You ask it to check if the cell equals
> the text string "0-Jan-00". I think what's actually happening is the cell is
> formatted as a date, and it has a value of 0. In which case, the cell
> actually has a value, not text. (as an example, try typing in a cell
> (=J2="0-Jan-00"). You'll see that it says "FALSE").
>
> Try this:
> --
> Best Regards,
>
> Luke M
> *Remember to click "yes" if this post helped you!*
>
>
> "Connie Martin" wrote:
>
> > I am trying to adapt a formula in I2 from another spreadsheet that works
> > well, but won't in mine.  I've traced the error, but I would need help to
> > understand the help it gives!  My formula is this:  =IF(J2="0-Jan-00","To be
> > advised",WORKDAY(J2,1,NWD)).  I have a worksheet in the same workbook with a
> > list of non-workdays, and defined the column of dates with the name "NWD".
> > What I expect the formula to do is this:  If J2 is Feb. 4, it would give Feb.
> > 5 in cell I2 because Feb. 5 is NOT a non-workday in NWD.  But if J2 is Feb.
> > 5, and Feb. 6 and 7 are non-workdays in NWD, then it would give Feb. 8.  I
> > don't know how this formula works in the other spreadsheet, but it does.
> > Can anyone help?  Connie
```
 0
Utf
1/13/2010 6:50:01 PM
```Hi,
try

"Connie Martin" wrote:

> Hi Luke,
> I tried the first thing you mentioned and it gives #VALUE!.  Then I tried
> the formula you gave and it still gives #NUM!.  I tried then by leaving the
> IF statement out and putting simply =WORKDAY(J2,1,NWD) and it still gives
> #NUM!.  Connie
>
> "Luke M" wrote:
>
> > The first part of formula looks fishy. You ask it to check if the cell equals
> > the text string "0-Jan-00". I think what's actually happening is the cell is
> > formatted as a date, and it has a value of 0. In which case, the cell
> > actually has a value, not text. (as an example, try typing in a cell
> > (=J2="0-Jan-00"). You'll see that it says "FALSE").
> >
> > Try this:
> > --
> > Best Regards,
> >
> > Luke M
> > *Remember to click "yes" if this post helped you!*
> >
> >
> > "Connie Martin" wrote:
> >
> > > I am trying to adapt a formula in I2 from another spreadsheet that works
> > > well, but won't in mine.  I've traced the error, but I would need help to
> > > understand the help it gives!  My formula is this:  =IF(J2="0-Jan-00","To be
> > > advised",WORKDAY(J2,1,NWD)).  I have a worksheet in the same workbook with a
> > > list of non-workdays, and defined the column of dates with the name "NWD".
> > > What I expect the formula to do is this:  If J2 is Feb. 4, it would give Feb.
> > > 5 in cell I2 because Feb. 5 is NOT a non-workday in NWD.  But if J2 is Feb.
> > > 5, and Feb. 6 and 7 are non-workdays in NWD, then it would give Feb. 8.  I
> > > don't know how this formula works in the other spreadsheet, but it does.
> > > Can anyone help?  Connie
```
 0
Utf
1/13/2010 8:55:03 PM
```Odd. The #NUM error appears if the Start_Date + number of days yields an
invalid date. If J2 was not a valid date, you would get the #VALUE error, so
that's not the problem...What exactly is in J2?  If it's a formula, please
list that. Is it possible it's a negative number? If so, we could compensate
with:

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

"Connie Martin" wrote:

> Hi Luke,
> I tried the first thing you mentioned and it gives #VALUE!.  Then I tried
> the formula you gave and it still gives #NUM!.  I tried then by leaving the
> IF statement out and putting simply =WORKDAY(J2,1,NWD) and it still gives
> #NUM!.  Connie
>
> "Luke M" wrote:
>
> > The first part of formula looks fishy. You ask it to check if the cell equals
> > the text string "0-Jan-00". I think what's actually happening is the cell is
> > formatted as a date, and it has a value of 0. In which case, the cell
> > actually has a value, not text. (as an example, try typing in a cell
> > (=J2="0-Jan-00"). You'll see that it says "FALSE").
> >
> > Try this:
> > --
> > Best Regards,
> >
> > Luke M
> > *Remember to click "yes" if this post helped you!*
> >
> >
> > "Connie Martin" wrote:
> >
> > > I am trying to adapt a formula in I2 from another spreadsheet that works
> > > well, but won't in mine.  I've traced the error, but I would need help to
> > > understand the help it gives!  My formula is this:  =IF(J2="0-Jan-00","To be
> > > advised",WORKDAY(J2,1,NWD)).  I have a worksheet in the same workbook with a
> > > list of non-workdays, and defined the column of dates with the name "NWD".
> > > What I expect the formula to do is this:  If J2 is Feb. 4, it would give Feb.
> > > 5 in cell I2 because Feb. 5 is NOT a non-workday in NWD.  But if J2 is Feb.
> > > 5, and Feb. 6 and 7 are non-workdays in NWD, then it would give Feb. 8.  I
> > > don't know how this formula works in the other spreadsheet, but it does.
> > > Can anyone help?  Connie
```
 0
Utf
1/13/2010 8:55:11 PM
```On Jan 13, 6:50=EF=BF=BDpm, Connie Martin
<ConnieMar...@discussions.microsoft.com> wrote:
> Hi Luke,
> I tried the first thing you mentioned and it gives #VALUE!. =EF=BF=BDThen=
I tried
> the formula you gave and it still gives #NUM!. =EF=BF=BDI tried then by l=
eaving the
> IF statement out and putting simply =3DWORKDAY(J2,1,NWD) and it still giv=
es
> #NUM!. =EF=BF=BDConnie
>

You may have more than one possible error in your function Tie it down
and make sure you understand the WORKDAY() function as follows.

1 On a blank woeksheet enter 40194 & 40195 in two adjacent cells
this is saturday & sunday Jan 16 & 17 2010

2 In a cell enter     =3DWORKDAY(40194,1,40195)
this cell should return 40196 ie monday Jan 18 this shows the function
works

3 in the above cell in 2 enter the same formula but insread of 40195
enter the two cells of 1 by selecting 40195 in the function, then
dragging over the two cells in 1.
You should get 40196. This shows the function works with a non-working
day array

4 in the above cell in 2 select the 40194 in the function and click on
the cell in 1 with 40194 in it.
You should get the same result of 40196. This shows the function works
with a reference to the starting date.

5 Now select the two cells in 1 and name them NWD, Then enter NWD as
the third parameter in the formula in above cell in 2. This checks you
can use a named cell in the function.

In among the above you should find what you are doing wrong in your
real worksheet.

If you format the display of the cels in 1 as dd-mm-yyyy you should
see the values as dates.

Good hunting

Alan Lloyd

```
 0
alanglloyd
1/13/2010 8:57:12 PM
```There is a formula in J2.  It is:  =VLOOKUP(B3,NIRAV,10,0).  NIRAV is another
defined name worksheet.
The formula you gave here now gives me #VALUE!.
Connie

"Luke M" wrote:

> Odd. The #NUM error appears if the Start_Date + number of days yields an
> invalid date. If J2 was not a valid date, you would get the #VALUE error, so
> that's not the problem...What exactly is in J2?  If it's a formula, please
> list that. Is it possible it's a negative number? If so, we could compensate
> with:
>
>
> --
> Best Regards,
>
> Luke M
> *Remember to click "yes" if this post helped you!*
>
>
> "Connie Martin" wrote:
>
> > Hi Luke,
> > I tried the first thing you mentioned and it gives #VALUE!.  Then I tried
> > the formula you gave and it still gives #NUM!.  I tried then by leaving the
> > IF statement out and putting simply =WORKDAY(J2,1,NWD) and it still gives
> > #NUM!.  Connie
> >
> > "Luke M" wrote:
> >
> > > The first part of formula looks fishy. You ask it to check if the cell equals
> > > the text string "0-Jan-00". I think what's actually happening is the cell is
> > > formatted as a date, and it has a value of 0. In which case, the cell
> > > actually has a value, not text. (as an example, try typing in a cell
> > > (=J2="0-Jan-00"). You'll see that it says "FALSE").
> > >
> > > Try this:
> > > =IF(J2=0,"To be advised",WORKDAY(J2,1,NWD))
> > > --
> > > Best Regards,
> > >
> > > Luke M
> > > *Remember to click "yes" if this post helped you!*
> > >
> > >
> > > "Connie Martin" wrote:
> > >
> > > > I am trying to adapt a formula in I2 from another spreadsheet that works
> > > > well, but won't in mine.  I've traced the error, but I would need help to
> > > > understand the help it gives!  My formula is this:  =IF(J2="0-Jan-00","To be
> > > > advised",WORKDAY(J2,1,NWD)).  I have a worksheet in the same workbook with a
> > > > list of non-workdays, and defined the column of dates with the name "NWD".
> > > > What I expect the formula to do is this:  If J2 is Feb. 4, it would give Feb.
> > > > 5 in cell I2 because Feb. 5 is NOT a non-workday in NWD.  But if J2 is Feb.
> > > > 5, and Feb. 6 and 7 are non-workdays in NWD, then it would give Feb. 8.  I
> > > > don't know how this formula works in the other spreadsheet, but it does.
> > > > Can anyone help?  Connie
```
 0
Utf
1/13/2010 9:21:01 PM
```That gives me #VALUE!.  Does it have something to do with the date in J2
which is from a formula, which is:  =VLOOKUP(B3,NIRAV,10,0)?  The thing is,
the worksheet where this is all working perfectly is working from cells with
much more complicated formulas than this one.  Connie

"Eduardo" wrote:

> Hi,
> try
>
>
> "Connie Martin" wrote:
>
> > Hi Luke,
> > I tried the first thing you mentioned and it gives #VALUE!.  Then I tried
> > the formula you gave and it still gives #NUM!.  I tried then by leaving the
> > IF statement out and putting simply =WORKDAY(J2,1,NWD) and it still gives
> > #NUM!.  Connie
> >
> > "Luke M" wrote:
> >
> > > The first part of formula looks fishy. You ask it to check if the cell equals
> > > the text string "0-Jan-00". I think what's actually happening is the cell is
> > > formatted as a date, and it has a value of 0. In which case, the cell
> > > actually has a value, not text. (as an example, try typing in a cell
> > > (=J2="0-Jan-00"). You'll see that it says "FALSE").
> > >
> > > Try this:
> > > =IF(J2=0,"To be advised",WORKDAY(J2,1,NWD))
> > > --
> > > Best Regards,
> > >
> > > Luke M
> > > *Remember to click "yes" if this post helped you!*
> > >
> > >
> > > "Connie Martin" wrote:
> > >
> > > > I am trying to adapt a formula in I2 from another spreadsheet that works
> > > > well, but won't in mine.  I've traced the error, but I would need help to
> > > > understand the help it gives!  My formula is this:  =IF(J2="0-Jan-00","To be
> > > > advised",WORKDAY(J2,1,NWD)).  I have a worksheet in the same workbook with a
> > > > list of non-workdays, and defined the column of dates with the name "NWD".
> > > > What I expect the formula to do is this:  If J2 is Feb. 4, it would give Feb.
> > > > 5 in cell I2 because Feb. 5 is NOT a non-workday in NWD.  But if J2 is Feb.
> > > > 5, and Feb. 6 and 7 are non-workdays in NWD, then it would give Feb. 8.  I
> > > > don't know how this formula works in the other spreadsheet, but it does.
> > > > Can anyone help?  Connie
```
 0
Utf
1/13/2010 9:24:01 PM
```Everything worked fine in the steps you outlined.  My worksheet does not.
I'm still sitting with the error code.  It's exactly as it is in the other
spreadsheet and it works fine.  Won't in mine.  Connie

"alanglloyd@aol.com" wrote:

> On Jan 13, 6:50�pm, Connie Martin
> <ConnieMar...@discussions.microsoft.com> wrote:
> > Hi Luke,
> > I tried the first thing you mentioned and it gives #VALUE!. �Then I tried
> > the formula you gave and it still gives #NUM!. �I tried then by leaving the
> > IF statement out and putting simply =WORKDAY(J2,1,NWD) and it still gives
> > #NUM!. �Connie
> >
>
> You may have more than one possible error in your function Tie it down
> and make sure you understand the WORKDAY() function as follows.
>
> 1 On a blank woeksheet enter 40194 & 40195 in two adjacent cells
>    this is saturday & sunday Jan 16 & 17 2010
>
> 2 In a cell enter     =WORKDAY(40194,1,40195)
> this cell should return 40196 ie monday Jan 18 this shows the function
> works
>
> 3 in the above cell in 2 enter the same formula but insread of 40195
> enter the two cells of 1 by selecting 40195 in the function, then
> dragging over the two cells in 1.
> You should get 40196. This shows the function works with a non-working
> day array
>
> 4 in the above cell in 2 select the 40194 in the function and click on
> the cell in 1 with 40194 in it.
> You should get the same result of 40196. This shows the function works
> with a reference to the starting date.
>
> 5 Now select the two cells in 1 and name them NWD, Then enter NWD as
> the third parameter in the formula in above cell in 2. This checks you
> can use a named cell in the function.
>
> In among the above you should find what you are doing wrong in your
> real worksheet.
>
> If you format the display of the cels in 1 as dd-mm-yyyy you should
> see the values as dates.
>
> Good hunting
>
> Alan Lloyd
>
> .
>
```
 0
Utf
1/15/2010 3:23:01 PM

Similar Artilces:

macro needed
hello listers to begin with i have no great excel skills what I am looking for is a macro that I can easily use to send mail to a list of users I have made a web page that students sign up on http://biosciences.utoledo.edu/prs enter there info when they press submit the data is saved to a text file that can be opened as a csv file in excel I would like to have a macro go row by row down the page and extract the email from column G and insert that in to an email and also the students name from coulmn B. The csv file is set up like this ID Fname Lname StudentID TransmitID Section e...

Office X 10.1.6 Update: Fonts missing?
> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3182711903_8674241 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit After updating to 10.1.6, some fonts are missing (KidPrint, Lucida handwriting, etc.) Any ideas? --B_3182711903_8674241 Content-type: text/html; charset="US-ASCII" Content-transfer-encoding: quoted-printable <HTML> <HEAD> <TITLE>Office X 10.1.6 Update: Fonts missing?</TITLE> </HEAD> <BODY> <FON...

Find Next Row With No Value In It
I've got this nextrow = Cells.Find(what:="*", searchdirection:=xlPrevious, searchorder:=xlByRows).Row + 1 Cells(nextrow, 1).Select which will find nextrow with nothing in it and select cell in Col A, but now I've got formulas in the cells, so instead of finding the next row with nothing in it, I want to find the next row with no value - even if it's got a formula in it. Can I do this by amending what's in the what:="*" part of this code? It's always better to include all the parms for .find(). If you don't, then you're at ...

Need VB code to edit cell values
I need some VB to do the following please (My VB is not good enough yet) Starting from the active cell (call it the StartCell) In the cell to the left.. ThreeChars = Value.right(3) (the last three chars) Value = Value - last three characters (assume value is text & at least 3 chars present) In StartCell.. value = ThreeChars (as text, overwrite any contents of StartCell) move active cell one down from StartCell Examples of before/after -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Stephen Ford" <Stephen_Ford_no@spam_uwclub....

Exchange and Windows 2003 I cannot get the Exchange Routing engine to start up. Upon startup I get an event ID 7023 telling me that the path is not found. Everything else works and starts up just fine, people can open mailboxes, but cannot send/receive emails. I read online that it sounds like the metabase.bin file is corrupted. I uninstalled/reinstalled IIS, reinstalled Exchange 2003, and have reinstalled Exchange 2003 SP1, everything works except that I still cannot get the routing engine to kick back on. Any suggestions! Thanks! Was this an OEM preinstall? I recently bought a ser...

Help Microsoft Office 2003
After now using Microsoft Office 2003 for about 3 months now, have suddenly noticed that my email messages have just started showing strange symbols for certain punctuation. This does not show as I type... but the recipent sees these symbols when the message is received. I can see it when they reply back. The strange symbols show for the apostrophe and sometimes for the exclaimation. I do not recognize the symbols. I do not have caps lock on, do have number lock on. None of the symbols show on my keys. Help?! Thanks! Kim it's caused by character encoding. What email client and...

.pst folder help
How do I put a .pst folder in My Documents for constant up to date copying? Mine seems to be in a folder called Office Data File which I cannot open but do not see any pst file...can someone kindly help me out here? Thanks! Right click the pst in outlook and select properties and then click Advanced You will see the location of the file. Depending on your view settings it may be hidden. Close the pst, (right click it, close) Move the file to the location you desire. Open Outlook select File - Open - Outlook Data File Browse to the file and select it. OK Done Regards Mark Dormer &quo...

Help with macro to choose printer
Hi, I am new to macros in Excel and this is what I did to print a worksheet: ------------------------------------------------------------------------ Range("A1:W35").Select ActiveSheet.PageSetup.PrintArea = "\$A\$1:\$W\$35" Selection.PrintOut From:=1, To:=1, Copies:=1, Collate:=True End Sub ------------------------------------------------------------------------ Works great except that I want to select the printer to use and the macro won't stop at the point to choose printer. Any way to work around this? Any help is greatly appreciated. Emilio Emilio,this wil...

Help with an expression to calculate a profit from one of three fi
Using Access 2003 SP3 - in Forms I know I'm probably asking a very complex question, so a very big THANK YOU to whomever can help. I am doing a detailed inventory and need help with an expression in my form that will calculate my profit based on one of three fields. Only one field would have the end data. To clarify what I am working with: Most stock items come by the "unit" and contain multiple "subunits" which, in turn, contain smaller "pieces." However, some "units" only come with "subunits" and some only come as &quo...

Printing checks #13
When i print a check in money deluxe 2005, It put the payee name on the pay to the order of line but also prints it below this line. Id this acceptable? and is there anyway to stop it from printing the payee name twice? Go to Tools, Settings, Print Checks and uncheck Print payee name in first line of address field. "vermonter" <vermonter@discussions.microsoft.com> wrote in message news:FC9A904E-A691-4DF3-9088-48D4130D6A51@microsoft.com... > When i print a check in money deluxe 2005, It put the payee name on the pay > to the order of line but also prints it below this...

how do you formula's but leave the value.
hi,how do you formula's but leave the value. if a1=1 and b1=2 and you have a code in c1 that says =a1+b1 c1 value would = 3 but how do you then delete the formula so that is stays as 3. i hope you understand what i mean. Hi Craig copy (don't cut) C1 and then click on C1 again and choose Edit / Paste Special now choose values and click Ok this should give you what you want Regards julie "craig" <anonymous@discussions.microsoft.com> wrote in message news:386401c4013b\$71f004b0\$a601280a@phx.gbl... > hi,how do you formula's but leave the value. > if a1=1 ...

Help with Lookup #2
I have an excel worksheet that has approximately 20,000+ records. I would like to classify these recorded based on two columns. The first column's name is "YB Description" and the other is "Sold Date". The YB column mainly contains 4 digit numerical values, with approximately 300-400 records that are text, ie. "classified", "not verified", "public use". The Sold Date column is pretty self-explanatory, all the records are dates with the following formatting - M/D/Y. What I would like to do is create a third column called YB/Sold that classi...

recurring meetings 03-26-10
How do I transfer ownership of a recurring meeting on my Outllok calendar? "Janet Hi-Stat" <Janet Hi-Stat@discussions.microsoft.com> wrote in message news:9674837E-DFF8-409C-8621-E7675D8986A5@microsoft.com... > How do I transfer ownership of a recurring meeting on my Outllok calendar? You can't. You must delete the existing event and the new owner must create the replacement. -- Brian Tillman [MVP-Outlook] ...

extend.dat 05-22-10
Since MS Security Essentials was NOT able to prevent me about the virus Pushbot.gen!C (it has accused its presence, but did not remove it), I don´t get to start Outlook 2007. The message it shows is "Outlook was not able to open extend.dat file". The next message is "you don´t have the permission to open the C:\users\xxx\AppData\Local\Microsoft\Outlook\Outlook.pst". I have administrative privileges and use Windows 7 OS. I´ve des- and re-installed Outlook twice and the same error appears. I´d appreciate a help on this subject. Thanks --- Felix Felix ...

Need a code
I need a code that does the following: If C3>D3, B3 value will start blink and if C3<D3, E3 value will start blink. Any help please. Thanks in advance. Personally, I would recommend against blinking text, but if you insist on employing it, this website should help you get started... http://www.cpearson.com/Excel/BlinkingText.aspx NOTE: Pay particular attention to the second of the two points delineated in the Summary section at the bottom of the webpage. -- Rick (MVP - Excel) "MAX" <MAX@discussions.microsoft.com> wrote in message news:C7E6...

Help with ACT! to CRM migration
Hi to anyone that can help. I am trying to do a trial data migration from our ACT! database to MSCRM to see what it will look like. I have installed the migration framework and appear to have CRM running fine on a server in our network (no errors on install and all windows appear ok). Since I do not have a strong database background I was wondering if someone could confirm what I believe is MS's way of migrating my data accross. I have exported the data from ACT! to a text file. I imported this into excel and repleaced the Free/busy data with a unique number for each record. I the...

duplicate records 10-26-05
Hi, How can i make a field as primary? For Example: in the account record, can i make the account name as Priamry. How can we control duplicate records in CRM? like cases, contacts, accounts etc. Do we need any customization? What tools can we use to accomplish this? Thanks Dev In CRM 1.2 you can not change the primary field and there is no duplicate checking. There are 3rd party tools that can do searches for duplicates and let you merge them, but nothing really effective at preventing duplicate records. For CRM 3.0 there is an example in the SDK of how to create a custom duplicat...

Minutes and Seconds Formula
I'm developing a workout programs for runners. I need a spreadsheet that will allow a user to enter min/seconds that is their target for running a mile. Then I need a formula that will provide intervals at every 16th of the mile of what there time should be. Any help. I thought i knew how to format to show min/sec but i'm having all kinds of problems THANKS! Put your target time in A20, and in B20 add =\$A\$20*COLUMN(A20)/16, then copy across to Q209. I input a target time of 4:32, and got values of 00:17, 00:34,00:52, etc. <vbg> -- HTH RP (remove nothere from the em...

How do you convert a formula cell to a constant cell
I use a formula to calculate the value, such as time difference, and want to save the value not the formula. The value (constant) will be used later to adjust both time cells used as arguments in the original formula--thus the need to preserve the value computed rather than the formula. After calculation, copy the cell with the formula then Edit - Paste Special - Values on the same cell. HTH "JQLogan" wrote: > I use a formula to calculate the value, such as time difference, and want to > save the value not the formula. The value (constant) will be used later to > ...

Experts Needed for Color Coding
Hello: this ought to be a challenge. I have a spreadsheet with 10 columns Col A contains an ID There are various color coded cells in each; Row 1: B - C are red; F is yellow Row 2: C is green; E is yellow, etc. Row count could be up to 300 Objective: Before refreshing this report (from Access query) 1. identify cell color for cells in rows 2. After refresh, find row (per the ID field) and replicate the color Conditions: New unique rows may be added during the refresh; these will not be color coded The color is applied without consistent rules in many cases Rather than refreshing the repo...