Conditional Formatting & Overview Sheet

Hi all, I have a bit of a problem with a Spreadsheet I am working on, and was hoping someone versed in excel/VBA could assist.

What I currently have, is a spreadsheet which has several worksheets, all containing identical columns with data for each month financial year to date, and then a totals column which has conditional formatting applied (see below for rough diagram)

The total column has conditional formatting set up as follows
0 – 5 = Format green
5 – 10 = Format yellow
10 + = Format Red

Ideally I would like to program a macro that on opening the spreadsheet (or possibly adding a button on an overview sheet) will run through all sheets and create a dynamic overview page.

eg. Top columns will show all names and data for people in 10 + and then after doing this, add the 5+ people (preferably keeping the conditional formatting). (There would be no need to copy any of the less that 5 people over)

I have found several examples that are slightly similiar, but not one which does exactly what I need, which is making me second guess if it is indeed possible.

I have attached a sample with dummy data for you to see the current setup.

Any help would be appreciated

Please Login or Register  to view this content.

Super close to what I need (and with a few tweaks I am almost there).

What I would like to do is be able to sort but start the output on row 23 (or better yet, the first free row),

Not sure if this is possible, and all the tweaks I have tried haven't allowed it to happen.

You mean you want to start the output on the Overview sheet on row 23? Is there something already in rows 1-22?

Originally Posted by Bob Phillips

You mean you want to start the output on the Overview sheet on row 23? Is there something already in rows 1-22?

Ideally there would be...

I tossed around the idea of outputting to a data sheet and copying and pasting, but it seemed like a lot of redundant code...

I would rather start on Row 23 if I could, or better yet, check for a value at just before (say the heading name) and start the row after that)

In your sample, you didn't have a heading row on the Overview sheet. Will there always be a header somewhere?

Ok, so the sample is very basic compared to what I now have.

The actual workbook has 13 sheets and overview as well as total rows. Initially your formula picked up the total rows, but this was fixed with And statements that excluded them.

The overview sheet pulls the data from the total rows on all sheets and pastes it, and then manually under this I have copied the heading on rows 20 and 21 (same format as headings on other sheets.

Ideally the code would look for "name" heading on overview sheet, start pasting the row after, and still be able to sort on these entries only.

With that said, the more I look into it, the more it doesn't seem possible.

At this point data starts output on row 22, but the sort function is not possible

It helps if you provide an actual example, not some workbook that then needs tweaking o the real situation.

Apart from not starting in row 23, didn't what I gave work for you.

Originally Posted by Bob Phillips

It helps if you provide an actual example, not some workbook that then needs tweaking o the real situation.

Apart from not starting in row 23, didn't what I gave work for you.

Absolutely Bob, and I'm totally grateful for the assistance you've provided all along the way.

I can't provide the actual spreadsheet as it obviously contains sensitive data, so I whipped up a quick Sample to give an idea of what I needed (which you were able to provide), and then tweaked the last little bit to remove the total row.

Essentially all I need to know is if it's possible to sort from Row 23 or any such row, if not what I have prints it in order of spreadsheet name which still will work for the purposes needed.

If it would help, I could whip up a more detailled sample tonight, but if sorting from row 23 onwards is not feasible/possible, there wouldn't be a point and I can mark this as Solved.

So noone can advise if this is possible?