Automating Tables

[Brew]

I would like to program 3 tables within excel that automatically updates the following data:

in U column
how many 3s, 2s 1s, and 0s in the last 10 entries and the percentages for each.
Also, how many for the last 100 entries and their percentages.
I have a 2nd column (column EM) that produces 0s, 1s, 2s and 3s. I need the last 10 entries and the percentages for each.
Also, how many for the last 100 entries and their percentages.
I would like to do the same for the data in a 3rd column (AV column) which has only 1s, 2s and 3s.

Some cells in the columns are blank, so ingnore those as non-entries.

PC Pick3 System Chart3.xls
	EC	ED	EE	EF	EG	EH	EI	EJ	EK	EL
21	E/O(ColumnU)						D/S(ColumnAV)
22		3	2	1	0			1	2	3
23	Last10						Last10
24	%						%
25	Last100						Last100
26	%						%
27
28
29	H/Low(ColumnEM)
30		3	2	1	0
31	Last10
32	%
33	Last100
34	%
combo

PC Pick3 System Chart3.xls
	EM	EN	EO	EP
51
52
53	2
54
55	2
56
57	1
58
59	0
60
61	1
62
63	1
64
combo

 

[Yogi Anand]

Hi Brew:

Hi Brew:
To automate the process, we need to do the following …

1. We have to use a dynamic range --so when you make a new entry, it is accounted for
a) to do that for column B entries, I have set up a dynamic range ...
yDynRng … =Sheet3a!$B$5:INDEX(Sheet3a!$B:$B,MATCH(MAX(Sheet3a!$B:$B)+1,Sheet3a!$B:$B))
b) I changed the formula in cell E2 making part of the AdvancedFilter Criteria to
=COUNT(B6:$B$65536)<=$B$2

2. Create a Worksheet_Change event that would execute Advanced Filter on making an additional numeric entry in column B
a) the Worksheet_Change event code is ...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("yNewEntryLocation")) Is Nothing Then
        Range("yDynRng").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("D1:E2"), CopyToRange:=Range("D5")
    End If
End Sub

b) I created a range named yNewEntryLocation ...
y NewEntryLocation … =INDEX(Sheet3a!$B:$B,MATCH(MAX(Sheet3a!$B:$B),Sheet3a!$B:$B))

I have used the dynamic ranges and the Worksheet_Change code in the following illustration ...

y040121h1a.xls
	B	C	D	E	F	G	H	I	J
1	CountLast
2	10		FALSE	FALSE
3	numericentries
4
5	entry		entry		NumberOf	3	2	1	0
6	blank		1			3	3	2	2
7	text		3
8	2		2
9	3		0			source
10			2			criteria
11	1		3			copyto
12	3		0
13	text		3
14	1		2
15	text		1
16	3
17	2
18	0
19	2
20	3
21	0
22	3
23	2
24	k
25	1
Sheet3a

I hope this helps!

 

[Brew]

Where do I place the following in my sheet:
yDynRng … =Sheet3a!$B$5:INDEX(Sheet3a!$B:$B,MATCH(MAX(Sheet3a!$B:$B)+1,Sheet3a!$B:$B))
and
y NewEntryLocation … =INDEX(Sheet3a!$B:$B,MATCH(MAX(Sheet3a!$B:$B),Sheet3a!$B:$B))

Also, I guess the code is to be place in the module.

Also, I assume I have to change your "sheet3a" reference to "combo" since that is the name of my sheet, and also change my column references, since it is different in my sheet.

 

[Yogi Anand]

Where do I place the following in my sheet:
yDynRng … =Sheet3a!$B$5:INDEX(Sheet3a!$B:$B,MATCH(MAX(Sheet3a!$B:$B)+1,Sheet3a!$B:$B))
and
y NewEntryLocation … =INDEX(Sheet3a!$B:$B,MATCH(MAX(Sheet3a!$B:$B),Sheet3a!$B:$B))

The range names are created from within the sheet of interest by invoking INSERT|Name|Define and then proceed from there.

Also, I guess the code is to be place in the module.

The code is to be placed in the Worksheet's module and not in a general module.

Also, I assume I have to change your "sheet3a" reference to "combo" since that is the name of my sheet, and also change my column references, since it is different in my sheet.

Yep, that's right!

Good Luck!

 

[Brew]

How do I add this code to the worksheet module, when I already have code in the worksheet module for another application within my chart?

Also, is this adjustment to your formula okay:
=COUNT(U38:$U$65536)<=$ED$19
I placed 10 in the ED19 cell. does it have to be in the U column?

I believe have everything else cell up correctly.
So when I add a couple row with one additional numeric entry how when the chart update? do I have to press a key?

Also, what automation adjustments do I make when I want to look at 100 entries, instead of 10 or a different column of data entry?

 

[Yogi Anand]

How do I add this code to the worksheet module, when I already have code in the worksheet module for another application within my chart?

One can have more than one worksheet event code associated with a worksheet -- but not knowing what event you already have in the worksheet, I can not say if there will be a conflict or whther they will both work in harmony.

Also, is this adjustment to your formula okay:
=COUNT(U38:$U$65536)<=$ED$19
I placed 10 in the ED19 cell. does it have to be in the U column?

Yes this seems to be OK -- the intent here is that count of numeric entries in all the cells in column U starting with U38 and going all the way down should be <= the entry in cell ED19.

I believe have everything else cell up correctly.
So when I add a couple row with one additional numeric entry how when the chart update? do I have to press a key?

It is the purpose of the Worksheet_Change event to monitor that a numeric entry has been made at the end of the range of numeric entries in column U and then fire up accordingly.

Also, what automation adjustments do I make when I want to look at 100 entries, instead of 10 or a different column of data entry?

I would rather have everything working for the count of 10 and not confound it with the matter of 100 entries at this time.

Good Luck Brew!

 

[Brew]

I added 2 rows to the column with one new numeric entry,
but the table and the coppyto range did not update.
row 299 was blank and row 300 had a #1.

 

[Yogi Anand]

Hi Brew:

At this point. I can not tell you what might be happening. It is working for me. It seems like a fairly large project -- for this you may need more than just help on the board. Send me a PM with some details of the project -- and then let us take it from there.

 

[Brew]

I updated the formula references as you suggested.
But I am getting an "ambigious name detected" error in the worksheet module. How do I fix that error?

 

[Yogi Anand]

Hi Brew:

Please check if you have two code modules by the same name -- each code module needs to have a unique name.

 

[Brew]

I now need a formula to look at the U column for the last 30 numerical entries, ignoring the blank or empty cells and then list the number in another column (EO35:EO65), each numerical entry in the order it occurred in the U column, and excluding empty cells. Everytime, I add a new value in the U column the values in EO35:EO65 should update accordingly.
Also, I want to take the last 60 numerical entries in AV and place in column ER5:ER65 in the exact order occurred excluding empty cells, then copy and paste only the values in the 30 odd numbered ER cells, starting with ER5 and ending with ER65, place those values in the order they occurred into EQ35:EQ65.