Help with Excel Formula/Macro if anyone can

[DarthTom]

I'm not sure this is possible or not and cannot find an answer on Google or in Excel help.

I have a series of excel spreadsheets where I've highlighted a specific row in yellow for a specific reason over the past 12 months. Now of course I need all of those rows to be consolidated into one excel spreadsheet and failed to have the foresight to create a null value to put into the file in advance to identify it.

Question: Is is possible to create a formula/query/macro to extract any row that is highlighted in yellow and place it into a separate excel file in an automated fashion as opposed to me going back 52 weeks - week by week - and manually pulling them out?

Any help would be appreciated.

 

[farmkid]

I'm not enough of an Excel guru to do this, but when I have a task like this, I ask for help on ExcelForum and usually get some good help. If no one here can help you, I'm sure someone there can.

 

[DarthTom]

^^^ Thank you. Great suggestion. No one has jumped in thus far here so maybe they can help. I did find by tinkering with it and looking at the Data Sort function you can sort by formatting - including color if anyone else was curious. Go to Data Sort and you can choose a sort by color. i still have to go through 52 files and then copy paste the highlighted cells but at least that's better and having to go through them row by row.

 

[rhubarbodendron]

Hold on, if you can sort by formatting options (I didn't know you can do that! Thanks for the info =)) wouldn't the yellow lines automatically come on top of your respective sheet?
In that case you can work with a formula and copy the first line of every sheet into the new sheet.
someting like: cell A1 = (sheet1!$A$1), cell B1 = (sheet2!$A$1) etc.
You'd just have to enter the formula once and then simply drag it down 51 lines. Don't forget the $ - it fixates the line and column. Else excel will automatically change it to A2, A3 etc when you drag the formula down.

In this example I used only a single cell from each sheet. Whole lines work analogue.


(Sorry about the delayed reply - haven't been here in a while)