find value in cells

Netanel yosef

hi,

I have a list of drawings for production and there may be a certain drawing that has been updated. I want to mark the old revision so I know there is a new drawing.

Current situation:

Required

there is a formula to do this?

Netanel yosef

Anyone ?

Paul Newcome

How is the data in the REV column being entered?

Netanel yosef

i'm enter the REV.

is column type "text/number"

Paul Newcome

Ok. You are going to want to add in a "helper" column that can later be hidden to keep the sheet looking clean. I will use a checkbox type for this example...

First we need to separate the number out from the data in the REV column.

=SUBSTITUTE(REV@row, "REV ", "")

Then we want to convert it to a number.

=VALUE(SUBSTITUTE(REV@row, "REV ", ""))

Next we count how many rows have a number in the REV column higher than the one the formula is sitting on.

=COUNTIFS(REV:REV, VALUE(SUBSTITUTE(@cell, "REV ", "")) > VALUE(SUBSTITUTE(REV@row, "REV ", "")))

Next we add in the condition of being the same Line Number.

=COUNTIFS(REV:REV, VALUE(SUBSTITUTE(@cell, "REV ", "")) > VALUE(SUBSTITUTE(REV@row, "REV ", "")), [Line Number]:[Line Number], [Line Number]@row)

So this will count how many numbers in the REV column are higher than the one on the same row as the formula that has a matching line number. But we need to narrow our ranges down to look at only rows that are within the same set of children rows. We can reference the [Line Number] column again for this and just use the criteria of rows that have the same data in the parent row of that column.

To do this, we will need to add in another "helper" column of the text/number type (I will call it "Helper" for this example). In this column, we would use a basic formula to pull the data that is in the parent row in the [Line Number] column for each row so that we can use it in our above calculations.

=PARENT([Line Number]@row

Now we can use the Helper column in our COUNTIFS to narrow down our ranges.

=COUNTIFS(REV:REV, VALUE(SUBSTITUTE(@cell, "REV ", "")) > VALUE(SUBSTITUTE(REV@row, "REV ", "")), [Line Number]:[Line Number], [Line Number]@row, Helper:Helper, Helper@row)

Now that we have our count, we can drop this into an IF statement to say that if that count is greater than zero (meaning at least one row with the same parent and the same line number data and a higher revision number exists) to check the box.

=IF(COUNTIFS(REV:REV, VALUE(SUBSTITUTE(@cell, "REV ", "")) > VALUE(SUBSTITUTE(REV@row, "REV ", "")), [Line Number]:[Line Number], [Line Number]@row, Helper:Helper, Helper@row) > 0, 1)

Now you would set up conditional formatting based off of this checkbox column being checked to apply the format of your choosing to older revisions.

Netanel yosef

hi,

thank you for answering.

i try it and i think i got lost... can i share you the sheet and you do the formula and i will learn it later ?

Paul Newcome

Those are the formulas.

You would add in a text/number column with this in every row:

=PARENT([Line Number]@row

Then you would add in a checkbox column with this in every row:

=IF(COUNTIFS(REV:REV, VALUE(SUBSTITUTE(@cell, "REV ", "")) > VALUE(SUBSTITUTE(REV@row, "REV ", "")), [Line Number]:[Line Number], [Line Number]@row, Helper:Helper, Helper@row) > 0, 1)

Then you would set your conditional formatting based on the boxes being check referring to older revisions.