need some help with: formatting of x2 dates in 1 cell

Hi and thanks to anyone who reads this.

I have a worksheet which contains two columns of dates.
In a second worksheet i have a column which adds the two dates together
as TEXT and ommits dates which are blank which works perfectly,
however:

I would like to know how i could format each of the 2 dates in the 1
cell to have different font colors?

Here is my existing cell formula:

=IF('Data'!E2=0,"",(TEXT('Data'!E2,"dd/mm/yy"))&" 
"&IF('Data'!F2=0,"",TEXT('Data'!F2,"dd/mm/yy")))

I have a feeling its not conditional formating, as that won't allow me
to use formulas across different worksheets, and i've tried for ages
with custom cell formatting too.

Any ideas would be greatly appreciated  :)


-- 
NickPR
------------------------------------------------------------------------
NickPR's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27376
View this thread: http://www.excelforum.com/showthread.php?threadid=468944

0
9/19/2005 7:26:39 PM
excel 39879 articles. 2 followers. Follow

12 Replies
549 Views

Similar Articles

[PageSpeed] 40

Well it seems i cannot use formatting in this way on anything but text
strings. Does anybody know a way to apply VB to this situation please?

thanks.


-- 
NickPR
------------------------------------------------------------------------
NickPR's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27376
View this thread: http://www.excelforum.com/showthread.php?threadid=468944

0
9/19/2005 8:05:30 PM
On Mon, 19 Sep 2005 14:26:39 -0500, NickPR
<NickPR.1vmgua_1127160304.4228@excelforum-nospam.com> wrote:

>
>Hi and thanks to anyone who reads this.
>
>I have a worksheet which contains two columns of dates.
>In a second worksheet i have a column which adds the two dates together
>as TEXT and ommits dates which are blank which works perfectly,
>however:
>
>I would like to know how i could format each of the 2 dates in the 1
>cell to have different font colors?
>
>Here is my existing cell formula:
>
>=IF('Data'!E2=0,"",(TEXT('Data'!E2,"dd/mm/yy"))&" 
>"&IF('Data'!F2=0,"",TEXT('Data'!F2,"dd/mm/yy")))
>
>I have a feeling its not conditional formating, as that won't allow me
>to use formulas across different worksheets, and i've tried for ages
>with custom cell formatting too.
>
>Any ideas would be greatly appreciated  :)

As far as I know, you can only have different characters in the same cell
formatted with different font colors if the contents of the cell is a text
string.

That means that, instead of having a formula in the cells on your worksheet,
you would need to run a VBA Sub that would read the data from 'Data!, and then
format it appropriately.

Something like:

===================================
Sub CombineDates()
Dim Source As Range
Dim Target As Range
Dim i As Long

Set Source = Worksheets("Data").Range("E2:F10")
Set Target = Worksheets("Sheet2").Range("A2:A10")

For i = 1 To Source.Rows.Count
    Target(i, 1).NumberFormat = "@"
    If IsDate(Source(i, 1)) Then
        Target(i, 1) = Format(Source(i, 1), "dd/mm/yy")
    Else: Target(i, 1) = ""
    End If
    If IsDate(Source(i, 2)) Then
        Target(i, 1) = Target(i, 1) & " " & Format(Source(i, 2), "dd/mm/yy")
    End If
    Target(i, 1) = Trim(Target(i, 1))
    With Target(i, 1)
        .Characters(1, 8).Font.Color = vbRed
        .Characters(10, 8).Font.Color = vbGreen
    End With
Next i
 
End Sub
=======================


--ron
0
ronrosenfeld (3122)
9/19/2005 9:47:14 PM
Ron, that was perfect, thankyou, your a star! :

--
NickP
-----------------------------------------------------------------------
NickPR's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2737
View this thread: http://www.excelforum.com/showthread.php?threadid=46894

0
9/20/2005 7:37:55 AM
would there be a way to have the macro run every 5min? So that it keeps
up-to- date with the source data Worksheet?

