Excel: Using Cell References and Defined Names in Criteria

ID: Q61090

SUMMARY

In Microsoft Excel, criteria can be set by typing the exact value that is desired in the criteria cells or by using cell references and/or defined names.

To use the value of cell D1 as the criteria, type the following formula in the criteria cell:


   =$D$1

To use the value of a defined name, such as "CritVar", type the following formula in the criteria cell:


   =CritVar

To use the operators, such as less than (<) and greater than (>), the operator must be concatenated with the formula. For example, to specify a match of greater than the value in cell D1, type the following formula in the criteria cell:


   =">"&$D$1

To specify a match of greater than or equal to a cell defined as "GVAR", type the following formula in the criteria cell:


   =">="&GVAR

MORE INFORMATION

Quotation marks must be used, otherwise Excel interprets the information as "greater than "$D$1"" where "$D$1" is a text string. The same applies for a defined name.

Additional query words: noupd

Keywords :
Version :
Platform :
Issue type :