Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Equal to previous cell and named range
In many of my spreadsheets I compare a value in a cell to the previous
cell value in the same column, e.g. "If(A3=A2,...)". I would like to have a named formula that supplies the A3=A2 part of the formula using named ranges. What I have tried and could not get to work properly is: Define a named range - "Item=Data!$A:$A" Define the previous cell - "ItemUp=INDEX(Item,ROW()-1)" Define equality test "ItemEqUp=(Item=ItemUp)" When I use ItemEqUp in a formula in my spreadsheet (for example: "IF(ItemEqUp,"Eq","NotEq"))" it evaluates to #NUM! during the Item part of the formula. In the formula evaluation box I see (#NUM! =INDEX($A:$A,ROW()-1)). The Index part of the formula evaluates correctly and returns the item number. If I substitute the actual range in place of the named range when defining the named formula, I get the same result. If I use the defined range and formula like this "If(Item=ItemUp,...)" in the spreadsheet cell it does work. Can this type of formula be written as a defined formula? If it can, how would I do it? Or: Is there a better way to do this? Thanks, Knapp |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
get data from a cell in each named range | Excel Worksheet Functions | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Value/IsNumber/Brackets..? cell equal to range w/o Average | Excel Discussion (Misc queries) | |||
named cell range | Excel Worksheet Functions | |||
Getting a named range from a cell value | Excel Worksheet Functions |