Thanks again,

Nick.


-- 
NickPR
------------------------------------------------------------------------
NickPR's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27376
View this thread: http://www.excelforum.com/showthread.php?threadid=468944

0
9/20/2005 7:59:25 AM
On Tue, 20 Sep 2005 02:59:25 -0500, NickPR
<NickPR.1vne6b_1127203502.9579@excelforum-nospam.com> wrote:

>
>would there be a way to have the macro run every 5min? So that it keeps
>up-to- date with the source data Worksheet?
>
>Thanks again,
>
>Nick.

If your goal is to keep things up to date, then an event driven macro might be
better.  You could use the worksheet change event which would trigger if you
changed a cell in Source.

For example:

============================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aoi As Range
Set aoi = Worksheets("Data").Range("$E$2:$F$10")

If Not Intersect(Target, aoi) Is Nothing Then
    CombineDates
End If

End Sub
===========================

To enter this sort of macro, right-click on the sheet tab for your Data
worksheet and select View Code from the right-click menu.

Then paste the code into the window that opens.


--ron
0
ronrosenfeld (3122)
9/20/2005 12:16:05 PM
Ron Rosenfeld Wrote: 
> On Tue, 20 Sep 2005 02:59:25 -0500, NickPR
> <NickPR.1vne6b_1127203502.9579@excelforum-nospam.com> wrote:
> 
> >
> >would there be a way to have the macro run every 5min? So that it
> keeps
> >up-to- date with the source data Worksheet?
> >
> >Thanks again,
> >
> >Nick.
> 
> If your goal is to keep things up to date, then an event driven macro
> might be
> better.  You could use the worksheet change event which would trigger
> if you
> changed a cell in Source.
> 
> For example:
> 
> ============================
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim aoi As Range
> Set aoi = Worksheets("Data").Range("$E$2:$F$10")
> 
> If Not Intersect(Target, aoi) Is Nothing Then
> CombineDates
> End If
> 
> End Sub
> ===========================
> 
> To enter this sort of macro, right-click on the sheet tab for your
> Data
> worksheet and select View Code from the right-click menu.
> 
> Then paste the code into the window that opens.
> 
> 
> --ron

Works perfectly with number fields, thanks again :D

With regards to the formatting of colors, to extend what you have done,
i am trying to add together a date with 3 other columns of text. Here is
what i have so far:


Code:
--------------------
    
  Sub CombineInitData()
  Dim Source1 As Range
  Dim Source2 As Range
  Dim Source3 As Range
  Dim Source4 As Range
  Dim Target As Range
  
  Dim ab As Long
  Dim bc As Long
  Dim cd As Long
  
  
  Dim i As Long
  
  Set Source1 = Worksheets("Data").Range("A2:A16")
  Set Source2 = Worksheets("Data").Range("B2:B16")
  Set Source3 = Worksheets("Data").Range("C2:C16")
  Set Source4 = Worksheets("Data").Range("D2:D16")
  Set Target = Worksheets("Worksheet1").Range("A2:A16")
  
  For i = 1 To Source1.Rows.Count
  
  If IsDate(Source4(i, 1)) Then
  Target(i, 1) = Format(Source4(i, 1), "dd/mm/yy")
  Else: Target(i, 1) = ""
  End If
  
  If IsNull(Source2(i, 1)) Then
  Target(i, 1) = ""
  Else: Target(i, 1) = Target(i, 1) & " / " & (Source2(i, 1)) & " / " & (Source3(i, 1)) & " / " & (Source4(i, 1))
  End If
  
  Target(i, 1) = Trim(Target(i, 1))
  With Target(i, 1)
  .Characters(1, ab).Font.ColorIndex = 10
  .Characters(ab, bc).Font.ColorIndex = 3
  .Characters(bc, cd).Font.ColorIndex = 5
  End With
  
  Next i
  
  End Sub
  
--------------------


Am i assuming correctly that i need to count the number of chars from
each source to ditermin the number of characters to trim from and to?

