Too many different cell formats!

This error occcurs when i add some worksheets to a workbook from another
one. I am not completely sure (cos this is not my work actually) but it
seems to me that there is not really too much (about 4000?) "different" cell
formats in the workbook, but there is a quite lot amount of drawing objects
(grouped technical drwaings plus autocad objects which i also converted them
to bitmaps to overcome the error).

I also dont understand the restriction:
If i have 3999 cells formatted "bold" and another 2 formatted "underlined"
this should not count 4001. True?


My workbook has about 15 worksheets with each fits to 2 printing pages.





0
serdarsoy (63)
8/13/2005 3:03:51 PM
excel 39879 articles. 2 followers. Follow

11 Replies
870 Views

Similar Articles

[PageSpeed] 16

Adding Jerome's question below:


So my point is  : How to trace the evolution of the current number of
different cell formats in my active workbook? Which property of which
object will give me this figure?

Thank you
J�r�me



0
serdarsoy (63)
8/13/2005 3:31:29 PM
"serdar" <serdarsoy@yahoo.com> wrote in
news:OVkk8hBoFHA.3120@TK2MSFTNGP09.phx.gbl: 

> This error occcurs when i add some worksheets to a workbook from
> another one. 
> 

Excel specifications and limits:

http://office.microsoft.com/en-us/assistance/HP051992911033.aspx


-- 

It is I, DeauDeau
(Free after monsieur Leclerc in 'Allo, 'allo)
0
Dodo
8/13/2005 4:32:47 PM
Sercar,

The way I understand it it this.  Excel creates a thing called a style for 
any combination of all possible formats for a cell.  More than one cell can 
use that style if it wants the same formats.  There can be 4000 styles, then 
it runs out.  If you have 3999 cells formatted for bold, that's one style. 
You have 3999 more styles you can have.  The two underlined cells use a 
second style.  It has nothing to do with how many cells are formatted using 
any style.
--
Earl Kiosterud
www.smokeylake.com

"serdar" <serdarsoy@yahoo.com> wrote in message 
news:OVkk8hBoFHA.3120@TK2MSFTNGP09.phx.gbl...
> This error occcurs when i add some worksheets to a workbook from another
> one. I am not completely sure (cos this is not my work actually) but it
> seems to me that there is not really too much (about 4000?) "different" 
> cell
> formats in the workbook, but there is a quite lot amount of drawing 
> objects
> (grouped technical drwaings plus autocad objects which i also converted 
> them
> to bitmaps to overcome the error).
>
> I also dont understand the restriction:
> If i have 3999 cells formatted "bold" and another 2 formatted "underlined"
> this should not count 4001. True?
>
>
> My workbook has about 15 worksheets with each fits to 2 printing pages.
>
>
>
>
> 


0
someone798 (944)
8/13/2005 4:44:23 PM
Well then i am almost sure that i dont have more than a hundred style in my
workbook. Why i am still getting an error? It would be real good if i could
trace the number of cell formats used in the workbook by VB for instance.


"Earl Kiosterud" <someone@nowhere.com>, haber iletisinde �unlar�
yazd�:uv5awYCoFHA.3984@TK2MSFTNGP10.phx.gbl...
> Sercar,
>
> The way I understand it it this.  Excel creates a thing called a style for
> any combination of all possible formats for a cell.  More than one cell
can
> use that style if it wants the same formats.  There can be 4000 styles,
then
> it runs out.  If you have 3999 cells formatted for bold, that's one style.
> You have 3999 more styles you can have.  The two underlined cells use a
> second style.  It has nothing to do with how many cells are formatted
using
> any style.
> --
> Earl Kiosterud
> www.smokeylake.com
>
> "serdar" <serdarsoy@yahoo.com> wrote in message
> news:OVkk8hBoFHA.3120@TK2MSFTNGP09.phx.gbl...
> > This error occcurs when i add some worksheets to a workbook from another
> > one. I am not completely sure (cos this is not my work actually) but it
> > seems to me that there is not really too much (about 4000?) "different"
> > cell
> > formats in the workbook, but there is a quite lot amount of drawing
> > objects
> > (grouped technical drwaings plus autocad objects which i also converted
> > them
> > to bitmaps to overcome the error).
> >
> > I also dont understand the restriction:
> > If i have 3999 cells formatted "bold" and another 2 formatted
"underlined"
> > this should not count 4001. True?
> >
> >
> > My workbook has about 15 worksheets with each fits to 2 printing pages.
> >
> >
> >
> >
> >
>
>


