Changing content in a merged cell using a dynamic range of cells with an "IF" condition

[Prectatorium]

Hello peps.

I’ve been searching google and this forum the whole morning, part being because I am unable to put in words what I intend to do. So an image might help.

So lets say I would like to change the content in a cell (simple/single or merged) from column1 by verifying the content of the cells from the respective rows in column5, but the merged cells from column1 use a different number of rows.

Something like

IF(within some dynamic range of rows corresponding to a range of merged cells has this string; put this string in the merged cell; else put some other string in the merged cell)

If there is a post with a resolution to this problem please point it out for me. And please point out any issue with the wording of my issue.

Many thanks in advance.

[anon73440385]

To me the picture does not make clear how SomeText in 10 rows of Column5 should determine a single value in Column1. The condition of let’s say OK or Not OK is quite unclear. Should they all be the same → OK, or OK if a single special predefined SomeText appearing in one of the 10 rows or OK if any SomeText at all in any of the 10 rows of Column5.

[mikekaganski]

If I understand correctly, your formula in merged “Not OK” cell (too bad you didn’t include headers of rows and columns into the screenshot, to allow addressing cells by names, like A3!) should automatically determine that it spans over 12 rows, and automatically check data in corresponding 12 cells in “SomeText” column (Column5)? So that when you reformat the cell and join 9 or 21 rows, it would not check 9 or 21 cells? I suppose it’s impossible with formulas.

[Prectatorium]

Thanks for the quick response.

Ok, so let say I’m searching for a specific string, in practical terms a firmware version of a device in column 5, and if it exists to change the text of the merged cell in column 1.

But don’t get me wrong, I can take care of the condition, my issue is how to dynamically reference a variable number of rows to the corresponding number of rows that are merged in column 1. So I could place the formula in the top cell in column 1, and drag it all the way to bottom, and no matter how many merged cells make a cell in column 1, only the corresponding number of cells in column 5 will be evaluated.

I mentioned one of my issues would be how to go about the adequate wording.

[anon73440385]

Thus in fact the question is - as @mikekaganski correctly interpreted - to get the number of rows the merged cells span by formula.

[Prectatorium]

That’s it. Any suggestions?
xD

[erAck]

Something like this

=IF(COUNTIF(E2:E7;"Some text")=ROWS(E2:E7);"OK";"Not OK")

[Prectatorium]

Something like this, but number of rows to evaluate needs to automatically adjust to the number of rows that makes up merged cell in “column 1”

[schiavinatto]

In the answer I gave above, I misunderstood the question.
Before mechanizing column 1, replicate the information on all cells and then merge Keeping the cell contents hidden. Thus the comparison is made on the same line.

[schiavinatto]

I used something similar, I did the following:

In column 1: in the cell below I compare with the previous one, if = “”, otherwise = colunax

E Formatted conditional: if cell = “” lateral wedding, cell <> “” lateral and top edges.

Simulates merging, just does not center vertically.

test file

Complement

In the answer I gave above, I misunderstood the question.
Before mechanizing column 1, replicate the information on all cells and then merge Keeping the cell contents hidden. Thus the comparison is made on the same line.

---

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball to the left of the answer, to finish the question.

[Prectatorium]

I did not even understand your answer, until I looked at the test file.
In your test file there’s a unique formula for each row (single or merged). I intend one formula, drag it though all rows, and let the evaluation be in accordance to the number of rows that make up the merged rows.
Thanks for answer thought… I feel I/we as a community are getting closer to the answer