Many thanks for your help,

Nick

Edit: Just trying the following doesn't seem to work either: 

Set ab = Source1.Characters.Count

Would an array be required?


-- 
NickPR
------------------------------------------------------------------------
NickPR's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27376
View this thread: http://www.excelforum.com/showthread.php?threadid=468944

0
9/20/2005 1:36:17 PM
Well this is my latest code and it doesn't seem to work. No errors
though..

Sub CombineInitData()
Dim Source1 As Range
Dim Source2 As Range
Dim Source3 As Range
Dim Source4 As Range
Dim Target As Range

Dim ab As String
Dim bc As Long
Dim cd As Long


Dim i As Long

Set Source1 = Worksheets("Data").Range("A2:A16")
Set Source2 = Worksheets("Data").Range("B2:B16")
Set Source3 = Worksheets("Data").Range("C2:C16")
Set Source4 = Worksheets("Data").Range("D2:D16")
Set Target = Worksheets("Worksheet1").Range("A2:A16")




For i = 1 To Source1.Rows.Count

ab = Source1(i, 1).Characters.Count

If IsDate(Source4(i, 1)) Then
Target(i, 1) = Format(Source4(i, 1), "dd/mm/yy")
Else: Target(i, 1) = ""

End If


If IsNull(Source2(i, 1)) Then
Target(i, 1) = ""
Else: Target(i, 1) = Target(i, 1) & " / " & (Source2(i, 1)) & " / " &
(Source3(i, 1)) & " / " & (Source4(i, 1))

End If

Target(i, 1) = Trim(Target(i, 1))
With Target(i, 1)
..Characters(1, ab).Font.ColorIndex = 10
..Characters(ab, bc).Font.ColorIndex = 3
..Characters(bc, cd).Font.ColorIndex = 5
End With

Next i

End Sub


-- 
NickPR
------------------------------------------------------------------------
NickPR's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27376
View this thread: http://www.excelforum.com/showthread.php?threadid=468944

0
9/20/2005 3:04:18 PM
On Tue, 20 Sep 2005 08:36:17 -0500, NickPR
<NickPR.1vnuuf_1127225105.2462@excelforum-nospam.com> wrote:

>Am i assuming correctly that i need to count the number of chars from
>each source to ditermin the number of characters to trim from and to?

I used the Trim function to remove the leading <space> that would appear in my
routine if there was nothing in the first column of Source.  So the answer is
no; and you may not even require it.

I was using it because in my testing, if Target contained only a single date,
Excel might convert it to an Excel date, rather than a text string, unless
there was something that made that impossible (like a leading space).

>
>Many thanks for your help,
>
>Nick
>
>Edit: Just trying the following doesn't seem to work either: 
>
>Set ab = Source1.Characters.Count
>
>Would an array be required?

Since ab is not an object, you would not use the Set statement.  Try just a
simple equality:

ab = Len(Source1(i,1).text)


--ron
0
ronrosenfeld (3122)
9/20/2005 3:07:43 PM
On Tue, 20 Sep 2005 10:04:18 -0500, NickPR
<NickPR.1vnxmf_1127228705.2675@excelforum-nospam.com> wrote:

>
>Well this is my latest code and it doesn't seem to work. No errors
>though..

What does "doesn't seem to work" mean?

I'm very surprised that you see no error messages.

I see both logic and syntax errors, and I don't know what your source data
looks like.

Why is ab Dim'd as String?

You never set bc or cd to anything, so they will effectively be zero(0).

