Transpose only highlighted cells from one sheet to another sheet

[Justin_Blush]

I am currently working on a spreadsheet that highlights cells with certain text based upon a category chosen in a drop down box at the top of the spreadsheet.  The cells are listed in columns with headings.  I want the highlighted cells from that sheet to auto-populate on a subsequent sheet in rows instead of columns.  To be clear I am wanting only the highlighted cells to transfer to the subsequent sheet when a selection is made, while leaving out the non-highlighted cells from the original sheet.

Thank you in advance for your assistance!

[#2]

@Justin_Blush 

To be clear I am wanting only the highlighted cells to transfer to the subsequent sheet when a selection is made, while leaving out the non-highlighted cells from the original sheet.

Actually, I thought that part was already clear.

I am currently working on a spreadsheet that highlights cells with certain text based upon a category chosen in a drop down box at the top of the spreadsheet. The cells are listed in columns with headings. I want the highlighted cells from that sheet to auto-populate on a subsequent sheet in rows instead of columns.

What still needs clarification is the arrangement of these columns in which the highlighted cells are to be found. For example:

• How many (more curiosity than necessity) columns are there?
• Are the highlightings randomly scattered throughout those columns?
• Are there multiple highlighted cells in any one column?
• Are there columns that don't have highlighted cells?
• Are all highlighted cells on one row, as identified by the "certain text chosen in a drop down box"?
• Is the "certain text chosen" the same as the text in the highlighted cells, or different?
• Are the highlighted cells different from the "certain text chosen" but all the same as one another?

I think you get the idea.

It might also be helpful to post a copy of the sheet, so those of us who would like to help don't need to create our own and have it end up not being representative of what you're actually dealing with.

[#3]

@mathetes I have attached a copy of the workbook but I had to remove some of the information from the original version.  You should be able to see what is occurring on the sheets.  The first sheet is search terms.  At the top of the second sheet are a couple drop down lists to choose categories that highlights cells based upon the selection made.  Even though I have removed information you will be able to see how many original columns exist on sheet 2 and how the highlighted cells change based upon selection.  There will be corresponding rows on sheet three and the goal would be to have only the information being highlighted on sheet two to end up on sheet 3.  Let me know if I can answer questions.  If you are able to help me understand the programming equation needed and how to apply it then I can build off of that.  Also see below for your questions answered.  Thank you sir.

• How many (more curiosity than necessity) columns are there?  21
• Are the highlightings randomly scattered throughout those columns?  YES
• Are there multiple highlighted cells in any one column?  YES
• Are there columns that don't have highlighted cells?  SOMETIMES
• Are all highlighted cells on one row, as identified by the "certain text chosen in a drop down box"?  NO, IT IS RANDOMLY SCATTERED AMONG THE COLUMNS
• Is the "certain text chosen" the same as the text in the highlighted cells, or different? IT IS THE SAME AND CAN BE VIEWED ON SHEET 1
• Are the highlighted cells different from the "certain text chosen" but all the same as one another?  NOT SURE I UNDERSTAND THE QUESTION BUT I ASSUME THAT YOU WILL UNDERSTAND ONCE YOU LOOK.

[#4]

@Justin_Blush 

THAT is the most overwhelming Excel challenge I've ever seen.

In no small measure because each cell is itself one or more sentences; they're not simple elements of data, easily sorted or filtered. But your Conditional Formatting rules do manage to do a form of filtering insofar as they selectively highlight some cells from among the many in each column.

It appears that you're really attempting what might be called a massive Mail Merge, beyond any scale I've ever seen, with all kinds of conditional inclusions and exclusions involved in determining which sentences get included in the final result.

For your sake, I'm hoping that some others among the "Expert Contributors"--a category that doesn't include me--will be able to look at this and say, "Oh, that's a snap" and point you to Power Query or one of the other advanced tools. I'm still an apprentice in these parts, having learned my craft in the early years of Lotus 1-2-3 and Excel (mostly, quite seriously, in the last century; I retired in 2002; Excel has greatly extended its toolkit in the nearly two decades since).

That disclaimer aside, were I to have any suggestions, I'd point you in the direction of using

1. the FILTER function--where I wonder if you could apply the same rules (equations, formulas) you have articulated for the Conditional Formatting--to yield (what would still be) a column of the cells meeting your selection criteria.
2. followed by the TRANSPOSE function, which could turn that column of cells into a row.

But, if I can stay with the analogy of tools, I feel like that might be the equivalent of saying, "Here, try this coping saw" when what you need is a CAD directed 3-D printer.

I wish you well, and will be following this thread with interest.

[#5]

@mathetes  Thank you good sir for taking the time.  What if I didn't transpose the columns to rows?  In your opinion does that make the goal any less daunting?  

So in other words is it possible to only populate what is being highlighted from page 2 onto page 3?

[#6]

@Justin_Blush 

You could try the FILTER function, using the criteria you have in your conditional formatting rules....

But again, I'd only offer that as a "could try" ...

If it does work, it needs space to "spill" ....