Thanks! Data validation is a feature in MS Excel used to control what a user can enter in a cell of an excel sheet. Revisit Data Validation: go to the Data tab and select Data Validation. Select the data validation cells in the first column On the Ribbon's Data tab, click Data Validation For Allow, select List In the Formula box, type: =IF(C2='',Produce, INDIRECT('FakeRange')) Click OK Now, the drop down in the first column won't work if the Item has been selected in that row. Most users know about data validation as a way to create dropdowns. Step 3: Create a data validation list for cell B2 with the formula =INDIRECT(A2). If you don't need to use it, swith to regular style by unchecking the R1C1 Refence Style box at TOOLS > OPTIONS > GENERAL tab. Join Dennis Taylor for an in-depth discussion in this video, INDIRECT with Data Validation for two-tiered pick list scenarios, part of Excel for Mac 2016: Advanced Formulas and Functions. Using Indirect function in Data Validation through VBA. Replace Indirect In Data Validation Rule Stack Overflow.

=INDEX(INDIRECT(“Table1”), MATCH(G14,Table1,0)) So by combining these formulas we can choose two criteria (Sales Rep & Metric name) to return the respective value. Mine is A4 therefore my formula is =INDIRECT (A4) Press OK. Ref_text is the text string to evaluate as a reference. This is a bit of a guess, becuase I never use RC referring. Thanks! Dùng hàm INDIRECT kết hợp Data Validation giải bài toán Excel phụ thuộc Hôm nay Tinhocaz7. In data validation, to create the dependent list, we have used INDIRECT function to return the value based on the primary data validation list. Selection3 data validation formula is '=INDIRECT(VLOOKUP(A2,Compiler_Table,2,0)):INDIRECT(VLOOKUP(A2,Compiler_Table,3,0))' Does anyone know if there is an acceptable way to build these data validations in Excel 2010 using Indirect, that would also work in Excel 2007? The INDIRECT function returns a valid cell reference from a given text string. Excel Data Validation Based On Another Cell Exceldemy. From the Allow drop-down list, select List. The first being the INDIRECT function and the second is using the named ranges. Like, restrict entries in a sheet, such as a date range or whole numbers only. For example, take a look at the INDIRECT function below. In other words, =INDIRECT ('D1') reduces to =D1. The data validation can be applied to a single cell or a range of cells. Excel indirect data validation showing choices of bolts from Inventory table.

Excel 2016 for mac cell name