Where you set the ColorIndex property of Characters, you don't specify any
Object for Characters to be a Property of (need to put '.'s in front of the
Characters to have them be a property of Target(i,1)



HTH,
  --ron


>
>Sub CombineInitData()
>Dim Source1 As Range
>Dim Source2 As Range
>Dim Source3 As Range
>Dim Source4 As Range
>Dim Target As Range
>
>Dim ab As String
>Dim bc As Long
>Dim cd As Long
>
>
>Dim i As Long
>
>Set Source1 = Worksheets("Data").Range("A2:A16")
>Set Source2 = Worksheets("Data").Range("B2:B16")
>Set Source3 = Worksheets("Data").Range("C2:C16")
>Set Source4 = Worksheets("Data").Range("D2:D16")
>Set Target = Worksheets("Worksheet1").Range("A2:A16")
>
>
>
>
>For i = 1 To Source1.Rows.Count
>
>ab = Source1(i, 1).Characters.Count
>
>If IsDate(Source4(i, 1)) Then
>Target(i, 1) = Format(Source4(i, 1), "dd/mm/yy")
>Else: Target(i, 1) = ""
>
>End If
>
>
>If IsNull(Source2(i, 1)) Then
>Target(i, 1) = ""
>Else: Target(i, 1) = Target(i, 1) & " / " & (Source2(i, 1)) & " / " &
>(Source3(i, 1)) & " / " & (Source4(i, 1))
>
>End If
>
>Target(i, 1) = Trim(Target(i, 1))
>With Target(i, 1)
>Characters(1, ab).Font.ColorIndex = 10
>Characters(ab, bc).Font.ColorIndex = 3
>Characters(bc, cd).Font.ColorIndex = 5
>End With
>
>Next i
>
>End Sub

--ron
0
ronrosenfeld (3122)
9/20/2005 4:37:01 PM
ok, so since using Len() it now appears to be modifying the text Target,
but in an odd way... When i first applied formatting to Source1, i had
to + 2 to make it goto the correct position. Is that due to Len not
counting white spaces?

Then, by the time i had got to entering the 4th Source formatting, the
entire output had color applied but all out of place, not even
back-to-front (see attachment).

Heres the code for it:

Code:
--------------------
    
  Target(i, 1) = Trim(Target(i, 1))
  ab = Len(Source1(i, 1).Text)
  bc = Len(Source2(i, 1).Text)
  cd = Len(Source3(i, 1).Text)
  de = Len(Source4(i, 1).Text)
  With Target(i, 1)
  .Characters(1, ab).Font.ColorIndex = 5
  .Characters(ab, bc).Font.ColorIndex = 10
  .Characters(bc, cd).Font.ColorIndex = 3
  .Characters(cd, de).Font.ColorIndex = 7
  
  End With
  
--------------------


Anyway, thanks for your help today, i was totally stuck before this, at
least now i have something to work on :)


+-------------------------------------------------------------------+
|Filename: sample.gif                                               |
|Download: http://www.excelforum.com/attachment.php?postid=3842     |
+-------------------------------------------------------------------+

-- 
NickPR
------------------------------------------------------------------------
NickPR's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27376
View this thread: http://www.excelforum.com/showthread.php?threadid=468944

0
9/20/2005 5:04:06 PM
On Tue, 20 Sep 2005 12:04:06 -0500, NickPR
<NickPR.1vo36c_1127235904.2873@excelforum-nospam.com> wrote:

>
>ok, so since using Len() it now appears to be modifying the text Target,
>but in an odd way... When i first applied formatting to Source1, i had
>to + 2 to make it goto the correct position. Is that due to Len not
>counting white spaces?
>
>Then, by the time i had got to entering the 4th Source formatting, the
>entire output had color applied but all out of place, not even
>back-to-front (see attachment).
>
>Heres the code for it:
>
>Code:
>--------------------
>    
>  Target(i, 1) = Trim(Target(i, 1))
>  ab = Len(Source1(i, 1).Text)
>  bc = Len(Source2(i, 1).Text)
>  cd = Len(Source3(i, 1).Text)
>  de = Len(Source4(i, 1).Text)
>  With Target(i, 1)
>  .Characters(1, ab).Font.ColorIndex = 5
>  .Characters(ab, bc).Font.ColorIndex = 10
>  .Characters(bc, cd).Font.ColorIndex = 3
>  .Characters(cd, de).Font.ColorIndex = 7
>  
>  End With
>  
>--------------------
>
>
>Anyway, thanks for your help today, i was totally stuck before this, at
>least now i have something to work on :)
>

