Multi-Worksheet results needed with Rules applied

**vortexkid** · 03-19-2012, 08:07 PM

4 Worksheets, Text oriented, no numbers, no percentages

I would like to have a specific "by-name result" posted to a separate worksheet, within the same workbook, based on the result of the text entered by the user. I have it working 50%, but I am looking to mass produce the formula congruent with the Main Spredsheet.

Example: person data sheet with the text "paid/not paid/pending" variable is in cells J3-J258 for each registrant. Based on the result of the text entered in cells J3-J258, I need the specific result "not paid" to produce that registrants entered Name from cells B3-B258 auto-filled in my Worksheet titled "Registrants Not Paid" in no specific order.

This formula partially works, but will not Copy/Paste and increase in value with each cell vertically.

=IF('Registration Data - ALL'!J3="not pd",'Registration Data - ALL'!B3,"") (Note= The resulting cell will remain an empty result "" unless the variable "not paid" is entered in the data worksheet)

How can I copy/paste the formula and auto-increase the cell J4/B3, J5/B5, J6/B6etc...

Thank you for your wisdom.

**Pete_UK** · 03-20-2012, 05:06 AM

Post a copy of your workbook and I'll put some formulae within it to do this for you.

Hope this helps.

Pete

**vortexkid** · 03-21-2012, 01:46 PM

File attached. Appreciate the look...

Sample Online Registrations 2012.xlsx

(all detailed info removed for security reasons)

**Pete_UK** · 03-21-2012, 02:48 PM

I've put this formula in cell L3 of your "All" sheet:

=IF(J3="","-",J3&"_"&COUNTIF(J$3:J3,J3))

and copied it down to row 381, i.e. to encompass the data that you have. The hyphen indicates where you have copied to, and the formula should be copied further if you add more data beyond that row. The formula returns a unique sequential reference for each row of data that you have, categorised by the type.

In your "Unpaid" sheet, I have added this formula to cell A5:

=IF(ISNA(MATCH("not pd_"&ROWS(A$5:A5),'Registration Data - ALL'!L:L,0)),"-",MATCH("not pd_"&ROWS(A$5:A5),'Registration Data - ALL'!L:L,0))

and this returns the row number from the "All" sheet where the "not pd" records can be found. The following two formulae in the cells stated:

B5: =IF(OR($A5="-",$A5=""),"",INDEX('Registration Data - ALL'!$B:$B,$A5))

C5: =IF($B5="","",INDEX('Registration Data - ALL'!$E:$E,$A5))

return the corresponding name and email address for the "not pd" records. I have copied these formulae down to row 50, though you can copy them further if you expect more records.

The list will automatically adjust if you add more data to the "All" sheet, or if you change any of the status fields to/from "not pd".

Hope this helps.

Pete