0
serdarsoy (63)
8/14/2005 7:41:11 AM
Every combination of
borders (8 per cell inside/outside):   width, color, style
font:  italic, bold, regular, fontsize, strikeout, super/subscript
interior:  pattern,  color
number format:  you can make up your own custom formats as well

Those are just off of my memory.

It is best to format the entire worksheet at once, rather than
one cell at a time.  Much more efficient.

---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"serdar" <serdarsoy@yahoo.com> wrote in message news:t%23N1POPKoFHA.3984@TK2MSFTNGP10.phx.gbl...
> Well then i am almost sure that i dont have more than a hundred style in my
> workbook. Why i am still getting an error? It would be real good if i could
> trace the number of cell formats used in the workbook by VB for instance.
>
>
> "Earl Kiosterud" <someone@nowhere.com>, haber iletisinde �unlar�
> yazd�:uv5awYCoFHA.3984@TK2MSFTNGP10.phx.gbl...
> > Sercar,
> >
> > The way I understand it it this.  Excel creates a thing called a style for
> > any combination of all possible formats for a cell.  More than one cell
> can
> > use that style if it wants the same formats.  There can be 4000 styles,
> then
> > it runs out.  If you have 3999 cells formatted for bold, that's one style.
> > You have 3999 more styles you can have.  The two underlined cells use a
> > second style.  It has nothing to do with how many cells are formatted
> using
> > any style.
> > --
> > Earl Kiosterud
> > www.smokeylake.com
> >
> > "serdar" <serdarsoy@yahoo.com> wrote in message
> > news:OVkk8hBoFHA.3120@TK2MSFTNGP09.phx.gbl...
> > > This error occcurs when i add some worksheets to a workbook from another
> > > one. I am not completely sure (cos this is not my work actually) but it
> > > seems to me that there is not really too much (about 4000?) "different"
> > > cell
> > > formats in the workbook, but there is a quite lot amount of drawing
> > > objects
> > > (grouped technical drwaings plus autocad objects which i also converted
> > > them
> > > to bitmaps to overcome the error).
> > >
> > > I also dont understand the restriction:
> > > If i have 3999 cells formatted "bold" and another 2 formatted
> "underlined"
> > > this should not count 4001. True?
> > >
> > >
> > > My workbook has about 15 worksheets with each fits to 2 printing pages.
> > >
> > >
> > >
> > >
> > >
> >
> >
>
>



0
8/14/2005 10:05:36 AM
The workbook i work on is sent to us by a state organization and definitely
designed by novice users. We are having trouble to spot where is the
problem. They make lots of other mistakes. We have just noticed they have
drawn hundreds of useless drawing objects near a corner of a worksheet etc.

As i said before, i would be perfect to trace the number of cell formats
used in a workbook.


"David McRitchie" <dmcritchie_xlmvp@verizon.net>, haber iletisinde �unlar�
yazd�:u#jRnfLoFHA.3380@TK2MSFTNGP12.phx.gbl...
> Every combination of
> borders (8 per cell inside/outside):   width, color, style
> font:  italic, bold, regular, fontsize, strikeout, super/subscript
> interior:  pattern,  color
> number format:  you can make up your own custom formats as well
>
> Those are just off of my memory.
>
> It is best to format the entire worksheet at once, rather than
> one cell at a time.  Much more efficient.
>
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
> My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm
>
> "serdar" <serdarsoy@yahoo.com> wrote in message
news:t%23N1POPKoFHA.3984@TK2MSFTNGP10.phx.gbl...
> > Well then i am almost sure that i dont have more than a hundred style in
my
> > workbook. Why i am still getting an error? It would be real good if i
could
> > trace the number of cell formats used in the workbook by VB for
instance.
> >
> >
> > "Earl Kiosterud" <someone@nowhere.com>, haber iletisinde �unlar�
> > yazd�:uv5awYCoFHA.3984@TK2MSFTNGP10.phx.gbl...
> > > Sercar,
> > >
> > > The way I understand it it this.  Excel creates a thing called a style
for
> > > any combination of all possible formats for a cell.  More than one
cell
> > can
> > > use that style if it wants the same formats.  There can be 4000
styles,
> > then
> > > it runs out.  If you have 3999 cells formatted for bold, that's one
style.
> > > You have 3999 more styles you can have.  The two underlined cells use
a
> > > second style.  It has nothing to do with how many cells are formatted
> > using
> > > any style.
> > > --
> > > Earl Kiosterud
> > > www.smokeylake.com
> > >
> > > "serdar" <serdarsoy@yahoo.com> wrote in message
> > > news:OVkk8hBoFHA.3120@TK2MSFTNGP09.phx.gbl...
> > > > This error occcurs when i add some worksheets to a workbook from
another
> > > > one. I am not completely sure (cos this is not my work actually) but
it
> > > > seems to me that there is not really too much (about 4000?)
"different"
> > > > cell
> > > > formats in the workbook, but there is a quite lot amount of drawing
> > > > objects
> > > > (grouped technical drwaings plus autocad objects which i also
converted
> > > > them
> > > > to bitmaps to overcome the error).
> > > >
> > > > I also dont understand the restriction:
> > > > If i have 3999 cells formatted "bold" and another 2 formatted
> > "underlined"
> > > > this should not count 4001. True?
> > > >
> > > >
> > > > My workbook has about 15 worksheets with each fits to 2 printing
pages.
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
>


