Excel — Use dynamic arrays and conditional formatting to create a selection dependent menu

MENUS
I built a short presentation demonstrating the use of dynamic arrays and conditional formatting to create a selection dependent menu.

Though I built this spreadsheet menu using Excel on a desktop machine, the spreadsheet file also works on an iPad ( ! ) and on an iPhone ( ! ! ).

Microsoft 365 is required.

Google Search

dynamic arrays

conditional formatting

TRY THIS

The MP4 video was recorded on an iPad and published using Camtasia 2020.

Overall no formulas
Overall no formulas
Overall with formulas
Overall with formulas
Overall with formulas
Data
Name Manager
Name Manager
Data Validation
Data Validation (notice # suffix indicates dynamic array)
Menu 1 Pull Down Array
Menu Pull Down Array
Menu 1 Pull Down
Menu Pull Down
Pulldown is in Cell H4; calculated values in Cells J4 and J5
Pulldown is in Cell H4; calculated values in Cells J4 and J5

The data range is conditionally formatted to match the selection

Conditional Formatting
Conditional Formatting

Leave a Reply

Your email address will not be published. Required fields are marked *