I'm having some issues with my virus protection program so don't download
attachments, but:

LEN counts any printing character, included <space>, if it is there.  But if
you are checking LEN in Source, and then adding <space>'s in Target, you can
easily get out of sync unless you account for them.

As a matter of fact, it might be easier to debug if you stored your text string
in a temporary string variable, before writing it to Target.  Also you might
not need some of the stuff I added to ensure that Target would not reformat my
results.


--ron
0
ronrosenfeld (3122)
9/20/2005 5:54:47 PM
Ron, you're the best! :D

Heres what i have working now:


Sub CombineInitData()
Dim Source1 As Range
Dim Source2 As Range
Dim Source3 As Range
Dim Source4 As Range
Dim Target As Range
Dim i As Long
Dim testing As String

Set Source1 = Worksheets("Data").Range("A2:A16")
Set Source2 = Worksheets("Data").Range("B2:B16")
Set Source3 = Worksheets("Data").Range("C2:C16")
Set Source4 = Worksheets("Data").Range("D2:D16")
Set Target = Worksheets("Worksheet1").Range("A2:A16")

For i = 1 To Source1.Rows.Count

'If IsNull(Source2(i, 1)) Then
'Target(i, 1) = ""
'Else: Target(i, 1) = Source1(i, 1) & " / " & (Source2(i, 1) & " / " &
(Source3(i, 1) & " / " & (Source4(i, 1))))
'End If

testing = Source1(i, 1) & " / " & Source2(i, 1) & " / " & Source3(i, 1)
& " / " & Format(Source4(i, 1), "dd/mm/yy")
Target(i, 1) = testing
Target(i, 1) = Trim(Target(i, 1))
ab = Len(Source1(i, 1).Text) + 3
bc = Len(Source2(i, 1).Text) + 3
cd = Len(Source3(i, 1).Text) + 3
de = Len(Source4(i, 1).Text) + 3
With Target(i, 1)
Characters(1, ab).Font.ColorIndex = 5
Characters(ab, bc).Font.ColorIndex = 10
Characters(ab + bc, cd).Font.ColorIndex = 3
Characters(ab + bc + cd, de).Font.ColorIndex = 7

End With

Next i

End Sub
======================

I had to append the previous number to the next to get the correct
starting point for .Characters(). Your idea about debugging to a string
got me thinking straight :)

Now working on removing the / / / when fields have no data and im done!
:D :D :D


-- 
NickPR
------------------------------------------------------------------------
NickPR's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27376
View this thread: http://www.excelforum.com/showthread.php?threadid=468944

0
9/20/2005 7:44:35 PM
Reply:

Similar Artilces:

Need Help, Task Start Date is wrong
I’m using MS Project 2007, have several task linked with finish to start. I have set date to schedule from, hours per day set to 8 and Working Monday thru Friday. My schedule shows Task 1 Duration 4 days, start Wed 6/2/10, Finish Mon 6/7/10 Task 2 Duration 3 days, start Mon 6/7/10, Finish Thu 6/10/10 Task 2 should have a Start Date of 6/8/10 not 6/7/10; what is causing this? Thanks in advance for your help. ...

Changing Cells and entering data in them
Thanks for the help again. Big thanks to Steve you've got me this far. I went out and bought a book, but it's like reading a foreign language. I was informed today that I can't have message boxes come up. I need to have the code point at the cells and if they are blank turn which ever one is blank red or if both are then both turn red then pause for each cell to be filled in. Cell F14 "Last Name" then automatically go to Cell F16 "First Name" on tab or enter. Basically if Cell F22 or F23 has an X in it, Cells F14 an F16 turn red and cell F14 has the focus...

