Combining multiple templates into one

[John F]

A member of my team has created numerous templates in Excel 2007 for various customers. Each template has some columns that are identical for each (Cust Name, for example), and some columns are unique to each customer. I want to combine all of these individual templates into one Excel workbook and be able to click (select from a dropdown list) the customer name, and have only those columns that pertain to that customer appear. Select another customer name, different columns appear, and so on. Is this possible to do in Excel 2007? [This is my first post, so please forgive any offenses if my question is too long.]

[Palmetto]

This is likely very possible using Dependent Data Validation Lists combined with look up functions. A more specific answer can be given once you've uploaded a sample workbook.

In the meantime take a look at these links regarding Data Validation and Dependent Lists

Excel Data Validation List Based on the Value of Another
Contextures

Sample workbooks are available at each site.

[John F]

Thank you. I will read the Dependent Validation List text, try the examples, and see if it fits what I am striving to do. I'll let you know. I appreciate the guidance.

[John F]

I've attached a test worksheet as a model of what I'm trying to accomplish.

Column A allows you to select from four different 2010 Toyota Prius models.
* If you select Prius II, only columns A, B, and E should appear. Columns C, D, and F should be hidden since they don't apply to this model.
* If you select Prius III, only columns A, B, C, and E should appear. Columns D and F should be hidden.
* If you select Prius IV, only columns A, B, C, and D should appear. Columns E and F should be hidden.
*If you select Prius V, only columns A, B, D, and F should appear. Columns C and E should be hidden.

It's not a great example, I know, but my intent hopefully will become clear. I need to hide certain columns, or display certain columns, based on what is selected in column A. Some of the suggested demos tell me how to make certain cells appear or not based on a selection, but not columns. Can you dynamically hide or unhide columns based on a selected answer from a list?

I hope this explanation helps clarify my goal? Thanks.

[John F]

OK, I'm not getting any hits on this, so maybe there is no solution in Excel to my initial question. Let me try another angle.
By using Conditional Formatting, can I select an entire column, or multiple cells within a column, to be the formula condition upon which an action is taken? For example, if the value in any cell within the range of B2 to B10 = XYZ, turn cell C1 red.
I tried using this formula in Excel:
=$B$1:$B$10="XYZ" the turn cell $C$1 red.
but this only seems to work when cell B2 is set to XYZ. If any cell within the range B3 to B10 is set to XYZ, it doesn't work.
What is the workaround? How should the formula be worded so that value XYZ in any cell within the range of B2 to B10 (meaning it could be one cell within that range, a few cells within that range, or all cells within that range) would turn cell C1 red?

[Palmetto]

Column A allows you to select from four different 2010 Toyota Prius models.
* If you select Prius II, only columns A, B, and E should appear. Columns C, D, and F should be hidden since they don't apply to this model.
* If you select Prius III, only columns A, B, C, and E should appear. Columns D and F should be hidden.
* If you select Prius IV, only columns A, B, C, and D should appear. Columns E and F should be hidden.
*If you select Prius V, only columns A, B, D, and F should appear. Columns C and E should be hidden.

See attached. Columns are hidden based on selection of auto model in cell A2.

Please Login or Register  to view this content.

[John F]

Not being a programmer, I really appreciate your work to make this happen for me. I'll give it a try and let you know of my success (really YOUR success!).

John

[John F]

Hey, Palmetto, where do I cut and paste this code in my sample spreadsheet that I attached?

[Palmetto]

where do I cut and paste this code in my sample spreadsheet that I attached?

I have already put the code in the correct place in the attachment that I uploaded in my last post.

The code must go into the worksheet module. Right-click the worksheet tab, choose View Code, the copy and paste the entire code into the code pane of the VB Editor.

[John F]

I now understand. Thank you so much, Palmetto. Your understanding of my problem and your successful solution is wonderful. I sincerely appreciate your efforts on my behalf.