I need the dynamic formula that I must put in the data validation. It should be dynamic as the number of commodities and stores can increase. It should exclude the store with zero stock. For example, View > Status Bar will toggle (show or hide) the Status Bar. Figure 1: Parts of the Calc window Note If any part of the Calc window in Figure 1 is not shown, you can display it using the View menu. So the INDIRECT refers to R1C1 cell definition instead of defaulted A1 cell definition. The drop-down list must meet the following conditions: It should provides the store number. Parts of the main Calc window When Calc is started, the main window looks similar to Figure 1. Please note that I put FALSE as the argument. The INDIRECT function references the selected column. In our example, United States is in the first column from cell G4, Canada is in the second column, and China is the third column. The MATCH function determines how many columns from cell G4. Start to use the command ‘Selection List’. ‘Close’ / ‘OK’ Make sure that the toolbar you chose under 1. Select (right) the ‘Command’ ‘Selection List’. Depending on the option chosen, the corresponding list will show up in the drop-down list. Click ‘Add’ to open a second dialogue titled ‘Add Commands’. Select List from the Allow drop-down box, and then type the following formula: IF(C2'Show Fruit', ShowMeat, ShowFruit) The formula uses two range names ShowFruit and ShowMeat. It starts from cell G4 because the drop-down data list starts from such cell in our example. In the Ribbon, select Data > Data Tools > Data Validation. The OFFSET function is used to referencing the position of the data. The COUNTA function is used to count the length of the state or province list. So the length of the list must also be taken into consideration. But there are only 12 provinces in Canada. For example, the US has 50 states (plus Washington DC, Puerto Rico, and Virgin Islands). What makes this formula so complicated is that the number of states or provinces different from country to country.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |