How to use INDIRECT

[Guest]

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

 

[Bob Phillips]

=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)

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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.

 

[Guest]

PS....

The line - Cells(1,2).Value - should be Cells(1,2).Formula

Did I mention it was late <g>

h.