I'm looking at an excel work-book with potential definitions for a column(upto 135) referenced from an adjacent sheet.
E.g. Sheet 1: Col C (values 0-134 defined in Sheet 2 ) E.g. 0,1,8 Each row in Col C Sheet 1 may have any combination of the values separated by commas.
Imagine a workbook with two sheets named 'Student', and 'Team names' Each row in the sheet 'Team names' lists the ID of a team. A student may play on more than one team.
Say there are 3 teams they would appear on Sheet 'Team names' as
A2 = 20
Say now that there are 4 students whose roll.no appears on Sheet 'Student' as A1 = 13, A2=27, A3=43, A4=53.
Roll.No 13 Col B 10, 20, 30
Roll.No 27 Col B 30, 10
Roll.No 53 Col B 20
Roll.No 43 Col B 30, 20
The intent here is to setup a formula such that in the sheet 'Team names'
10 Col B 2
20 Col B 3
30 Col B 3
Sheet 2 must maintain statistics of the occurence of each value. This is done using COUNTIF. The issue here is that COUNTIF doesn't handle an embedded value too well. When so done, the reference to '1' in the above example won't appear.
How can this be done without resorting to a sub-routine?