0
serdarsoy (63)
8/14/2005 10:13:45 AM
Hi Serdar,

Perhaps the following code from Leo Heuser may be of interest.

If the 'No' option is selected in response to Leo's opening message box, a 
list of all used (and unused) formats is produced.

'=============================>>
Sub DeleteUnusedCustomNumberFormats()
'leo.heu...@get2net.dk, May 6. 2001
'Version 1.01
    Dim Buffer As Object
    Dim Sh As Object
    Dim SaveFormat As Variant
    Dim fFormat As Variant
    Dim nFormat() As Variant
    Dim xFormat As Long
    Dim Counter As Long
    Dim Counter1 As Long
    Dim Counter2 As Long
    Dim StartRow As Long
    Dim EndRow As Long
    Dim pPresent As Boolean
    Dim NumberOfFormats As Long
    Dim Answer
    Dim Cell As Object
    Dim DataStart As Long
    Dim DataEnd As Long
    Dim AnswerText As String
    Dim ActWorkbookName As String
    Dim BufferWorkbookName As String

    NumberOfFormats = 1000
    StartRow = 3 ' Do not alter this value
    EndRow = 16384 ' For Excel 97 and 2000 set EndRow to 65536


    ReDim nFormat(0 To NumberOfFormats)


    AnswerText = "Do you want to delete unused custom formats " _
                                & "from the workbook?"
    AnswerText = AnswerText & Chr(10) & "To get a list of used " _
                         & "and unused formats only, choose No."
    Answer = MsgBox(AnswerText, 259)
    If Answer = vbCancel Then GoTo Finito


    On Error GoTo Finito
    ActWorkbookName = ActiveWorkbook.Name
    Workbooks.Add
    BufferWorkbookName = ActiveWorkbook.Name


    Set Buffer = Workbooks(BufferWorkbookName). _
                                      ActiveSheet.Range("A3")
    nFormat(0) = Buffer.NumberFormatLocal
    Buffer.NumberFormat = "@"
    Buffer.Value = nFormat(0)

    Workbooks(ActWorkbookName).Activate

    Counter = 1
    Do
        SaveFormat = Buffer.Value
        DoEvents
        SendKeys "{TAB 3}"
        For Counter1 = 1 To Counter
            SendKeys "{DOWN}"
        Next Counter1
        SendKeys "+{TAB}{HOME}'{HOME}+{END}" _
                                & "^C{TAB 4}{ENTER}"
        Application.Dialogs(xlDialogFormatNumber). _
                                      Show nFormat(0)
        ActiveSheet.Paste Destination:=Buffer
        Buffer.Value = Mid(Buffer.Value, 2)
        nFormat(Counter) = Buffer.Value
        Counter = Counter + 1
    Loop Until nFormat(Counter - 1) = SaveFormat