New to excel
Hi All, I'm new to Excel ( and to this forum :) ) and so I hope somebody may b able to help me. I've got 2 questions.... QUESTION 1 I've got a spreadsheet which takes data from one worksheet and uses i to calculate data in a second worksheet using the following code formula: =IF('4th November 2005'!B19="","nothing here dude",IF(B19<'4th Novembe 2005'!B19,"UP",IF(B19='4th November 2005'!B19,"Same",IF(B19>'4t November 2005'!B19,"DOWN")))) The problem is, when I create a new worksheet I have...

Looking for Excel Help
I'm a very novice Excel user and am looking for a little help with creating a formula for a spreadsheet I'm creating for my personal use. I would appreciate some assistance if possible. Thanks in advance. Dan --- Message posted from http://www.ExcelForum.com/ Hi Dan! Post a sample of what you want to do. Your question is just a tad open ended <g> -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "DanB4105" <DanB4105.ywtpa@excelfor...

conditional formatting sort
Is there a way to sort after conditional formatting. So, if I've set up a conditional format to color my text red if the number is between 0 and 95, then I want to sort so all red numbers are at the top of the spreadsheet? Any ideas? Thanks for your time and assistance. Roberta On Thu, 25 Oct 2007 05:08:00 -0700, rrupp <rrupp@discussions.microsoft.com> wrote: You can't sort by color, but you can add a column to your query like this: SortColumn: Iif(SomeValue >= 0 and SomeValue <= 95, 1, 2) Then sort by this column. -Tom. >Is there a way to sort after condition...

Please help #8
I have Exchange 2000 and Outlook 2003 in Cache mode. Has anyone seen this or know how to fix it? Task 'Microsoft Exchange Server' reported error (0x8007000E) : 'Out of memory or system resources. Close some windows or programs and try again.' "John S" wrote: > > I have Exchange 2000 and Outlook 2003 in Cache mode. Has anyone seen this or > know how to fix it? > > Task 'Microsoft Exchange Server' reported error (0x8007000E) : 'Out of > memory or system resources. Close some windows or programs and try again.' > >...

