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 |
All times are GMT +1. The time now is 11:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com