ReDim Preserve nFormat(0 To Counter - 2)

    Workbooks(BufferWorkbookName).Activate

    Range("A1").Value = "Custom formats"
    Range("B1").Value = "Formats used in workbook"
    Range("C1").Value = "Formats not used"
    Range("A1:C1").Font.Bold = True

    For Counter = 0 To UBound(nFormat)
        Cells(StartRow, 1).Offset(Counter, 0). _
                 NumberFormatLocal = nFormat(Counter)
        Cells(StartRow, 1).Offset(Counter, 0).Value = _
                                      nFormat(Counter)
    Next Counter

    Counter = 0
    For Each Sh In Workbooks(ActWorkbookName).Worksheets
        For Each Cell In Sh.UsedRange.Cells
            fFormat = Cell.NumberFormatLocal
            If Application.WorksheetFunction.CountIf _
                    (Range(Cells(StartRow, 2), Cells _
                    (EndRow, 2)), fFormat) = 0 Then
                Cells(StartRow, 2).Offset(Counter, 0). _
                              NumberFormatLocal = fFormat
                Cells(StartRow, 2).Offset(Counter, 0).Value _
                                                = fFormat
                Counter = Counter + 1
            End If
        Next Cell
    Next Sh

    xFormat = Range(Cells(StartRow, 2), Cells(EndRow, 2)). _
                                            Find("").Row - 2
    Counter2 = 0
    For Counter = 0 To UBound(nFormat)
        pPresent = False
        For Counter1 = 1 To xFormat
            If nFormat(Counter) = Cells(StartRow, 2).Offset _
                        (Counter1, 0).NumberFormatLocal Then
                pPresent = True
            End If
        Next Counter1
        If pPresent = False Then
            Cells(StartRow, 3).Offset(Counter2, 0). _
                       NumberFormatLocal = nFormat(Counter)
            Cells(StartRow, 3).Offset(Counter2, 0).Value = _
                                           nFormat(Counter)
            Counter2 = Counter2 + 1
        End If
    Next Counter
    With ActiveSheet.Columns("A:C")
        .AutoFit
        .HorizontalAlignment = xlLeft
    End With
    If Answer = vbYes Then
        DataStart = Range(Cells(1, 3), _
                        Cells(EndRow, 3)).Find("").Row + 1
        DataEnd = Cells(DataStart, 3).Resize(EndRow, 1). _
                                          Find("").Row - 1
        On Error Resume Next
        For Each Cell In Range(Cells(DataStart, 3), _
                                    Cells(DataEnd, 3)).Cells
            Workbooks(ActWorkbookName).DeleteNumberFormat _
                                        (Cell.NumberFormat)
        Next Cell
    End If
Finito:
    Set Cell = Nothing
    Set Sh = Nothing
    Set Buffer = Nothing
End Sub
'<<=============================


---
Regards,
Norman



"serdar" <serdarsoy@yahoo.com> wrote in message 
news:OREPekLoFHA.2472@tk2msftngp13.phx.gbl...
> The workbook i work on is sent to us by a state organization and 
> definitely
> designed by novice users. We are having trouble to spot where is the
> problem. They make lots of other mistakes. We have just noticed they have
> drawn hundreds of useless drawing objects near a corner of a worksheet 
> etc.
>
> As i said before, i would be perfect to trace the number of cell formats
> used in a workbook.


0
normanjones (1047)
8/14/2005 11:30:20 AM
Serdar,

They call them "format combinations," not styles, and I'm not sure they're 
actually called styles internally (I'm not sure where I read that they're 
called styles -- maybe I made it up).  They aren't part of the 
workbook.styles collection.  That collection has the normal style, a handful 
of commonly used styles (currency, etc), and the Normal style, the one used 
when  you haven't done any formatting on a cell, or have removed all 
formatting, as with Edit - Clear - Formats, and any styles you've added with 
Format - Styles.  I haven't been able to find anything in vba that would 
tell you how many "format combinations" have been used.  It would certainly 
be useful.
--
Earl Kiosterud
www.smokeylake.com

"serdar" <serdarsoy@yahoo.com> wrote in message 
news:%23N1POPKoFHA.3984@TK2MSFTNGP10.phx.gbl...
> Well then i am almost sure that i dont have more than a hundred style in 
> my
> workbook. Why i am still getting an error? It would be real good if i 
> could
> trace the number of cell formats used in the workbook by VB for instance.
>
>
> "Earl Kiosterud" <someone@nowhere.com>, haber iletisinde �unlar�
> yazd�:uv5awYCoFHA.3984@TK2MSFTNGP10.phx.gbl...
>> Sercar,
>>
>> The way I understand it it this.  Excel creates a thing called a style 
>> for
>> any combination of all possible formats for a cell.  More than one cell
> can
>> use that style if it wants the same formats.  There can be 4000 styles,
> then
>> it runs out.  If you have 3999 cells formatted for bold, that's one 
>> style.
>> You have 3999 more styles you can have.  The two underlined cells use a
>> second style.  It has nothing to do with how many cells are formatted
> using
>> any style.
>> --
>> Earl Kiosterud
>> www.smokeylake.com
>>
>> "serdar" <serdarsoy@yahoo.com> wrote in message
>> news:OVkk8hBoFHA.3120@TK2MSFTNGP09.phx.gbl...
>> > This error occcurs when i add some worksheets to a workbook from 
>> > another
>> > one. I am not completely sure (cos this is not my work actually) but it
>> > seems to me that there is not really too much (about 4000?) "different"
>> > cell
>> > formats in the workbook, but there is a quite lot amount of drawing
>> > objects
>> > (grouped technical drwaings plus autocad objects which i also converted
>> > them
>> > to bitmaps to overcome the error).
>> >
>> > I also dont understand the restriction:
>> > If i have 3999 cells formatted "bold" and another 2 formatted
> "underlined"
>> > this should not count 4001. True?
>> >
>> >
>> > My workbook has about 15 worksheets with each fits to 2 printing pages.
>> >
>> >
>> >
>> >
>> >
>>
>>
>
> 