default values in a cell
Hello, can you help me please Cell B1 contains a complex mathematical formula which requires (in several places) a number from cell A1. Cell A1 can contain any integer number, but it is usually the same (30). I would like to be able to leave cell A1 empty, and only enter a number when it is not 30 , ie the default value of A1 is 30, unless specified. How do I go about this ? Should I look at conditional formatting, or put lots of IF functions into an already complex formula? Thank as always KK Use 2 cells Modify the complex formula to use B1 rather than A1 ( or any other un-u...

Show date, time & day of week in one cell
Can I show the date, time and day of week in one cell. I have: 09/03/07 8:30 AM in one cell using the format (Format/Cells/Custom): [$-409]mm/dd/yy h:mm AM/PM;@ Excel refuses to accept ddd for Mon or dddd Monday at the end of the format I want it to read: 09/03/07 8:30 AM Monday in 1 cell. I have Excel 2003. One way: mm/dd/yy h:mm AM/PM dddd;@ In article <Xns99B8A3CAF9130pencilunlistedcom@208.49.82.220>, Burp <burp@beep.comINVALID> wrote: > Can I show the date, time and day of week in one cell. > > I have: > 09/03/07 8:30 AM > in one cell using the for...

Over Time Pay rate 1.51?
Its that time of year again, Audit time. The auditors have come across a very strange problem it seems that around October of last year random employees have had there OT pay rates increased to 1.51 (instead of the standard 1.50), I cant for the life of me figure out how this has come to be. Has anyone come across this problem before? And is there something I can do to fix or prevent this from reoccurring? It’s just not feasible to check each employee every week before payroll. Im certain that the employees were setup correctly initially. Thanks I have seen this and have not been ab...

I need a macro
I would like a macro to do the following: I would place the the cursor in any cell and this macro would give me the average of all the entries to the left if the selected cell. EX: I place the cursor in cell M12. I want this macro on display in cell M12 the average of all the values from Col C12 to Col L12. Note that there may NOT be entries in all the cells in that range. Also the start point would always be col C .Thanks You can do this easily with a worksheet_selection event in the sheet module but you would probably want to restrict to a certain range or columns and rows or it woul...

Date/Day
Hi, How do I make Excell tell me the day that the Date corresponds to. Example: If I have 8/7/03 in a column, I want to be able to enter a formula in the next column such that Excell would tell me it is Thursday Thx Omer Omer, Look at the WEEKDAY function. From Help Syntax WEEKDAY(serial_number,return_type) Serial_number is a sequential number that represents the date of the day you are trying to find. Dates may be entered as text strings within quotation marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers (for example, 35825, which represents...

Help with importing data
Can I have users fill in a form in Access and have that data be transferred and updated to a spreadsheet. Need for fill out several fields and then export to a specific spreadsheet and place that data into the cells that will update that cell (add to the total in that cell) of a spreadsheet. ...

MOVE TO FOLDER... only appears. I need MOVE TO FOLDER
This is very odd and I've found that I've had this problem before with not finding icons. Some I've found at the office don't appear here and vice versa, or they act differently. I have Outlook 2000 in both places! Very odd. At home, I couldn't find the HIGH IMPORTANCE icon under the list of items available in the customize. Yet I have it at work. That's the one where when the HIGH IMPORTANCE is on, it shows a depressed button state. I really need that in both places. Anyway, simple (or so I thought) - I ended up just brining the toolbar from work on a floppy, a...

number format with leading 0
Dose anyone know how to create a number format that would show a leading zero for the numbers 0-9. That is, so that 1 would show as 01. I can do with a text format but would like to retain the integrity of the number. Thanks Try this: Format>Cells>Number Category: Custom Type: 00 Click [OK] Does that help? *********** Regards, Ron "Darby" wrote: > Dose anyone know how to create a number format that would show a leading zero > for the numbers 0-9. That is, so that 1 would show as 01. I can do with a > text format but would like to retain the integrity...

How do I get Excell to sort dates that range from 1800 to 1900's
I have posted a cemetery survey on Excell spreadsheet. When I try to sort the birth dates or death dates in ascending or descending order the range of 1900 dates will sort but the 1800 dates fall after the 1900 and will not sort. Why? Is this inherit with this program? The WinXL default dates start with 31 December 1899, so earlier "dates" are interpreted as Text. You can use helper columns and text functions (LEFT(),RIGHT(),MID()) to put the Year, Month and Day in separate columns, then sort. Take a look here for additional workarounds: http://j-walk.com/ss/excel/files...

I need to print a word in PDF from excel.
Hi, In my excel Macro, I update several values and then open a word linked with that excel. Finally what I need to do is to print that word to PDF. I already have a PDF Printer, so I just need the code to do it. Can anybody help me? Thanks in advance. Gast=F3n. Habilita la impresora predeterminada como la PDF y ya lo puedes imprimir a al formato deseado Gracias Francisco,=20 Tienes alg=FAn codigo para ello? gracias Repitiendo: Desde el panel de impresoras asigno primero la que me interesa y despues ejecuto mi macro desde el archivo excel asignando la instruccion sig: midefaultes = a...

Call & Place Graphic Based on Cell Value?
Is there any way to call & place a graphic image based upon a cell value? Maybe you can look at J.E. McGimpsey's page: http://www.mcgimpsey.com/excel/lookuppics.html documike wrote: > > Is there any way to call & place a graphic image based upon a cell value? -- Dave Peterson ...

Fixing a formula to read a general format?
A few months ago I had asked a question and got this answer: Question: I have a a set of values in A1 through A100. I need to look up each value and find a match in another set of values located in C1 through C200. If a match is found then I need the formula located in column B to return the value in the same row but the next column over (D). Answer: =IF(ISNUMBER(VLOOKUP(A1,$C$1:$D$200,2,0)), VLOOKUP(A1,$C$1:$D$200,2,0), "") Copy down through A100. The formula worked fine, however the reference value and the look up value are not any more numbers but a combinatio...

ERROR: 5.1.2 or other punctuation after the recipient's email address.
I am trying to send a email to a group address I created with 58 addresses in it. When I try to send the email to this group I get the following Error: "5.1.2 or other punctuation after the recipient's email address. " I have gone back to the original contact cards and made sure the addresses were all legal. I have re-created the list, same problem I have re-booted, same thing. Any thoughts out there? Thanks Dunc My wife and I have MBP's and are both running MS Office for Mac's, Entourage 12.2.3. Both of us have messages cued up in the out boxes th...

Outlook 2000 calendar save as web page wrong dates.
Hi, When I save my calendar as a web page the dates are in error. This has just occurred in year 2005. Microsoft's site says it is a known issue but gives no details of how to resolve it. Anybody got any ideas? Regards Brian. Hi Brian, According to KB EN-US;891521 this issue is still under investigation. You can access this article by clicking on the link below. http://support.microsoft.com/default.aspx?scid=KB;EN-US;891521 Regards, Scott Atkins MCSE, MCSA Partner Technical Lead - Outlook Microsoft Technical Support for Platforms and Business Applications -------------------- ...

Formating text in cells
Hello, does anyone know how to force Excel to keep the hyphen or dash character (-) with the associated numbers when putting multiple dash-#s in a cell and turning "wrap" formating on? This is the same problem I have in Word; when I add dash-#s (ie; -xxx, -yyy, -zzz) Office apps interpret dashes ( - ) as a hypehen to a word and will keep the dash/hyphen on the line above until there are enough characters to force the dash/hyphen to the next line. In my work we use the format; -xxx, -yyy, -zzz to denote multiple versions of the same base part. Work instructions to operators...

New Record, last Date + 1 different records
I have a datefield in a form that I would like to have autofilled when I enter a new record. Right now I have something like this: If Me.NewRecord then Me.DateGauged = DMax("DateGauged","tblGaugesInput") + 1 End if My problem is that it just looks for the max date of the whole table. I would like to narrow it down to a specific group of records in the table and do the last date + 1 on that group: Example of my table. TankNumber DateGauged Measurement 8555 2/1/2008 8" 8555 2/2/2008 9" 8556 2/1/2008 ...

Calculations adding Text formats
I am new at this so please bear with me. I am using Excel XP. In my column I have a list of numbers. Some are formatted as text and some are formatted at numeric. When I insert by formula it seems to be calculating the entire column, even the text formatted cells. What am I doing wrong. Example Cell A1 = 5 - numeric Cell A2 = 5 - text format Cell A3 = 5 - text format Cell A4 = 5 - numeric format formula states =sum(a1:a4) the total should be 10 not 20. Correct? You're not doing anything wrong, per se. Changing formats cannot change numbers into text values or text values into...

Is there a way to cut off unused cells on a sheet
It seems there are an infinite number of cells on a sheet. As I really dont have much info to enter on each sheet I was hoping there was a way I could somehow cut off all the extra stuff to the sides and bottoms. It is a hassle because everytime I scroll, it will scroll off the side or bottom way past what I was looking for. Thanks! A manual way is to goto the last used row and delete all rows below. Do the same with columns. SAVE -- Don Guillett SalesAid Software donaldb@281.com "newbie" <newbie@discussions.microsoft.com> wrote in message news:469AABE7-8D17-4B72-91C1-...

Automatically copy input from one cell to another
After I enter a value in one cell, how can I have it automatically enter it into another cell, within the same worksheet, or into a different worksheet. Thanks, Tom picktr@wowway.co -- Message posted from http://www.ExcelForum.com If you enter the value in A1 of sheet1, put this in the other cell in sheet1: =A1 or in another worksheet: =Sheet1!A1 In article <picktr.15c6uy@excelforum-nospam.com>, picktr <<picktr.15c6uy@excelforum-nospam.com>> wrote: > After I enter a value in one cell, how can > I have it automatically enter it into another cell...