ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Equal to previous cell and named range (https://www.excelbanter.com/excel-worksheet-functions/134649-equal-previous-cell-named-range.html)

KnappH

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