0
someone798 (944)
8/14/2005 3:03:44 PM
Yes, i mean cell formats. I used the word "styles" cos Microsoft refers them
so in this article as "Cell styles in a workbook":
    http://office.microsoft.com/en-us/assistance/HP051992911033.aspx

thanks.


"Earl Kiosterud" <someone@nowhere.com>, haber iletisinde �unlar�
yazd�:OoAxbFOoFHA.3316@TK2MSFTNGP14.phx.gbl...
> Serdar,
>
> They call them "format combinations," not styles, and I'm not sure they're
> actually called styles internally (I'm not sure where I read that they're
> called styles -- maybe I made it up).  They aren't part of the
> workbook.styles collection.  That collection has the normal style, a
handful
> of commonly used styles (currency, etc), and the Normal style, the one
used
> when  you haven't done any formatting on a cell, or have removed all
> formatting, as with Edit - Clear - Formats, and any styles you've added
with
> Format - Styles.  I haven't been able to find anything in vba that would
> tell you how many "format combinations" have been used.  It would
certainly
> be useful.
> --
> Earl Kiosterud
> www.smokeylake.com


0
serdarsoy (63)
8/14/2005 3:29:08 PM
Serdar,

HUH! There it is!  HUH! There it is!  They don't differentiate between 
user-defined styles (Format - Style) or internally-generated styles (created 
with each unique combination of formats).  To determine if they're both 
included,
I wrote a vba routine to put unique combinations of font color, fill color, 
and border color.  It crashed at cell 3821.  After that, I was unable to add 
a style (Format Style), or manually format a cell.  Either gave the "Too 
many format combinations" message.  Apparently, they're all lumped together. 
And it ain't 4000 exactly.

This is significant:  If you manually clear or delete (or even delete the 
sheet), it still doesn't allow any more cell formatting or style adding. 
It's as if the styles don't get cleared when no longer used in any cell. 
That might account for your situation.  You may have to copy the stuff to a 
new workbook.

Here's the routine, if you want to play with it.

Sub TestStyles()
Dim F As Integer
Dim i As Integer
Dim B As Integer
Dim StyleCount As Integer

Range("A:A").Clear
For F = 1 To 56 ' font colorindex
  For i = 1 To 56 ' interior (fill) colorindex
     For B = 1 To 56 ' top border colorindex
       StyleCount = StyleCount + 1
       Cells(StyleCount, 1).Select            ' watch it run
       Cells(StyleCount, 1).Value = StyleCount ' put style count in cell
       Cells(StyleCount, 1).Font.ColorIndex = F ' set font color
       Cells(StyleCount, 1).Interior.ColorIndex = i ' set fill color
       With Cells(StyleCount, 1).Borders(xlEdgeTop)
          .LineStyle = xlContinuous
          .Weight = xlThick
          .ColorIndex = B               ' set border color
          End With
        Next B
      Next i
    Next F

  End Sub
-- 
Earl Kiosterud
www.smokeylake.com

"serdar" <serdarsoy@yahoo.com> wrote in message 
news:O2nWtUOoFHA.1204@TK2MSFTNGP12.phx.gbl...
> Yes, i mean cell formats. I used the word "styles" cos Microsoft refers 
> them
> so in this article as "Cell styles in a workbook":
>    http://office.microsoft.com/en-us/assistance/HP051992911033.aspx
>
> thanks.
>
>
> "Earl Kiosterud" <someone@nowhere.com>, haber iletisinde �unlar�
> yazd�:OoAxbFOoFHA.3316@TK2MSFTNGP14.phx.gbl...
>> Serdar,
>>
>> They call them "format combinations," not styles, and I'm not sure 
>> they're
>> actually called styles internally (I'm not sure where I read that they're
>> called styles -- maybe I made it up).  They aren't part of the
>> workbook.styles collection.  That collection has the normal style, a
> handful
>> of commonly used styles (currency, etc), and the Normal style, the one
> used
>> when  you haven't done any formatting on a cell, or have removed all
>> formatting, as with Edit - Clear - Formats, and any styles you've added
> with
>> Format - Styles.  I haven't been able to find anything in vba that would
>> tell you how many "format combinations" have been used.  It would
> certainly
>> be useful.
>> --
>> Earl Kiosterud
>> www.smokeylake.com
>
> 


