Inserting Rows across sheets

[komet12]

Good Day all.
I have a sheet example that has an ALL tab were data is actualy changed in the 5 sheets that takes the data and puts it in groups of defeciant test scores. a couple of thing get messed up id you insert a row into the All tab.
1. any filters set in the other sheets go away.
2. the cell referces from the all tab get outta wack and I have to pull them down again.

Now I think I have the person doing the inputting to just add any new people to the sheet at the bottom then resort them, this will work fine but I still have to go into each sheet and re-do each filter that I have set up for each sheet.

So is there anyway to filter out the blanks in my sheets so I get the correct deficency reports per each class. This will make sence I think when you look at the workbook.any advice would be great.

[Marcol]

See if this workbook helps. It should remove the need for filters.

Typically
With Sheet "BPD_DEF"
1/. Insert 2 columns at the start of the sheet, these will be used as helpers and can be hidden.

2/. In A2

Please Login or Register  to view this content.

Drag/Fill Down to match the rows in Sheet "All" and a bit more to allow for as much entries as you reasonably expect to have.

3/. In B2 this array formula

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter not just Enter.
Drag Down as required.

4/. In C2

Please Login or Register  to view this content.

Drag Across to your last required column (Column P?), then Down as required.

Repeat this for each sheet adjusting the column reference in column A to suit
i.e.
=IF(AND(All!A2<>"",All!H2<70),ROW(C2),"")

Hope this helps.

Note
I have removed all names, macros, and tidied the C/F to clarify this sample workbook.

[komet12]

Thank You So MUCH, This solved alot of issues. Awsome

[komet12]

If you look at the BPD_DEF tab Marcus isnt Deficient in anything yet it is showing he is. it should in each tab show the people deficient in that catagory but it is still catching some that are not deficient. Looking at this It happened when I added a new person then resorted the All tab. so is there another way to add a person to this and sorting it in alpha, without is messing up? I tried to insert a line too didnt fare all that well either. Man this code is awsome if I can just keep them from adding new.

[Marcol]

Marcus doesn't show in tab "BPD_DEF" in the sample I posted.

Have you downloaded the sample and compared it with your actual workbook?

[komet12]

Marcus doesn't show in tab "BPD_DEF" in the sample I posted.

Have you downloaded the sample and compared it with your actual workbook?

I think the Issue came when I Inserted a row, but I think I can get around it by putting on the bottom and then sorting them

[Marcol]

Indeed, that's the easiest way, I should have mentioned that!

[komet12]

What would be an easy way to automate the sort so she don't mess this up, such as a "sort button" I tried to record a macro but that didnt work, went back to my excel 2010 bible still reading lol

I have the following macro :

Please Login or Register  to view this content.

In 2010 I created a tab and that worked ok, however the end user is using 2007 so is there a way I can have a code that would create a tab or something in the ribbon that would allow her to execute this??

[Marcol]

Try this workbook, it should sort as you go, no buttons required.

Add a new name at the bottom of the list, fill Column A first then Column B.

You should be able to add/delete rows in Sheet "All" as required. (Don't add/delete columns)

This code goes in the worksheet module for Sheet "All"

If you have other sheets in the workbook, you will have to exclude them from the last loop, or specify the sheets that the code has to work with.

Please Login or Register  to view this content.

Hope this helps.

[komet12]

Well Of course I have a issue, she sent me back the sheet all screwed up, she was entering data in the result sheet bpd, meeting ect.... (cant follow instructions) so can I protect the sheets to accept data only through the macros and formulas and not from direct entry into cells?

[Marcol]

Try this workbook. It's protected without passwords.

If you want to use a password then use the same password for all the sheets (except sheet "All", it can have a different one).

For each sheet apply the formatting and protection as shown then apply your password.

Then in the code for the worksheet module Sheet "All" worksheet_change event.
Change these lines

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

I hope that she understands this, however I'm sure you'll keep her right.

[komet12]

on the komet12_testing sheets yours do not show the formulas, but I cannot get mine even them protected NOT to show the formulas?? all else seems ok though

[Marcol]

Select the whole sheet in question (Not Sheet "All") then

Format > Format Cells ...
Select the Tab "Protection" and check both boxes Locked and Hidden

[komet12]

I have another issue with this sheet Marcol, I have a complete new sheet of People that in in essence going to be tha (all) sheet. I need to compare the 2 sheets and add any new ones to the list. I then need to take the names in tha (all) sheet recompare and those names that are not on the new list need an addendum in the notes line of there info stating "no longer LT but has training"