How to copy data from a sheet based on a cell value?

Hi Guys,
Excel 2007
I am very much in need of assistance to complete some work I am doing

I am trying to build a template to compile build lists
I am stuck & would appreciate any ideas how to proceed

Ref to attached worksheet as needed
This is the process so far:

1. From a Dropdown List select a Number (I have no problem setting this up)

2. Based on 1st selection, a 2nd second selection is made from a new dropdown box (This ok too)

3. Each possible selection has its own sheet & the sheet is called whatever the number is.

I just can't get the following to work & would appreciate help

4. (a) Clear all rows below row with the selected cell
(b) Based the value returned by the selected cell, find the sheet with the same name (E.g selection = AD0250 sheet name must = AD0250)
(c) Copy all data on the sheet named AD0250 & place it in Column A & 1 row directly below the selected cell

5. Once the 'copy' has been done prevent any changes to cells B1 or B7

Note: Data on the copied sheet can include addition Dropdown Lists or data based on a VLookup using different


Thanks in advance

Regards

Bill
P&T Raw Data1.xlsx

This is my non-VBA approach...

How about this...

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

Ok, you can't have formulas and drop down lists in the same cell, so this final layout DOES give you your Assay drop downs in the same position, but you will have to use the drop downs to make selections.

Also, using drop downs allows you to create scenarios where you had selected assays from train parts and then switched to plane and the old values still showed. I created a couple of named formulas called ErrorCheck and ErrorCheck2 that are then used in conditional formatting to cause the old values to be "hidden" if the current values no longer match the drop down values for that existing selections above.

I've added some flags in C8:C11 (names shown in D8:D11) that show how the conditional formatting formulas in B9:B11 are working.

B7 uses the conditional formatting formula ErrorCheck2 which test the current DV list and sees if the currently displayed VALUE belongs to the current list. If so, TRUE, the value stays visible, else FALSE and the CF hides it until you change it.

Same for B9:B11. Each now has it's own ErrorCheck formuas B, A or E. So, as the values appear and disappear, click on the TRUE/FALSE cell and use the Evaluate Formula tool to watch it unfold until you understand how these formulas are working. Since it's a named Formula, that's how it's being used in B7, B9:B11.

Once you figure how that's working, you can delete those reference cells C8:D11.

I've also changed the way the Item Count works, not it, too, works exclusively off of the part numbers you list on each sheet. No other data comes from those sheets, it's all coming from the VLOOKUP database.

Beyond that, any specific questions?

You would have to take care with any extensively designed workbook, right?

As long as you understand this, I would use a non-VBA approach. It's much safer to "share" workbooks without VBA, some people/companies do not allow macros at all, so they wouldn't be able to see it working at all.

If you do go the VBA route, changes to your workbook would typically require the same kind of care, and possible extensive maintenance, so I don't see it being less work overall. You have to manage the process either way.