0
someone798 (944)
8/14/2005 5:44:57 PM
> That might account for your situation.  You may have to copy the stuff to
a
> new workbook.

Thanks, that was the solution i suggested to my friend, and im gonna try it
first time i arrived at the office again. I was even thinking to email the
workbook to some excel pro (like u:) to check it out. Thanks for the script
also.


0
serdar
8/15/2005 10:14:51 AM
Reply:

Similar Artilces:

cell value depends on value in another cell question
Hi. Anyone know how to have a cell formula control the information in another cell? I'm trying to get a row of cells to only allow one x (trying to get them to act like a check box that only allows one check). I can't figure if excel is capable of controling a cell's value from an outside formula. Hi maybe 'Data - Validation' is what you're looking for. See http://www.contextures.com/xlDataVal01.html -- Regards Frank Kabel Frankfurt, Germany Sylvia wrote: > Hi. Anyone know how to have a cell formula control the information > in another cell? I'm tryi...

copy sort results to different worksheet?
Hi all, Relative Excel newbie here :) I have an Excel file with three worksheets in it. Right now, only the first worksheet has data--the data is four columns of unsorted text (it's a list of karaoke songs...the columns are labeled "Song Title", "Artist", "CD", and "Track Number"). I know how to sort the data by different columns so that I can either have everything listed alphabetically by Song Title, or alphabetically by Artist. What I'd like to do is somehow be able to take those two "sorts" and output each one on a differe...

Linked cells #4
I have two spreadsheets. One has a list of names and the other has only 4 cells, each with a link to one of the first four names in the first spreadsheet. I would like to print the second spreadsheet and then delete the first four names in the first spreadsheet so that the cells in the second spreadsheet now contain the next 4 names on the list. When I delete the first 4 names in the first spreadsheet, the links in the second spreadsheet fail and I get #REF! in the cells. I have tried both relative addressing and absolute addressing and both return the same error when I delete th...

Count on cell for each time it is changed
I would like to count the number of times a cell has been amended/ changed e.g cell b2 has the word red it it, in cell c2 i would like a counter for everytime the cell in b2 has changed, at the end of the day you get final number. Is this at all possible? Jelinek, You can do it by putting the following VBA macro in your sheet: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$2" Then Cells(2, 3) = Cells(2, 3) + 1 End If End Sub Art "Jelinek" wrote: > I would like to count the number of times a cell has bee...

difference in percentange
This is probably a very easy question.... I have the following cell D cell E 484 526 In cell F I want to show the difference in a percentage. I currently have the following format (D1-E10/D1 but it gives me a -9%....when it is actaully an increase....what am I doing wrong? Then you want: =(E1-D1)/D1 Regards, Fred "Caribbrz" <Caribbrz@discussions.microsoft.com> wrote in message news:423B80BF-1828-4E17-A48B-F5027EFE4A38@microsoft.com... > This is probably a very easy question.... > > I have the following > > cell D cell ...

Edit directly in Cells
I am having intermittent (daily) problems with 'Editing Directly in Cells'. After hitting the [F2] key to edit data in a cell, the edit bar cursor shows in the cell correctly. But, when I use the arrow keys in jumps to the next cell to the left as if I hit [Shift][Tab]. I do have the 'Edit Directly in Cells' checked in Options-Edit. Turning it off and on does not make any difference. I must use the mouse to select the text to edit. We tend to input some status language at the end of the task name, so this would be a productivity impact. I have researched the we...

many-to-many relationships by Graham Mandeno
Hi to all, A couples of months ago I was looking for a good way to handle a many- to-many relationships. I finally found this sample db: http://www.accessmvp.com/KDSnell/SampleDBs.htm. It's simply great! Unfortunately it doesn't work in my case because instead of using just numeric ID to relate the table I'm make use of alphanumeric ID. In the previous: http://groups.google.it/group/microsoft.public.access.formscoding/browse_thread/thread/25f1941ea8428424/9d3fc99e39b80743?lnk=gst&q=riccifs+many#9d3fc99e39b80743 Graham told me that there was a way to give to the sample db this...

Cell colour format according to date
If a cell value is equal to today I want the colour to change to red. I have entered =TODAY as a conditional formatting value but the cell colour does not change. The cell has been formatted as ddd dd mmm. Any ideas please. Thanks in advance for any help John Hi John Try the below 1. Select the cell/Range (say A1:A10). Please note that the cell reference A1 mentioned in the formula is the active cell in the selection. Active cell will have a white background even after selection 2. From menu Format>Conditional Formatting> 3. For Condition1>Select 'Form...

constant cell
Assume an investment sum. Assume two columns. Column A has a list of monetary figures increasing in value as they go down the column. Column B is a percentage of the increases the cells in Column A. For example Cell A would have value of $5000.00 Cell A2 $5140.00 Cell lA3 $5216 and so on own the column Cell B1 would show a nil value as it is related to Cell A1 Cell B2 would show a % value of % value of $140.00 as it related to Cell A1 Cell B3 would show a $216.00 as it related the Cell A1 In other words Cell A1 must be related to all the cells in Column B. I want to set the formula ...

"Too many different cell formats" error
Receiving this pop up error message when opening a .xls file. File will not open. Any suggestions on how to fix and save file? Hi Lorena XL: Error Message: Too Many Different Cell Formats http://support.microsoft.com/default.aspx?scid=kb;en-us;213904 David McRitchie posted this Leo Heuser posted a macro 2001-05-06 in programming as a very major revision of the macro in the eee007 article. http://groups.google.com/groups?selm=OxP9cgi1AHA.1572%40tkmsftngp02 -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Lorena" <lorena.l.solano@convergys.com...

Reference a cell in criteria range
I want to average a range if 2 criteria are met; the criteria includes cell references: C6 = 1/1/2009 (internal date) E6 = 1/1/2010 (internal date) Cells B11:BJ11 contain valid dates Cells C11:AJ11 contain valid numbers I want to average the values in C11:AJ11 that are >= C6 and <= E6. Can I reference the cells in the criteria range? I used averageifs(C11:AJ11,B11:BJ11,">=C6",B11:BJ11,"<=E6") which of course did not work. Thanks! Hi Connie You are very close: =averageifs(C11:AJ11,B11:BJ11,">=" & C6...

When forwarding an email how many recipients can I send it to?
I got an error response when I forwarded an email to many in my address book. It said, something about too many recipients. How many is too many? I set a 300 recipient limit to my Exchange users at work. This is set at my server level. Quote from http://office.microsoft.com/en-us/outlook/HA012002371033.aspx Many e-mail service providers set limits for the number of names that can be in the To, Cc, and Bcc fields in a message. For example, your e-mail service provider may limit each message to a maximum of 100 e-mail addresses. If these addresses can be distributed among the To, Cc, and ...

Too many clicks! Available formats box
Hello folks I have a client who is unhappy with the amount of clicks in Word 2007. One particular frustration is the Insert Date and Time Available Formats box. Is there anyway to get Word 2007 to just insert the default Date without the additional steps of the format box? I know to the average person, it's not a big deal but this user lives in a pressure cooker and needs the useability of Word 2007 to make things faster, not slower. Thanks for your suggestions! Bill Hole MCTS, SBSC Ask the client to add the Date & Time command to the Quick Access Toolbar ...

is it possible to have 2 different users on same computer
I have outlook 2002 on computer that I share with my spouse. We currently have our email thru hotmail, but have been thinking of changing over to Outlook. Is it possible to have 2 seperate address books and inboxes with different email addresses, so that he can get his emails and I get mine? Yes. You can have a single installation of Outlook and multiple Profiles. Profiles can be setup via the Mail Control Panel (Start - Control Panel - Mail). Click the Show Profiles... button. You should see the Mail tab and at lease one profile. You can click the Add button to configure a new profile...

Combobox add large range of cells
I have placed a Combobox(ActiveX) on a worksheet(Sheet2), but now i want to populate it will all the values(that are not blank) in Sheet3 between range("A2:A25000"). How do i do this without manually do this: Combobox1.Additem Sheet3.Range("A2").value Combobox1.Additem Sheet3.Range("A3").value ..... ..... and so on? Corey.... Dim myRng as range dim myCell as range set myrng = sheet3.range("a2:a25000") 'or if you potentially have lots of empty cells at the bottom with sheet3 set myrng = .range("A2", .cells(...

Should this be one to Many or Many to Many.. Relationship
Hi, New to Access learning... I have Table 1.. Publisher Table 2.. Books I was wondering .. Should I create Transient Third Table e.g. BooksPublisher.. Or Should I put PublisherID key AS Foreign key into Books Table ? which would be Practical E.g Books Publishers BooksNPublishers Pk BookID Pk PublisherID PK BookPublisherID Fk BookID ...

How do I reverse a spreadsheet that is in the format of a subtota.
I have a spreadsheet that details transactions by vendor but there is one line for the vendor and the detail transactions fall below the vendor line. The detail transactions do not show the vendor name. I need to have the vendor name on each line of the detail so I can sort and analyze the data. This is actually a report exported from QuickBooks. The only option I see is to copy the vendor name to each detail line and then remove the original vendor line. This is a lot of manual labor for a pretty large spreadsheet. Frank, Check this out... http://j-walk.com/ss/excel/usertips/tip0...

Removing " " from a cell
I downloaded a file which has the extension .csv. It has a column of numbers but they are preceded and followed by " like this "4293226" and I am trying to create a formula that subtracts the second row, third row.... to the end number from the first row number. I get a #VALUE!. Is there a simple way to remove the "" from each number? Select the range to fix Edit replace what: " with: (leave blank) replace all Maybe the quickest way. GTT wrote: > > I downloaded a file which has the extension .csv. It has a column of > numbers but they are preced...

Connect Mailbox to a Different User
I have User A that has a mailbox. I have User B that has no mailbox. At the moment both A and B can use A's mailbox. I want to disconnect the mailbox from A and connect it to B's account, not just have B able to use the mailbox. How do I do this? Thanks. Make sure you retention set on the mailbox store. http://support.microsoft.com/default.aspx?scid=kb;en-us;274343 Dont be fooled by the title of this KB, you need to delete the mailbox first. On Wed, 23 Feb 2005 14:17:04 -0800, NDaveZ <NDaveZ@discussions.microsoft.com> wrote: >I have User A that has a mailbox. >I ha...

Difference in asset tax and financial basis
Acquired assets have a higher value for financial than tax. How do I record a lower tax basis in GP 9.0 fixed assets for tax depreciation calculations? Open the Asset Book (Cards>Fixed Assets>Book) and change the Cost Basis field for your Tax Book(s) to reflect the correct tax basis. Frank Hamelly, MCP-GP NOVA Solutions LLC Melbourne, FL Does this effect the financial basis? "Frank Hamelly, MCP-GP" wrote: > Open the Asset Book (Cards>Fixed Assets>Book) and change the Cost > Basis field for your Tax Book(s) to reflect the correct tax basis. > > Frank ...

Filling Blank Cells
Hi I run Excel 2K I have some information that I download from a mainframe into a spreadsheet. See Example Smith Training Module 1 Training Module 2 Training Module 3 Smith Training Module 4 Alder Training Module 1 Training Module 2 Alder Training Module 3 As you can see, the information that is duplicated leaves blank cells between the first and last entries of each unique record. I would like to have a macro or code or similar to be able to fill in these empty cells with the missing unique entries. Any su...

sort data without formulas in other cells changing?
Hi, Can someone please help? I've got quite a lot of data that I want to sort by the persons date of birth, but because I have cells with formula in it (this works out the persons age) the sort function is changing the formulas so the formulas no longer work becuase it changes the cell where it is getting the data from. Does anyone have any ideas how this could be fixed?? Thanks! I bet you have formulas that point directly at other cells in another worksheet: =sheet2!b3 If you do, you may want to see if you can redesign your worksheet so that you can use some key column (name/i...

Hours difference
Need to calculate difference in terms of hours....weekend issue. 1st date and time - 1/29/10 (friday) 1:00am 2nd date and time - 2/1/10 (monday) 1:00am By calculating manually, the result that i would need to show is 24 hrs (based on my shop floor operations). Anything more than 24hrs is an issue at my operations. How would i do this in excel? Thank you. Hmmm...., if the 01:00 can be ignored you may try this: In A1 - Start Date In A2 - End Date In A3 - The hereunder Array-Formula: {=24*(SUM(N(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))<>7))-SUM(N(WEEKDAY(ROW(INDIR...

Formatting footnotes when page numbering is turned on
When I have paragraph numbering turned on, Word insists on assigning a paragraph number to my footnotes as well as a footnote number. I can manually delete the paragraph number, but would prefer to turn off the option. More important, however, is that Word also assigns a paragraph number to the line drawn across the page above the footnotes, and I cannot find a way to remove this. Can somebody offer me a suggestion on what to do? It would appear that you have applied numbering to the Normal style, which affects every style based on it. This is one reason we advocate using Bod...

Formatting in sql server reports
I have some sql server report service 2008 reports that will be exported to word 2007 documents. Are there any formatting features I should be aware of so these SSRS 2008 reports will not have formatting problems? I want to make certain that all the data is displayed appropriately for one page. I want to make certain there are not page breaks between different sections of the reports, data that show be one one page does not overflow to two or more pages. Thus, can you tell me what the export formatting options are that I can use? Would I need to use the formatting options w...