Hiding and Displaying Data from other worksheets on a common sheet

[tombd]

I have 4 different software products that have some common and some unique features. I have about 10 rows and about 20 column headings that display these features with an "x" in the row/column cell if a product has that feature.


I want to be able to click a button label at the top of the common sheet with the headings and have the corresponding product sheet displayed underneath the columns/row headings. I plan to have a different sheet for each product with "x" in the row/column intersection for the product if it has that feature.


Any suggestions on how to accomplish this would be appreciated. I am dangerous with VBA but by no means an expert.

 

[Luke M]

I'm afraid I don't understand the layout of your workbook. It sounded at first like you were just talking about 1 sheet which had a product/feature matrix.


If you're talking about having a sheet for each product listed, and simply want to go to that sheet when you click on something, you can simply use hyperlinks.

 

[tombd]

Luke, thanks for your reply.


Let me try to explain. On the first sheet I have 14 rows that have feature categories and 20 columns that contain more specific feature detail.


Let me use a simple example of a car manufacturer. The rows would be items such as engine, tires, transmission, etc. The columns would have more specific features such as: 450 V8 engine, 230 V6 engine, Michelin tires, Goodyear tiers, Firestone tires, automatic transmission, 4 speed manual, 5 speed manual, etc.


Using those row and column headings, I will construct a separate sheet for each of 4 different model cars with "x" in the cells that match the features for that model. Every car will offer different features so each car's matrix will be different. But the row and column heading will be common for all car models.


I want to be able to put 4 button labels across the top of the first sheet that contains the row and column labels such that when I click the button for a particular model, all the "x" for that model display appropriately. By clicking the different model buttons, I am able to quickly see on one sheet which features each model offers.


Does that help explain what I am trying to do?

 

[Luke M]

Actually, what you're describing might be best handled using scenarios. Under Data, you should find the scenario item. This feature lets you save a particular view/data setup and give it a name. You could do this for each car and have 4 scenarios. Then, to use your buttons, record 4 brief macros of you selecting the various scenarios. Then assign the 4 macros to 4 buttons (Forms toolbar - command button).


Would that work?

 

[Luke M]

Hmm. Lots of ways come to think of it...

You could use formulas with INDIRECT to change which sheet to look at.

You could have the data be copied from the sheet to look at

You could use a picture link to various sheet, using dynamic drop down. =)

 

[tombd]

Thanks Luke.


I think I will try your first suggestion.


I looked at the INDIRECT function but can't see how I can copy a range of cells since its format is: INDIRECT(ref_text,a1) and seems to point at a single cell. Unless you are suggesting the use of INDIRECT to point to the sheet I want and then use another function to do the copying and pasting. Is that what you are suggesting?


When you say, "picture link", are you referring to the Excel Camera tool?

 

[Luke M]

With the INDIRECT, I was thinking of having the button change a cell to a sheet name (say, "Ford"). Then your formula would be something like:

=INDIRECT("'"&$A$1&"'!R"&ROW(B2)&"C"&COLUMN(B2),FALSE)


In this formula, A1 is the cell containing name of sheet, and B2 is the cell the formula is in. If you copy this formula down/across, it will automatically change which cell it's referencing.


Yes, I was referring to camera tool.

 

[tombd]

Thanks! I will let you know how it goes. Tom D