populate row information based on cell condition or cell value

do you want to identify the page or move the info.
Identify the page - set up a range with Row: 0,31,37 & Another Row Page #
Use Match to identify the correct column (Policy in Row 2 would return 3, Policy in Row 3 would return 1)
Then you could use lookup or index to return the result you want.

Do you want the whole of the policy information copied to the relevant sheet, or just parts of it?

Pete

Columns C, E, H, P are the columns I am most interested in. Policy #, policy name, months since review, and comments.

Here is where I am so far with the retrofitting of a formula


Excel Forum Version Policy Spreadsheet.xlsm

Not only does it still not work, but it won't even tell my why. This is the formula you see entered in A1 of '>60 days overdue'

=IFERROR(INDEX(Master!$A$3:$P$6,SMALL(IF(Master!$H$3:$H$6>170,ROW(Master!$H$3:$H$6)-1),ROWS($H$1:H1)),MATCH(I$1,Master!$H$3:$P$3,)),"")

yes I entered it as an array (cntrl, alt, enter)

Please tell me why this is not working and how to get it to work. You can tell by reading the tabs how I am wanting to sort this data.

I've attached the file that I worked on when I first replied to you - I've been waiting for you to get back to me.

In the main sheet I inserted two new columns (S and T) and put this formula in T2:

=IF(H2="","-",MATCH(H2,{0,31,37,61})&"_"&COUNTIF(T$1:T1,MATCH(H2,{0,31,37,61})&"_*")+1)

and copied this down to beyond your data - the hyphens show where the formula is active. The formula identifies which of the 4 age groups column H falls into, and then adds a sequential number to the end of this, separated by an underscore. Consequently, each record has a unique identifier. I've also added two test records to show the effect of the formula more clearly.

In the subsidiary sheets I've inserted a new row 1 and put the numbers 1 to 4 in cell A1 to correspond to the appropriate age group. I've also put this formula in cell S5:

=IFERROR(MATCH($A$1&"_"&ROWS($1:1),'All Policies'!$T:$T,0),"-")

and again this is copied down beyond the data expected (to row 20 in the example file). This formula finds the row in the main sheet where the record occurs, starting with record 1 for that age group and continuing down the page (with a hyphen showing where the record does not exist.

In cell A5 of the subsidiary sheets I've used this formula:

=IF(OR($S5="-",$S5=""),"",IF(INDEX('All Policies'!A:A,$S5)="","",INDEX('All Policies'!A:A,$S5)))

which brings the data from column A of the main sheet where column S indicates it is present, and ensures that blank entries are shown as blank. This formula is copied across to column R, so that it brings all the fields for that record, and the appropriate formatting has been applied to each cell using the Format Painter from the main sheet. The row of formulae have then been copied down to row 20 in each of the subsidiary sheets.

Hope this helps.

Pete

This solution is as complete as it is complex. I completely appreciated it, as it appears to be one that might be more responsive to retrofitting in the future. Thank you for the time it obviously took to revamp the book instead of just offer a solution in the form of a formula. The explanation helps me understand it much better than if it had not been there.

Glad to be of help - thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete