Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Excel question
I have two columns in an excel sheet. One column shows 1 representing male
and 2 represnting female. In the second column I have marks scored by males and females in a class. In all, I have 20 rows used to store the data for males and females. The storage is not in any particular order. Can you please let me know how i can either use the if condition or the ifcount function to count the number males and females that scored a specific mark, say 50% in the class? Thanks. Dolpandotcom 06/16/09 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Excel question
Hi,
If you want to just count the total of Male and Female that achieved 50% =sumproduct(--($B$1:$B$20=0.5)) if you want to count who achieve 50% or more =sumproduct(--($B$1:$B$20=0.5)) if this helps please click yes, thands "dolpandotcom" wrote: I have two columns in an excel sheet. One column shows 1 representing male and 2 represnting female. In the second column I have marks scored by males and females in a class. In all, I have 20 rows used to store the data for males and females. The storage is not in any particular order. Can you please let me know how i can either use the if condition or the ifcount function to count the number males and females that scored a specific mark, say 50% in the class? Thanks. Dolpandotcom 06/16/09 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Excel question
If you want to count males and females with 50 marks then use
=COUNTIF(B1:B20,"50") Change 50 to appropriate %. If you want males only use =SUMPRODUCT(--(A1:A20=1),--(B1:B2050)) "dolpandotcom" wrote: I have two columns in an excel sheet. One column shows 1 representing male and 2 represnting female. In the second column I have marks scored by males and females in a class. In all, I have 20 rows used to store the data for males and females. The storage is not in any particular order. Can you please let me know how i can either use the if condition or the ifcount function to count the number males and females that scored a specific mark, say 50% in the class? Thanks. Dolpandotcom 06/16/09 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Excel question
Hi,
1. Enter the value (mark) you want in D1 2. Use the formula =COUNTIF(B1:B20,D1) If you want just males or just females with a particular score, in 2007 you could use =COUNTIFS(B1:B20,D1,A1:A20,E1) where E1 contains the number 1 or 2 to indicate male or female and D1 is as above. If you want to count both males and females with scores between 40% and 50% for example 2007: =COUNTIFS(B1:B20,D1,B1:B20,E1) with =.4 in D1 and <=.5 in E1 2003 =SUMPRODUCT((B1:B20=D1)*(B1:B20<=E1)) with .4 in D1 and .5 in E1 -- If this helps, please click the Yes button. Cheers, Shane Devenshire "dolpandotcom" wrote: I have two columns in an excel sheet. One column shows 1 representing male and 2 represnting female. In the second column I have marks scored by males and females in a class. In all, I have 20 rows used to store the data for males and females. The storage is not in any particular order. Can you please let me know how i can either use the if condition or the ifcount function to count the number males and females that scored a specific mark, say 50% in the class? Thanks. Dolpandotcom 06/16/09 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Excel question
Thanks, but this exactly what I have in an Excel sheet:
Sex score 1 50 1 30 2 50 2 25 2 50 1 50 From the above table, I will like to know how can use an IF or IFcount to generate a result such as saying three male had the score of 50, while two females also had 50. Please note that 1 refers to Male while 2 refers to female. I will appreciate your respond Edardo or any other person pls. Thanks. Dolpan Eduardo wrote: Hi, If you want to just count the total of Male and Female that achieved 50% =sumproduct(--($B$1:$B$20=0.5)) if you want to count who achieve 50% or more =sumproduct(--($B$1:$B$20=0.5)) if this helps please click yes, thands I have two columns in an excel sheet. One column shows 1 representing male and 2 represnting female. In the second column I have marks scored by males [quoted text clipped - 8 lines] Dolpandotcom 06/16/09 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Excel question
Hi
Sex in column A and score in column B To get the results for 1 =SUMPRODUCT(--(A1:A14=1),--(B1:B14=50)) To get the results for 2 =SUMPRODUCT(--(A1:A14=2),--(B1:B14=50)) "dolpandotcom" wrote: Thanks, but this exactly what I have in an Excel sheet: Sex score 1 50 1 30 2 50 2 25 2 50 1 50 From the above table, I will like to know how can use an IF or IFcount to generate a result such as saying three male had the score of 50, while two females also had 50. Please note that 1 refers to Male while 2 refers to female. I will appreciate your respond Edardo or any other person pls. Thanks. Dolpan Eduardo wrote: Hi, If you want to just count the total of Male and Female that achieved 50% =sumproduct(--($B$1:$B$20=0.5)) if you want to count who achieve 50% or more =sumproduct(--($B$1:$B$20=0.5)) if this helps please click yes, thands I have two columns in an excel sheet. One column shows 1 representing male and 2 represnting female. In the second column I have marks scored by males [quoted text clipped - 8 lines] Dolpandotcom 06/16/09 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Excel question
Hi Shane,
Thanks a lot. Your script for the Excel 2003 did the magic for me. I still have two more questions for you and others please, 1. How can I select two items on my dropdown menu created using the data validation method in Excel 2003? Presently I can only select one item, but I will like to be able to select two items at a time. 2. How can I make calendar to pupup so as to be able to select month/date please? 3. Is it possible to write a script to reject a name that already features among my client list in an excel sheet: Please give me some clues or the script for it. Cheers, Dolpan Shane Devenshire wrote: Hi, 1. Enter the value (mark) you want in D1 2. Use the formula =COUNTIF(B1:B20,D1) If you want just males or just females with a particular score, in 2007 you could use =COUNTIFS(B1:B20,D1,A1:A20,E1) where E1 contains the number 1 or 2 to indicate male or female and D1 is as above. If you want to count both males and females with scores between 40% and 50% for example 2007: =COUNTIFS(B1:B20,D1,B1:B20,E1) with =.4 in D1 and <=.5 in E1 2003 =SUMPRODUCT((B1:B20=D1)*(B1:B20<=E1)) with .4 in D1 and .5 in E1 I have two columns in an excel sheet. One column shows 1 representing male and 2 represnting female. In the second column I have marks scored by males [quoted text clipped - 8 lines] Dolpandotcom 06/16/09 -- Message posted via http://www.officekb.com |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Excel question
Hi Eduardo,
Thanks a lot. Your script did the magic for me. I still have two more questions for you and others please, 1. How can I select two items on my dropdown menu created using the data validation method in Excel 2003? Presently I can only select one item, but I will like to be able to select two items at a time. 2. How can I make calendar to pupup so as to be able to select month/date please? 3. Is it possible to write a script to reject a name that already features among my client list in an excel sheet: Please give me some clues or the script for it. Cheers, Dolpan Eduardo wrote: Hi Sex in column A and score in column B To get the results for 1 =SUMPRODUCT(--(A1:A14=1),--(B1:B14=50)) To get the results for 2 =SUMPRODUCT(--(A1:A14=2),--(B1:B14=50)) Thanks, but this exactly what I have in an Excel sheet: [quoted text clipped - 33 lines] Dolpandotcom 06/16/09 -- Message posted via http://www.officekb.com |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Excel question
Question 1.
Download a sample workbook from Debra Dalgleish's site. http://www.contextures.on.ca/excelfiles.html#DV0017 Question 2. See Ron de Bruin's site http://www.rondebruin.nl/calendar.htm Question 3. See Chip Pearson's site. http://www.cpearson.com/excel/NoDupEntry.aspx Gord Dibben MS Excel MVP On Wed, 17 Jun 2009 07:38:07 GMT, "dolpandotcom via OfficeKB.com" <u52610@uwe wrote: Hi Shane, Thanks a lot. Your script for the Excel 2003 did the magic for me. I still have two more questions for you and others please, 1. How can I select two items on my dropdown menu created using the data validation method in Excel 2003? Presently I can only select one item, but I will like to be able to select two items at a time. 2. How can I make calendar to pupup so as to be able to select month/date please? 3. Is it possible to write a script to reject a name that already features among my client list in an excel sheet: Please give me some clues or the script for it. Cheers, Dolpan Shane Devenshire wrote: Hi, 1. Enter the value (mark) you want in D1 2. Use the formula =COUNTIF(B1:B20,D1) If you want just males or just females with a particular score, in 2007 you could use =COUNTIFS(B1:B20,D1,A1:A20,E1) where E1 contains the number 1 or 2 to indicate male or female and D1 is as above. If you want to count both males and females with scores between 40% and 50% for example 2007: =COUNTIFS(B1:B20,D1,B1:B20,E1) with =.4 in D1 and <=.5 in E1 2003 =SUMPRODUCT((B1:B20=D1)*(B1:B20<=E1)) with .4 in D1 and .5 in E1 I have two columns in an excel sheet. One column shows 1 representing male and 2 represnting female. In the second column I have marks scored by males [quoted text clipped - 8 lines] Dolpandotcom 06/16/09 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Excel question
Hi Gord,
Thanks for the link. I did download of the first link which enables multiple data item selection into one cell. I copied the code into the VB editor in my own worksheet. I lunched the MACRO in my vb editor layout, but i did not work as it was with the downloaded sheet. Pls see below the codes if I need to change any instruction : Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String If Target.Count 1 Then GoTo exitHandler On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitHandler If rngDV Is Nothing Then GoTo exitHandler If Intersect(Target, rngDV) Is Nothing Then 'do nothing Else Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value Target.Value = newVal If Target.Column = 3 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ", " & newVal End If End If End If End If exitHandler: Application.EnableEvents = True End Sub Any other person can equally assist in this direction. Thanks. Dolpan Gord Dibben wrote: Question 1. Download a sample workbook from Debra Dalgleish's site. http://www.contextures.on.ca/excelfiles.html#DV0017 Question 2. See Ron de Bruin's site http://www.rondebruin.nl/calendar.htm Question 3. See Chip Pearson's site. http://www.cpearson.com/excel/NoDupEntry.aspx Gord Dibben MS Excel MVP Hi Shane, [quoted text clipped - 41 lines] Dolpandotcom 06/16/09 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200906/1 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Excel question
The code you posted is from the "SameCell" sheet in Debra's sample workbook.
Are you sure you pasted it into the appropriate sheet module in your workbook? This code is event code and is "lunched"(sic) only when a selection is made from a dropdown list in column C If your dropdown is not in column C you must edit this line to your column number If Target.Column = 3 Then Gord On Thu, 18 Jun 2009 12:56:21 GMT, "dolpandotcom via OfficeKB.com" <u52610@uwe wrote: Hi Gord, Thanks for the link. I did download of the first link which enables multiple data item selection into one cell. I copied the code into the VB editor in my own worksheet. I lunched the MACRO in my vb editor layout, but i did not work as it was with the downloaded sheet. Pls see below the codes if I need to change any instruction : Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String If Target.Count 1 Then GoTo exitHandler On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitHandler If rngDV Is Nothing Then GoTo exitHandler If Intersect(Target, rngDV) Is Nothing Then 'do nothing Else Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value Target.Value = newVal If Target.Column = 3 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ", " & newVal End If End If End If End If exitHandler: Application.EnableEvents = True End Sub Any other person can equally assist in this direction. Thanks. Dolpan Gord Dibben wrote: Question 1. Download a sample workbook from Debra Dalgleish's site. http://www.contextures.on.ca/excelfiles.html#DV0017 Question 2. See Ron de Bruin's site http://www.rondebruin.nl/calendar.htm Question 3. See Chip Pearson's site. http://www.cpearson.com/excel/NoDupEntry.aspx Gord Dibben MS Excel MVP Hi Shane, [quoted text clipped - 41 lines] Dolpandotcom 06/16/09 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Excel question
Hi Gord,
Thanks a million. It is working fine. Since I needed it to work on 6 other columns, I decided to make the If Target.Column = 3 Then to be IF Target. Column=3 which makes it to include other columns that I want to use, but I will like two or three items selected in a cell to be counted as one using the COUNT(C8:j8) as an example. Is there any other statement to add to the COUNTI(C8:j8) to enable count two or more items selected in a cell as one (1) please? Thanks. Dolpan Gord Dibben wrote: The code you posted is from the "SameCell" sheet in Debra's sample workbook. Are you sure you pasted it into the appropriate sheet module in your workbook? This code is event code and is "lunched"(sic) only when a selection is made from a dropdown list in column C If your dropdown is not in column C you must edit this line to your column number If Target.Column = 3 Then Gord Hi Gord, [quoted text clipped - 73 lines] Dolpandotcom 06/16/09 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200906/1 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Excel question
=COUNTA(C8:J8) counts multiple entries in a cell as one.
Also......I would change If Target.Column = 3 Then to If Not Application.Intersect(Target, Columns("C:H")) Is Nothing Then Nothing wrong with your revision except it potentially includes columns past H Gord On Thu, 18 Jun 2009 16:05:50 GMT, "dolpandotcom via OfficeKB.com" <u52610@uwe wrote: Hi Gord, Thanks a million. It is working fine. Since I needed it to work on 6 other columns, I decided to make the If Target.Column = 3 Then to be IF Target. Column=3 which makes it to include other columns that I want to use, but I will like two or three items selected in a cell to be counted as one using the COUNT(C8:j8) as an example. Is there any other statement to add to the COUNTI(C8:j8) to enable count two or more items selected in a cell as one (1) please? Thanks. Dolpan Gord Dibben wrote: The code you posted is from the "SameCell" sheet in Debra's sample workbook. Are you sure you pasted it into the appropriate sheet module in your workbook? This code is event code and is "lunched"(sic) only when a selection is made from a dropdown list in column C If your dropdown is not in column C you must edit this line to your column number If Target.Column = 3 Then Gord Hi Gord, [quoted text clipped - 73 lines] Dolpandotcom 06/16/09 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Excel question
I agree with your explanation Gord and also appreciate your explanations, but
take for an example if have a sheet with column label as A, B, C, D with one row of data as shown below:: A B C D 2 2,1 1 If I want to count the numbers that exist on that row as COUNT(Ax:Cx) whrer x is the row number. The result which will be placed in cell Dx will not be 3 simply because the cell Bx is taken as character probably. My humble question is that "How do I use the COUNT function to accomplish the task or is ther any other function that I can use? Thanks. Dolpan Gord Dibben wrote: =COUNTA(C8:J8) counts multiple entries in a cell as one. Also......I would change If Target.Column = 3 Then to If Not Application.Intersect(Target, Columns("C:H")) Is Nothing Then Nothing wrong with your revision except it potentially includes columns past H Gord Hi Gord, [quoted text clipped - 30 lines] Dolpandotcom 06/16/09 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200906/1 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Excel question
I guess I misunderstood this statement................
Is there any other statement to add to the COUNT(C8:j8) to enable count two or more items selected in a cell as one (1) please? You have A1=2, B1=2,1 and C1=1 What do you want as a result in D1? COUNT will give you 2 because it counts numbers only COUNTA will give you 3 which seems to be what your starement above asks for. Do you want to count B1 as two numbers giving you a return of 4 in D1? Gord On Thu, 18 Jun 2009 19:17:08 GMT, "dolpandotcom via OfficeKB.com" <u52610@uwe wrote: I agree with your explanation Gord and also appreciate your explanations, but take for an example if have a sheet with column label as A, B, C, D with one row of data as shown below:: A B C D 2 2,1 1 If I want to count the numbers that exist on that row as COUNT(Ax:Cx) whrer x is the row number. The result which will be placed in cell Dx will not be 3 simply because the cell Bx is taken as character probably. My humble question is that "How do I use the COUNT function to accomplish the task or is ther any other function that I can use? Thanks. Dolpan Gord Dibben wrote: =COUNTA(C8:J8) counts multiple entries in a cell as one. Also......I would change If Target.Column = 3 Then to If Not Application.Intersect(Target, Columns("C:H")) Is Nothing Then Nothing wrong with your revision except it potentially includes columns past H Gord Hi Gord, [quoted text clipped - 30 lines] Dolpandotcom 06/16/09 |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Excel question
Your COUNTA function is absolutely correct. It has done what I want exactly.
Thanks. Dolpan Gord Dibben wrote: I guess I misunderstood this statement................ Is there any other statement to add to the COUNT(C8:j8) to enable count two or more items selected in a cell as one (1) please? You have A1=2, B1=2,1 and C1=1 What do you want as a result in D1? COUNT will give you 2 because it counts numbers only COUNTA will give you 3 which seems to be what your starement above asks for. Do you want to count B1 as two numbers giving you a return of 4 in D1? Gord I agree with your explanation Gord and also appreciate your explanations, but take for an example if have a sheet with column label as A, B, C, D with one [quoted text clipped - 30 lines] Dolpandotcom 06/16/09 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200906/1 |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Excel question
Hi Gord,
Happy Sunday. I am here again. I tried to use the calendar program in a worksheet. I did change the cell where I will like to automate the calendar as so that when one clicks on that cell, the calendar pop up for appropriate date selection. It gave me an error "Run-time error, Object required. Is it that it cannot work in excel2003 or I did not change a statement somewhere in the program? This is the program which I pasted but only changed the cell A1:A20 to A1 since that is the position where I want the calendar to pop up. Private Sub Calendar1_Click() ActiveCell.Value = CDbl(Calendar1.Value) ActiveCell.NumberFormat = "mm/dd/yyyy" ActiveCell.Select End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Application.Intersect(Range("A1"), Target) Is Nothing Then Calendar1.Left = Target.Left + Target.Width - Calendar1.Width Calendar1.Top = Target.Top + Target.Height Calendar1.Visible = True ' select Today's date in the Calendar Calendar1.Value = Date ElseIf Calendar1.Visible Then Calendar1.Visible = False End If End Sub Thanks and enjoy your Sunday. Dolpan Gord Dibben wrote: I guess I misunderstood this statement................ Is there any other statement to add to the COUNT(C8:j8) to enable count two or more items selected in a cell as one (1) please? You have A1=2, B1=2,1 and C1=1 What do you want as a result in D1? COUNT will give you 2 because it counts numbers only COUNTA will give you 3 which seems to be what your starement above asks for. Do you want to count B1 as two numbers giving you a return of 4 in D1? Gord I agree with your explanation Gord and also appreciate your explanations, but take for an example if have a sheet with column label as A, B, C, D with one [quoted text clipped - 30 lines] Dolpandotcom 06/16/09 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200906/1 |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Excel question
I am sending same message again. It was like I did not have a good internet
connection. Find posting below: Hi Gord, Happy Sunday. I am here again. I tried to use the calendar program in a worksheet. I did change the cell where I will like to automate the calendar as so that when one clicks on that cell, the calendar pop up for appropriate date selection. It gave me an error "Run-time error, Object required. Is it that it cannot work in excel2003 or I did not change a statement somewhere in the program? This is the program which I pasted but only changed the cell A1:A20 to A1 since that is the position where I want the calendar to pop up. Private Sub Calendar1_Click() ActiveCell.Value = CDbl(Calendar1.Value) ActiveCell.NumberFormat = "mm/dd/yyyy" ActiveCell.Select End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Application.Intersect(Range("A1"), Target) Is Nothing Then Calendar1.Left = Target.Left + Target.Width - Calendar1.Width Calendar1.Top = Target.Top + Target.Height Calendar1.Visible = True ' select Today's date in the Calendar Calendar1.Value = Date ElseIf Calendar1.Visible Then Calendar1.Visible = False End If End Sub Thanks and enjoy your Sunday. Dolpan Gord Dibben wrote: I guess I misunderstood this statement................ Is there any other statement to add to the COUNT(C8:j8) to enable count two or more items selected in a cell as one (1) please? You have A1=2, B1=2,1 and C1=1 What do you want as a result in D1? COUNT will give you 2 because it counts numbers only COUNTA will give you 3 which seems to be what your starement above asks for. Do you want to count B1 as two numbers giving you a return of 4 in D1? Gord I agree with your explanation Gord and also appreciate your explanations, but take for an example if have a sheet with column label as A, B, C, D with one [quoted text clipped - 30 lines] Dolpandotcom 06/16/09 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200906/1 |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Excel question
Did you first insert the calendar activex form from the control toolboxmore
controls on your sheet? Do you have MS Access installed..................you won't have the needed mscal.ocx file if you haven't. See Ron de Bruin's site for details and instructions. http://www.rondebruin.nl/calendar.htm Gord On Sun, 21 Jun 2009 14:37:16 GMT, "dolpandotcom via OfficeKB.com" <u52610@uwe wrote: Hi Gord, Happy Sunday. I am here again. I tried to use the calendar program in a worksheet. I did change the cell where I will like to automate the calendar as so that when one clicks on that cell, the calendar pop up for appropriate date selection. It gave me an error "Run-time error, Object required. Is it that it cannot work in excel2003 or I did not change a statement somewhere in the program? This is the program which I pasted but only changed the cell A1:A20 to A1 since that is the position where I want the calendar to pop up. Private Sub Calendar1_Click() ActiveCell.Value = CDbl(Calendar1.Value) ActiveCell.NumberFormat = "mm/dd/yyyy" ActiveCell.Select End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Application.Intersect(Range("A1"), Target) Is Nothing Then Calendar1.Left = Target.Left + Target.Width - Calendar1.Width Calendar1.Top = Target.Top + Target.Height Calendar1.Visible = True ' select Today's date in the Calendar Calendar1.Value = Date ElseIf Calendar1.Visible Then Calendar1.Visible = False End If End Sub Thanks and enjoy your Sunday. Dolpan Gord Dibben wrote: I guess I misunderstood this statement................ Is there any other statement to add to the COUNT(C8:j8) to enable count two or more items selected in a cell as one (1) please? You have A1=2, B1=2,1 and C1=1 What do you want as a result in D1? COUNT will give you 2 because it counts numbers only COUNTA will give you 3 which seems to be what your starement above asks for. Do you want to count B1 as two numbers giving you a return of 4 in D1? Gord I agree with your explanation Gord and also appreciate your explanations, but take for an example if have a sheet with column label as A, B, C, D with one [quoted text clipped - 30 lines] Dolpandotcom 06/16/09 |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Excel question
I am using excel2003 and the instuction says that by simply selecting from
the active worksheet excel menu "Object" and further select "Calendar" it will place the calendar on the sheet. It is like the VB code given only works for excel 2007. Am I right pls? Dolpan Gord Dibben wrote: Did you first insert the calendar activex form from the control toolboxmore controls on your sheet? Do you have MS Access installed..................you won't have the needed mscal.ocx file if you haven't. See Ron de Bruin's site for details and instructions. http://www.rondebruin.nl/calendar.htm Gord Hi Gord, [quoted text clipped - 51 lines] Dolpandotcom 06/16/09 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200906/1 |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Excel question
I tested the code using Excel 2003 after InsertObjectCalendar Control 11.0
Works fine for me. Will trigger only when you select A1 and go away when you select any other cell. Maybe you are still in Design Mode? When you xelect the Calendar can you move it or resize it? Open the Control Toolbox and hit the "design mode" icon once to exit from that mode. Gord On Sun, 21 Jun 2009 17:24:31 GMT, "dolpandotcom via OfficeKB.com" <u52610@uwe wrote: I am using excel2003 and the instuction says that by simply selecting from the active worksheet excel menu "Object" and further select "Calendar" it will place the calendar on the sheet. It is like the VB code given only works for excel 2007. Am I right pls? Dolpan Gord Dibben wrote: Did you first insert the calendar activex form from the control toolboxmore controls on your sheet? Do you have MS Access installed..................you won't have the needed mscal.ocx file if you haven't. See Ron de Bruin's site for details and instructions. http://www.rondebruin.nl/calendar.htm Gord Hi Gord, [quoted text clipped - 51 lines] Dolpandotcom 06/16/09 |
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Excel question
I was out of design mode.
I equally selected Calendar Control 9.0. I do not have Calendar 11.o as you did . I was able to resize and also being able to move it from point to the other within the sheet.. I placed my cursor in cell A1 before selecting the Insertobjectcalendar control 9.0. Even after this, if i place my cursor in a different cell, the calendar does not disappear. I need further explanation on what i must have done wrong in mine pls. Hanging by for your response pls. Thanks. Dolpan Gord Dibben wrote: I tested the code using Excel 2003 after InsertObjectCalendar Control 11.0 Works fine for me. Will trigger only when you select A1 and go away when you select any other cell. Maybe you are still in Design Mode? When you xelect the Calendar can you move it or resize it? Open the Control Toolbox and hit the "design mode" icon once to exit from that mode. Gord I am using excel2003 and the instuction says that by simply selecting from the active worksheet excel menu "Object" and further select "Calendar" it [quoted text clipped - 20 lines] Dolpandotcom 06/16/09 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200906/1 |
#23
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Excel question
It is now showing calendar control 11.0 instead of 9.0. Even with that, it
does no disappear when you click a new cell different from the cell where the calendar is inserted. Advice pls. Thanks. Dolpan Gord Dibben wrote: I tested the code using Excel 2003 after InsertObjectCalendar Control 11.0 Works fine for me. Will trigger only when you select A1 and go away when you select any other cell. Maybe you are still in Design Mode? When you xelect the Calendar can you move it or resize it? Open the Control Toolbox and hit the "design mode" icon once to exit from that mode. Gord I am using excel2003 and the instuction says that by simply selecting from the active worksheet excel menu "Object" and further select "Calendar" it [quoted text clipped - 20 lines] Dolpandotcom 06/16/09 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200906/1 |
#24
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Excel question
email me and I'll send you a workbook with the Calendar Control in working
condition. Or send me the workbook may be better. gorddibbATshawDOTca Change AT and DOT Gord On Mon, 22 Jun 2009 18:32:46 GMT, "dolpandotcom via OfficeKB.com" <u52610@uwe wrote: It is now showing calendar control 11.0 instead of 9.0. Even with that, it does no disappear when you click a new cell different from the cell where the calendar is inserted. Advice pls. Thanks. Dolpan Gord Dibben wrote: I tested the code using Excel 2003 after InsertObjectCalendar Control 11.0 Works fine for me. Will trigger only when you select A1 and go away when you select any other cell. Maybe you are still in Design Mode? When you xelect the Calendar can you move it or resize it? Open the Control Toolbox and hit the "design mode" icon once to exit from that mode. Gord I am using excel2003 and the instuction says that by simply selecting from the active worksheet excel menu "Object" and further select "Calendar" it [quoted text clipped - 20 lines] Dolpandotcom 06/16/09 |
#25
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Excel question
Thanks. You can send to me the workbook with the Calendar using this email
below pls: and copy also Cheers, Dolpan. Gord Dibben wrote: email me and I'll send you a workbook with the Calendar Control in working condition. Or send me the workbook may be better. gorddibbATshawDOTca Change AT and DOT Gord It is now showing calendar control 11.0 instead of 9.0. Even with that, it does no disappear when you click a new cell different from the cell where the [quoted text clipped - 24 lines] Dolpandotcom 06/16/09 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200906/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A VERY General question | Excel Worksheet Functions | |||
General question about 2003 and 2007 VBA in Excel | Excel Discussion (Misc queries) | |||
Question For Importing Data Into Excel/Converting General Number to Dollar Amount | Excel Discussion (Misc queries) | |||
General Question | Excel Discussion (Misc queries) | |||
General Question | Excel Discussion (Misc queries) |