Hello, darling subscribers. This is Oppadu Excel. The name of the channel is two brothers. As the day goes, In this lesson, you will only see a list box that automatically changes items according to various conditions. After you list the main items for creating a household dashboard, try to create the original data in the household dashboard. What are the multiple condition list boxes? I'll see the screen together. There are three accounts. Items received from three suppliers are different. The item will be replaced with the item that corresponds to your brother's two vegetables. If you choose two fruits, the fruit names are listed. If you change the list of items that were already selected, it will be changed to the item of the corresponding customer. There are two main ways. While it is easy to write, it is somewhat inconvenient to manage many items. Second, using dynamic range is a bit inconvenient to write, but it is a convenient way to manage many items. In this lesson, we will create a multi-condition list box using the first method, the table and name manager. Are you ready? Let's go together! In the previous lesson, I learned about the ability to move sheets every time you click a button. If you change the cell width, the width of the shape will change. To avoid this problem, please select three shapes. Click Object formatting - 'Gridline icon'. Check the 'Unchanged' option in 'Properties'. Check "Unchanged" in the figure settings for the remaining two sheets. I will enter the items of the configuration for the household dashboard. Please put 'deposit account'. I'll expand the cell width. There are three divisors in the household part. There are 'earnings', 'spending', and 'move by account'. Please indicate the type of income you are importing. Company salary, and other salary. "Spend" also lists according to your spending type. In 'Move by Account', please enter 'Account Transfer' and 'Accounts Payable' items. I will put a small account by account subject. I will give you the company salary. Company salaries include salaries, bonuses, and so on. Other payouts include "YouTube Revenue" and "Ad Revenue." Please include 'Public transportation', 'Taxi', 'Liquor fee', 'Other transportation expenses' and so on. Please provide a small account for each account. Please change all of your ranges to tables. After selecting a range, press Ctrl + T keyboard and 'Create Table' window will appear. Please check the Include Header. Please make the same table for all other ranges. In my case, it took time to create a range first and then create a table for each range. You can make it faster by first creating a table, then copying and pasting the table with copy-paste, and then putting a small account for each account in it. To make it easier to see, I'll sort the colors of the table. I will change the name of the table. After clicking on the table, go to 'Design' and you will see the 'Table Name' item. Please change them one by one corresponding to the table heading. Alternatively, you can select the entire range and then click the 'Formula' - 'Create in Selection' button. There is the ability to create custom scopes in the Name Manager based on the values in the first row. However, this feature does not apply to dynamic ranges where the range automatically changes along the table dynamically. So, to be able to change each range dynamically, it is somewhat inconvenient at first, but please rename the table in the table tool for each table. It creates the original data table in the household entry sheet. Date, division, account subject, small account, deposit account, increase / decrease, amount, remarks. Please adjust the width of each column. The width should be widened to match the width of the previously created image. Change the format of the cell to enter the data nicely. Please make the text bold, align the center. The text color changes to gray. The border should be '[T] op (top)', '[B] ottom (bottom)'. Please select the other range and fill it in gray. Please fill in the middle six rows of gray. Select the range in which you want to insert the data and then put '[H] orizontal (horizontal line)' and '[B] ottom (bottom)' as a dotted line in the cell format. Finally, select the entire range and put a border around '[R] ight (right)', '[L] eft (left)', and '[V] ertical'. You can see what is neatly organized. If you implement Excel automation using only built-in functions without VBA, file size will increase and operation speed will be slower. It is important to limit the input range of the data. In this lesson, we limit the range to 10000 lines. Please go to 'A10000' cell after naming box. Please select the whole range below. Use Shift + Space to select the entire row. Ctrl + 9 keys to hide the entire row. Each item you create in preferences is put into a list box of data validity. Under Data - Data Validation, select List. Please put it in "income, expenditure, movement". A list box has been created to select and enter the desired values via the mouse. Create a multi-condition list box so that you can separate accounts by income, expense, and movement. If you select "Income" in the category, put a list box so that the account items corresponding to "income" are displayed. Under Data - "Data Validation", select "List" Use the INDIRECT function to retrieve the value of the selected cell. The '$' mark will be automatically entered and will be an absolute reference. Because the rows vary across the range, please remove the $ before the number so that each value can be applied when the rows are different. Please put it in the same account as the account. Similarly, please remove the dollar sign ($) before the number. It is divided into small accounts according to the division and account subjects. Please also put your deposit and withdrawal account in the list of data validation. Use the INDIRECT function to retrieve the range corresponding to the 'Bank Account'. Apply data validation to the entire scope. I will increase and decrease. I'll put an icon. Please refer to the previous lesson for more information on 'icon set'. In case of increase / decrease, '1' is displayed for income, '-1' is displayed for expenditure, '0' is displayed for movement, and blank space is displayed if there is no value. Paste only "Formula" from Paste by selecting Ctrl + Alt + V. Next, with the entire range selected, select "Other rules" under "Conditional Formatting" - "Icon Set." Change the type to all numbers and then enter the values. You can see the icon changes depending on the classification. I do not like the numbers next to me, so I'll hide the numbers. Please check 'Show only icons' in 'Rules Management' - 'Edit Rule'. You can see that only the icon is displayed. If you want to insert new material, you need to move down the sheet each time and add a value. I will use the button that automatically finds and moves the last cell learned in the previous lecture, so that I can automatically move to the last cell of household input. Please put 'last cell' in the name box. Right-click the Household Input Shape and click Edit Link. Select "Last cell" from "Defined name" of the current document. Go to 'Name Manager' and change the function in the last cell. Search for 'last cell' in the top right corner. Under the related function, copy and paste the function 'reference scope with numbers'. Put the 'INDEX' function in front, and put the reference range from cell B7 to cell B10000. Also change the reference scope of the MATCH function. Close the parentheses by putting a +1 after it. Every time you click the input button, it moves to the last cell of the sheet. Please copy the geometry and paste it on another sheet. Each time you click on each sheet, the sheet is moved. Even if the sheet is located elsewhere, you can click on the Household Input button to move to the last cell in the sheet and make it easier for users to edit. In this lesson, you learned how to enter the original data from the Household Dashboard by using the multiple condition list box, a set of icons handled in the previous lesson, and a button to move to the last cell. Please leave a comment below regarding your questions during the lecture. If this lecture is helpful, I would like to ask you to subscribe.
