How to display different content/area based on the value selected.

thecat23

New Member
Joined
Apr 6, 2019
Messages
19
Office Version
  1. 365
Hi Guys

I am trying to write my own spreadsheet for Timber and steel beam design. In order to keep it simple, I would like to integrate both design into one single page layout. Both Timber and Steel will share some common parameters, in which will not change. There will be an block of content contains different formula, drop down list etc. I would like to swap between the two pre-defined block of formatted text, equation, and drop-down-list based on what beam type is chosen, "Timber" or "Steel". This block of content will automatic update/swap to its associated content once the beam type is selected. This content can be prepared and stored on a different sheet, but need to be somehow swap back to the main page once beam type is selected. So this main page has a common area which is never going to change, and an area that will change once different beam type is selected.

Can anyone point me to the right direction? I know this is achievable but I don't know where to start with. The images below are the swap-able content that I have mentioned above, they need to be interchangeable. Thanks!

2.PNG
1.PNG
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Hi TheCat23,

I can't tell how many formulae there are or how many LoVs but one way would be to keep the Timber and Steel as separate sheets, each with their own calculations. Then build a Design sheet which just pulls the results from Timber or Steel, whichever is selected. You can use IF statements or INDIRECT to retrieve the correct value.

For the LoVs make them Excel Tables named with the appropriate suffix, Steel or Timber. Then use INDIRECT so it shows the correct one.
e.g.
Book1
ABCDEFG
1DesignbarkTimberLoV1SteelLoV1
2Timberwoodzinc
3barkcarbon
4twigmetal
Sheet1
Cells with Data Validation
CellAllowCriteria
C1List=INDIRECT($A$2&"LoV1")
 
Upvote 0
Hi TheCat23,

I can't tell how many formulae there are or how many LoVs but one way would be to keep the Timber and Steel as separate sheets, each with their own calculations. Then build a Design sheet which just pulls the results from Timber or Steel, whichever is selected. You can use IF statements or INDIRECT to retrieve the correct value.

For the LoVs make them Excel Tables named with the appropriate suffix, Steel or Timber. Then use INDIRECT so it shows the correct one.
e.g.
Book1
ABCDEFG
1DesignbarkTimberLoV1SteelLoV1
2Timberwoodzinc
3barkcarbon
4twigmetal
Sheet1
Cells with Data Validation
CellAllowCriteria
C1List=INDIRECT($A$2&"LoV1")

Hi Toadstool

Thanks for your comments! I have thought about it, if we use two separate sheets, one each for "Steel" and "Timber", the main design sheet just pull data from it, this is fine. But how do we retrieved, show, hide Dropdownlist that is specifically relevant to the "Steel" or "Timber"? Take the photos above for instance, if "Timber" is selected, the the cell next to it will need to add a dropdownlist for timber grade selection, "LVL" was then selected for timber grade in this case. However, when "Steel" is selected, then this timber grade dropdownlist needs to disappear.
The idea is not to leave the main design sheet. I prefer not needing to go the other sheet to make changes, then this changes are then feed back to the main design sheet. I would like to make the main sheet dynamic, so I can make whatever design changes, Beam Type selection, timber grade, etc on this one page only. I am not sure if the main page only has datas or droplist linked from other sheets, then I would not able to make changes since the contents are from other sheets?
I know this is achievable since someone else has done it, not sure if VBA code is required to make this happen. Thanks

Regards
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top