FORMULA Which searches one column for a name, then copies all Data in that row to a sheet!

[WaylettChris]

Hey All,

I have attached a spreadsheet to look at.

What I need to do is: On the sheet named "MAIN INPUT" I will log all the data. Then I will have a seperate sheet named for each persons individual errors. What I want is to make it so it searches Column B for the NAME of the person who made the error. Then to copy the data in columns A to O for that row to a seperate sheet. I want it to copy the data to the sheet with name that matches the person in column B...If this is making sense so far?

On the individual peoples sheets, I want no duplicates to be copied, and I want them to be copied in the same order as they appear on the Main Input sheet. With no gaps so they appear sequencially with no spaces.

Not sure whether formulas or Macros are best for this....anyway...HELP!!!

Any Q's do let me know!

Thanks!

Chris

[NBVC]

Add this formula to P2 of the main sheet:

=B2&"_"&COUNTIF(B$2:B2,B2)

copied down

Then Select the first name sheet, hold the Shift key and then select the last name sheet... this groups all the sheets.

In A1 of the visible sheet enter formula:

=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,255)

This should get the person's name for that sheet.

Then in a row below that enter formula:

=IFERROR(INDEX('MAIN INPUT'!A:A,MATCH($A$1&"_"&ROWS($A$1:$A1),'MAIN INPUT'!$P:$P,0)),"")

copied across to column O and down as far as you want.

you may need to format date columns as Date

Then you can right-click any sheet and Ungroup Sheets.

Now each sheet should have it's respective data from the main one.

[DannyJ]

Hi Chris,

Try this and drag it down and across. You will need to put the name of each person in the worksheet (This formula says B1)

=INDEX('MAIN INPUT'!$A1:$O28,MATCH($B$1,'MAIN INPUT'!$B1:$B100,0),1)

EDIT:NBVC does things fancy, too clever for me to compete with!!

Danny

[WaylettChris]

NBVC!

You are a genius!

DannyJ - Yours does also work, but I don't find it as easy to get on with.

Thanks both so much for your help!

Legends!

[dwint]

write down this code in VBE module

Please Login or Register  to view this content.

please see attached file