File:Create list

[Rusty Shakleford]

I have a master list of equipment that can be installed on a piece of equipment on sheet1 of my worksheet:

Equipment list Machine1 Machine2 Machine3

Piece of Equipment1 Location Weight X X
Piece of Equipment2 Location Weight X
Piece of Equipment3 Location Weight X
Piece of Equipment4 Location Weight X

I have made drop-down boxes to indicate what equipment is installed on each machine. I have made sheets for each machine and would like to make a list of the installed equipment with the the weight and location (different cells) on the sheet for the individual machines to generate off the master by dropping down to the X. I would also like the equipment to be removed by removing the X. So far I have been able to have the cells copy over to the other sheet, but they always go to the same cells. I would like it to make a nice condensed list, with no empty rows in between. Thanks for your help.

[martindwilson]

post a workbook with sample data and expected outcome

[Rusty Shakleford]

Here is the spreadsheet. On the Master sheet I want to be able to mark what equipment is installed by selecting "X" from the drop down. I want the selected equipment information (description, weight, arm and moment) to show up on that aircraft's sheet at the bottom as a list under "Mission Removable Equipment". I also want it to disappear when the drop down box is selected to be blank.

[martindwilson]

well with functions you have alot of work to do.
insert a new column for every aircraft to dount the "x "s ive done col H on a119master
you can then index match using the formula given on sheet n108ag
note rather than insert a column you could use another sheet to do the counting and use that.
see missionmaster2

[Rusty Shakleford]

That looks like what I want. Now I just need to make it for all of the aircraft!
Thank you.

[JBeaucaire]

The programming option you can try. The macro will go by each sheet name and look for a matching column of data on the A119 sheet. If one is found, it will put the items marked into the Removable Equipment section of that sheet. The section can be in a different spot on each sheet, it will still work.

You can "mark" items with anything, not just "X", any entry at all will cause it to transfer to that sheet.

Any sheets without a matching column of data will be noted for you by the macro so you can check it later. Just press the button anytime you want to update the other sheets.

Please Login or Register  to view this content.

[Rusty Shakleford]

The macro works well too. I failed to mention that this will be a public form available to many people. Because of this I need to lock it up. With protection enabled can I still update the cells with the macro?

[JBeaucaire]

Yes, you just need to enable macros on protected sheets. There a many ways to do that.

Are you going to protect each sheet with the same settings and the same password? If so, turn on the macro recorder and let it record you protecting one sheet with all the settings the way you want. Post the resulting code here and I'll show you how to put that into a workbook_open macro that protect ALL the sheets and gives access to the sheets for VBA changes.

You will want all sheets protecting EXCEPT A119, yes? Or that one, too? Be sure to think it through and give good information so we can get it right.

[Rusty Shakleford]

Basically the only user input I want is to choose "X" or blank on the drop down boxes in the master sheet. From there I only want the user to be able to view and print the individual aircraft sheets. I would like to password protect all of the sheets and workbook. Thank you for your help, and sorry for the broken information. I didn't discover the problem until I tried protecting the sheets.

[JBeaucaire]

Once you start this stuff, it's always good to have an easy way to jump around in your workbook, so I converted the "column headers" to hyperlinks for you. There is another set of text labels above in row1, that is used to create a single formula in G2 that can be copied across and still work.

So, make your edits, click the UPDATE button, then use the hyperlinks to jump around quickly. I added a hyperlink on each aircraft sheet to make jumping back quick, too.

I used a temporary password of "password123" on all the sheets. I also created an "UnprotectALL" macro you can run manually from inside the VBEditor (ThisWorkbook module) to quickly unlock all sheets. There's a "ProtectAll" macro, too, and opening the workbook will run that macro for you every time.

Once you get it all finished, you might want to password protect the VBAProject as well so no one can see the code which includes the password.

[Rusty Shakleford]

That works great! Thank you! As you can tell, I'm not to savy in Excel. How do I manually run the protectall and unprotectall macros?

[JBeaucaire]

They are in the ThisWorkbook module. Go into that module, click inside the macro code you want to run and press F5, it runs instantly.

[Rusty Shakleford]

Thank you, yet again.

[Rusty Shakleford]

So, I found out today that there is a change in the requirement for this spreadsheet. Instead of just the Mission Removable Equipment being editable, the whole list needs to be editable. I have cleared it all out and started on this, but I have ran in to some trouble. I need to move the category titles over along with the equipment. Thanks in advance for your help.

[Rusty Shakleford]

Here is an unlocked spreadsheet

[JBeaucaire]

An autofilter requires the data set be congruent. No blanks IN the middle of the data. Feel free to add/delete rows as you wish, but when you're done, no blanks, ok?

Also, make sure the gray rows have an "x" in all the cells all the way across, on every grey row. You want those rows to always be visible when the filter kicks in.

I tweaked the macro to remove the MRE search, so it now it copies everything and replaces everything each time it is run.

Please Login or Register  to view this content.

[Rusty Shakleford]

Jerry, you rule!

[JBeaucaire]

Thanks for that.

It's actually much easier to grab ALL the data and replace ALL the data on each sheet than it was to just do the one section, so even though it looks like the macro is working harder, it's actually moving more data by working less. Hehe...funny stuff.

========
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.