Populate Data Based on Multiple Criteria

Hi,

See the attached. I have used dynamic range names for the data, and proper dates for the two result tables.

I also think your PLoan Commission count of 1 is wrong since the only Ploan record shows a 'N' in compliance.

Regards

Hi,

Dynamic range names as you probably know are useful when you are adding new records to a database and wish to include the records in a range name.

They use the standard OFFSET() function which takes four arguments, to define a range of cells. A cell (or range) reference and then four numbers. The first two define the upper left cell of the new range and are a) the number of rows to offset and b) the number of columns to offset. Having established the upper left cell, the third and fourth numbers define how tall and how wide the new range should be.

So for instance in the range I named channel, the formula is

=OFFSET('Data Table'!$D$5,0,0,COUNTA('Data Table'!$B:$B),1)

Here D5 is the first cell in the data records channel column. The formula makes this an absolute reference with $D$5, and continues with ‘0,0’ which says don’t offset by any rows or columns, hence the upper left cell remains at D5.

The COUNTA() function counts the number of non blank cells in a range, and hence in this formula they count the number of such cells in column B of the data table. i.e. the formula automatically expands the height of the defined name every time a new entry is made in column B. The final ‘,1’ simply says that the new range is 1 column wide, i.e. just column D.

One point to note with these formulae where you are creating several names for ranges within the same database, is always to be consistent with the first bit of the COUNTA() function, i.e. the $B:$B in this example. Always choose a column which will always have a value on every row of the database. The reason is this. Some columns will not have values in every row and if you use these columns to count the number of rows in order to establish the height of the range it will not cover all the rows of the database.

I wasn't quite sure what you meant by the second part about using a formula to populate a range since of course a formula will only return a single value, but I've assumed you mean that you want to filter a list of records from the database. You would do this with a Data Filter Advanced filter and specify the database and a criteria and output range. In the attached I've created three new names, 'Data', 'Data_Out' and 'Crit' and used these names in a macro which will do the data filter for you by clicking the blue button.

HTH