[dannac]

Workbook has 2 sheets.
Sheet 1 has all the data.
Workers in col A and the amount of rows changes.
Categories are listed in B1:X1.

Sheet 2 has a validation dropdown in cell A1.
It references a named range from sheet 1 (B1:X1)

Workers have info under different categories.

From sheet 2 I would like to choose a name from the dropdown in A1.
The macro would search categories on Sheet 1 that match dropdown.

Then copy the worker's entire row... only if they have info under that category.

Row would be copied to sheet 2 starting in cell A3.

[ScottGem]

Excel has a macro recorder. You can try recording the steps you take, then view the generated code to make it more dynamic.

[dannac]

I have used macro recorder for simple things.

I do not believe this can even be started, much less accomplished with the macro recorder.

Not a simple task.

Thanks for your reply.

[ScottGem]

You should be able to record a macro to copy a range from one area to another. Then look at the selection process.

At least it will be a start.

[dannac]

Yes I can record a copy operation with a macro.

I do not see how to record a "find" or "search" operation with a macro.

I'm hoping the "Software Expert" can help.

[JBeaucaire]

Your question first states sheet2 A1 would have a drop down comprised of B1:X1 from SHeet1, that's the categories.

Then you state you'd like to choose a worker name in A1. You would need a second cell, perhaps B1 to select the worker name?

Else your requirements are unclear. Please restate them step by step.

Sheet1
1) Names in column A
2) Categories in Row 1

Sheet2
3) A1 has a drop down with categories from sheet1
4) B1 has a drop down with names from sheet1

Event
5) When a category is A1 AND a name in B1 are selected, the following happens:

- - ?
- - ?
- - ?

Please be very specific. Thanks.

[dannac]

Morning JB... you up early.

I do not believe I stated I would choose a worker name.

In fact, I should not have mentioned anything about the worker names as it does not look for worker name.

It would search category only.

It finds the category and copies the entire row of "non-blank" cells under that category to Sheet 2 starting at A3.

Thanks for replying

[dannac]

If I would search for Cat3 from the dropdown on Sheet 2

It would return row 3, 6, and 8 to Sheet 2 starting at A3.

[JBeaucaire]

Like so, see attached.

[dannac]

Exactly... thanks very much JB.