Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use INDIRECT
I have the following formula:
=SUMPRODUCT((A8:A100<"")/COUNTIF(A8:A100,A8:A100&"")) This formula counts the number of unique records in a column (A) The column (A) grows every day to a variable amount I need to replace the (100) by the value of a cell that counts the row in column (A) I tried all the variations of "INDIRECT) but it doesn't seem to work. Obviously my syntax is not right. Any pointer will be gratefully appreciated. Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use INDIRECT
=SUMPRODUCT((A8:INDEX(A8:A1000,COUNTA(A:A))<"")/COUNTIF(A8:INDEX(A8:A1000,COUNTA(A:A)),A8:INDEX(A8 :A1000,COUNTA(A:A))&""))
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Leon" wrote in message ... I have the following formula: =SUMPRODUCT((A8:A100<"")/COUNTIF(A8:A100,A8:A100&"")) This formula counts the number of unique records in a column (A) The column (A) grows every day to a variable amount I need to replace the (100) by the value of a cell that counts the row in column (A) I tried all the variations of "INDIRECT) but it doesn't seem to work. Obviously my syntax is not right. Any pointer will be gratefully appreciated. Thanks in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use INDIRECT
Hi, try this. Pop it in the Worksheet_Change event.
Dim rgN As Range Application.EnableEvents = False If Target.Column = 1 Then Set rgN = Range("A1", Range("a65536").End(xlUp).Address) Cells(1, 2).Value = _ "=SUMPRODUCT((" & rgN.Address & "<" & Chr$(34) & Chr$(34) & _ ")/COUNTIF(" & rgN.Address & "," & rgN.Address & _ "&" & Chr$(34) & Chr$(34) & "))" Set rgN = Nothing End If Application.EnableEvents = True "Leon" wrote: I have the following formula: =SUMPRODUCT((A8:A100<"")/COUNTIF(A8:A100,A8:A100&"")) This formula counts the number of unique records in a column (A) The column (A) grows every day to a variable amount I need to replace the (100) by the value of a cell that counts the row in column (A) I tried all the variations of "INDIRECT) but it doesn't seem to work. Obviously my syntax is not right. Any pointer will be gratefully appreciated. Thanks in advance |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use INDIRECT
HI bob,
I tried to copy and paste your formula but it doesn't work. I looked on some excel websites and it seems that the INDIRECT function is the one to use for this kind of stuff. Thanks anyway. "Bob Phillips" wrote: =SUMPRODUCT((A8:INDEX(A8:A1000,COUNTA(A:A))<"")/COUNTIF(A8:INDEX(A8:A1000,COUNTA(A:A)),A8:INDEX(A8 :A1000,COUNTA(A:A))&"")) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Leon" wrote in message ... I have the following formula: =SUMPRODUCT((A8:A100<"")/COUNTIF(A8:A100,A8:A100&"")) This formula counts the number of unique records in a column (A) The column (A) grows every day to a variable amount I need to replace the (100) by the value of a cell that counts the row in column (A) I tried all the variations of "INDIRECT) but it doesn't seem to work. Obviously my syntax is not right. Any pointer will be gratefully appreciated. Thanks in advance |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use INDIRECT
Hi Hector,
I would gladly follow your advice if I only knew how to insert your macro? in Worksheet_Change event. And if I do where would the value show? Thanks "Hector" wrote: Hi, try this. Pop it in the Worksheet_Change event. Dim rgN As Range Application.EnableEvents = False If Target.Column = 1 Then Set rgN = Range("A1", Range("a65536").End(xlUp).Address) Cells(1, 2).Value = _ "=SUMPRODUCT((" & rgN.Address & "<" & Chr$(34) & Chr$(34) & _ ")/COUNTIF(" & rgN.Address & "," & rgN.Address & _ "&" & Chr$(34) & Chr$(34) & "))" Set rgN = Nothing End If Application.EnableEvents = True "Leon" wrote: I have the following formula: =SUMPRODUCT((A8:A100<"")/COUNTIF(A8:A100,A8:A100&"")) This formula counts the number of unique records in a column (A) The column (A) grows every day to a variable amount I need to replace the (100) by the value of a cell that counts the row in column (A) I tried all the variations of "INDIRECT) but it doesn't seem to work. Obviously my syntax is not right. Any pointer will be gratefully appreciated. Thanks in advance |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use INDIRECT
Well you could use INDIRECT, I prefer to use INDEX.
In what way does it not work. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Leon" wrote in message ... HI bob, I tried to copy and paste your formula but it doesn't work. I looked on some excel websites and it seems that the INDIRECT function is the one to use for this kind of stuff. Thanks anyway. "Bob Phillips" wrote: =SUMPRODUCT((A8:INDEX(A8:A1000,COUNTA(A:A))<"")/COUNTIF(A8:INDEX(A8:A1000,COUNTA(A:A)),A8:INDEX(A8 :A1000,COUNTA(A:A))&"")) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Leon" wrote in message ... I have the following formula: =SUMPRODUCT((A8:A100<"")/COUNTIF(A8:A100,A8:A100&"")) This formula counts the number of unique records in a column (A) The column (A) grows every day to a variable amount I need to replace the (100) by the value of a cell that counts the row in column (A) I tried all the variations of "INDIRECT) but it doesn't seem to work. Obviously my syntax is not right. Any pointer will be gratefully appreciated. Thanks in advance |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use INDIRECT
Sorry Leon, I assumed that as you posted to Programming it would all make
sense. Now for some explanations. Sorry if this is too much. No offence intended. With the workbook open (or a copy of it!), check first that you have the Visual Basic menu. Right click on menu bar, check/select Visual Basic if it is not already checked. If the menu entry isn't available you will need to install it from your original installation CDs. Click the Visual Basic Editor icon (tooltips will show it when you hover over). In the left window you will see a list of worksheets in your workbook. Double click on the name of the sheet that this formula will be stored in. In the right-hand window, click the left-hand drop down box and select Worksheet. In the right-hand window, click the right-hand drop down box and select Change. Paste the code in-between the 'Private Sub...' and 'End Sub' lines. This code will run every time there is a change on the chosen worksheet. The satement 'If Target.column = 1' traps explictly for column A. The next line finds and sets the address of the last row containing data in column A. Just noticed that I started my range from Row 1, whereas yours was Row 8 - it was late!. So that line should read:- Set rgN = Range("A8", Range("A65536").End(xlUp).Address) Your original formula is then built using the address of the last cell containing data and puts that in cell B2 - Cells(1,2) = the cell in row 1, column 2. You probably want the formula in a different location so change that as you wish. Return to the worksheet, make a change in the data in column A, or add a new piece and then you will see the result of the formula in your chosen location. h. "Leon" wrote: Hi Hector, I would gladly follow your advice if I only knew how to insert your macro? in Worksheet_Change event. And if I do where would the value show? Thanks "Hector" wrote: Hi, try this. Pop it in the Worksheet_Change event. Dim rgN As Range Application.EnableEvents = False If Target.Column = 1 Then Set rgN = Range("A1", Range("a65536").End(xlUp).Address) Cells(1, 2).Value = _ "=SUMPRODUCT((" & rgN.Address & "<" & Chr$(34) & Chr$(34) & _ ")/COUNTIF(" & rgN.Address & "," & rgN.Address & _ "&" & Chr$(34) & Chr$(34) & "))" Set rgN = Nothing End If Application.EnableEvents = True "Leon" wrote: I have the following formula: =SUMPRODUCT((A8:A100<"")/COUNTIF(A8:A100,A8:A100&"")) This formula counts the number of unique records in a column (A) The column (A) grows every day to a variable amount I need to replace the (100) by the value of a cell that counts the row in column (A) I tried all the variations of "INDIRECT) but it doesn't seem to work. Obviously my syntax is not right. Any pointer will be gratefully appreciated. Thanks in advance |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use INDIRECT
PS....
The line - Cells(1,2).Value - should be Cells(1,2).Formula Did I mention it was late <g h. "Hector" wrote: Sorry Leon, I assumed that as you posted to Programming it would all make sense. Now for some explanations. Sorry if this is too much. No offence intended. With the workbook open (or a copy of it!), check first that you have the Visual Basic menu. Right click on menu bar, check/select Visual Basic if it is not already checked. If the menu entry isn't available you will need to install it from your original installation CDs. Click the Visual Basic Editor icon (tooltips will show it when you hover over). In the left window you will see a list of worksheets in your workbook. Double click on the name of the sheet that this formula will be stored in. In the right-hand window, click the left-hand drop down box and select Worksheet. In the right-hand window, click the right-hand drop down box and select Change. Paste the code in-between the 'Private Sub...' and 'End Sub' lines. This code will run every time there is a change on the chosen worksheet. The satement 'If Target.column = 1' traps explictly for column A. The next line finds and sets the address of the last row containing data in column A. Just noticed that I started my range from Row 1, whereas yours was Row 8 - it was late!. So that line should read:- Set rgN = Range("A8", Range("A65536").End(xlUp).Address) Your original formula is then built using the address of the last cell containing data and puts that in cell B2 - Cells(1,2) = the cell in row 1, column 2. You probably want the formula in a different location so change that as you wish. Return to the worksheet, make a change in the data in column A, or add a new piece and then you will see the result of the formula in your chosen location. h. "Leon" wrote: Hi Hector, I would gladly follow your advice if I only knew how to insert your macro? in Worksheet_Change event. And if I do where would the value show? Thanks "Hector" wrote: Hi, try this. Pop it in the Worksheet_Change event. Dim rgN As Range Application.EnableEvents = False If Target.Column = 1 Then Set rgN = Range("A1", Range("a65536").End(xlUp).Address) Cells(1, 2).Value = _ "=SUMPRODUCT((" & rgN.Address & "<" & Chr$(34) & Chr$(34) & _ ")/COUNTIF(" & rgN.Address & "," & rgN.Address & _ "&" & Chr$(34) & Chr$(34) & "))" Set rgN = Nothing End If Application.EnableEvents = True "Leon" wrote: I have the following formula: =SUMPRODUCT((A8:A100<"")/COUNTIF(A8:A100,A8:A100&"")) This formula counts the number of unique records in a column (A) The column (A) grows every day to a variable amount I need to replace the (100) by the value of a cell that counts the row in column (A) I tried all the variations of "INDIRECT) but it doesn't seem to work. Obviously my syntax is not right. Any pointer will be gratefully appreciated. Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
INDEX("a"&COLUMNS(INDIRECT(A1):INDIRECT(A2)):A30,4) | Excel Discussion (Misc queries) | |||
INDIRECT? | Excel Worksheet Functions | |||
Help with INDIRECT() | Excel Discussion (Misc queries) | |||
Need help with Indirect | Excel Discussion (Misc queries) |