Originally Posted by
teo_7
I have a worksheet with many named tables (the names are given automatically through a VBA script) updated in a regular basis with the same labels at each first row.Let's say
A1:E12 is TABLE1,
A13:E48 is TABLE2...
A1200:E1261 is TABLE90 etc....
I think there are reasons needed to name multi ranges, i.e TABLE1 for list of employees of AA company, TABLE2 for BB company ...
As per Fortis1991 in #6, column name (AGE45, SALARY45 ...) were fixed to TABLE45 only.
What happen if seaching to TABLE90 or others?
Therefore, in general, my solution is:
1- Name all the column ACROSS all table (AGE, SALARY, CHILDREN)
2- Crossing the TABLE and column with criteria like this: (TABLE45 AGE>=45)*(TABLE45 SALARY=15000)...then use SUMPRODUCT to count.
So, the formula is:
with H1: Table Name (TABLE45)
H2 = Age (45)
H3 = Salary (15000)
H4 = Children (2)
User can change H1,2,3,4 to get expected result.
Bookmarks