If a cell has a specific word in it, copy data from other cells into a different worksheet

Hi

Let's say i will be using 1 of 6 words (cell a13) to categorise a row of data (cells b13 - q13) in a worksheet.

I then have 6 other worksheets each named after the 6 words.

Let's say the category words/worksheet names are A, B, C, D, E & F.

If a13 = A then put the values in cells b13 - q13 into worksheet A, cells b13 - q13
If a13 = B then put the values in cells b13 - q13 into worksheet B, cells b13 - q13 etc

Hope that's clear & someone can help.

Thanks,
Al.

Hi

Thanks for replying.

Copy of the spreadsheet attached.

The WT column will have 1 of 6 words in it. These will relate to the worksheets of the same name.

So all values/data in the corresponding rows b-q (PS# - ATP) will populate the relevant worksheet (ie all rows of data b-q with a WT of ADP will copy over into the ADP worksheet)

Thanks again,
Al.

Hi Dili

What do i need to do/change to the formula so it looks at all entries in the A column of the 10032 worksheet, & if it sees 1 of the 6 category names copy the data (b-q) into the relevant worksheet?
I assumed "filename" is the variable so changed that to ADP, JLP, BRANCH etc, but no good.
Your formula also references cell A1, why is that?

Thanks,
Al.

Hi Dili

I think we might be getting confused.

What i want is for each of the 6 worksheets (JLP - ADP) to search column A of the 10032 worksheet.
If it comes across 1 of the 6 names in column A, then to copy the corresponding row (b-q) over into the worksheet of the same name.
eg worksheet ADP is searching worksheet 10032 column A, & every ADP it finds to copy the data in cells b-q into worksheet ADP.
worksheet BOOTS is searching worksheet 10032 column A, & every BOOTS it finds to copy the data in cells b-q into worksheet BOOTS.

I'd love it if excel had a function to do this, however, the more i look into it the more i think i need to go down the VBA/macro route.

What do you think?

Thanks,
Al.

...or

Worksheet 10032 searches itself (column A) & if it finds 1 of the 6 names copies the corresponding row data (b-q) into the relevant worksheet