Value of merged cell next to a non-merged one

[Bgs1]

I have a table where column B contains individual data and column A has several merged cells that informally group the B cells. Let’s say B contains transaction IDs and A contains merged cells where each merged cell contains a date string for the time ordered transactions.

In another sheet in whatever way I get a reference to a B cell (for example the output of a function is sheet1.B42). I need to get the A value for that B value. For example B42 happens to be next to the A38:A52 merged cell. That is I need the value of A38.

To sum up:
sheet2.A1 → function with a value of “sheet1.B42” (result of a lookup)
sheet1.B42 is next to the merged cell A38:A52
sheet2.B1 should get the value of sheet1.A38

How do I make that sheet2.A1 → sheet1.A38 connection?

[karolus]

Hallo
There is no normal*** way to reference the neighboured merged cell except this Cell starts on same row as the cell where you want to reference from.

***probably with some weird UserDefinedFunctions or Calc-Addinn-Functions.

Under the Condition the “hidden” cells “behind the merged one” are empty, maybe it is possible to create a nested INDEX|OFFSET|WHATEVER-function.

Karolus

[Bgs1]

The hidden cells are indeed empty. The logical algorithm this way is simple: step left → while cell empty, step up → Get cell value.

But I do not see any obvious way either to achieve the same with functions. I guess it can be done by scripting it, but I’ve never done LO scripting.

Theoretically something like this should work, but I wanted to see if there is a more straightforward way:
From the coordinate B42 create an array of A1:A42 → Do some lookup from bottom up for non-empty cell and return value.

[karolus]

Youre writing about Date-Strings in Column A ?
please check if they are string or date-value ?

in case of string in iso-format - yyyy-mm-dd or date-value – are they ascending ordered ?
Karolus

[Bgs1]

It doesn’t really matter what the format or ordering is. I have multiple tables with this structure. I picked the date for easier understanding. In another case for example I have mac addresses grouped by switch port or port-channel. In practical terms those are randomly ordered free form strings.

[karolus]

If it doesn’t matter i would not ask!

[Bgs1]

It actually works with match -1:

=MATCH("", INDIRECT("sheet1.A1:A"&RIGHT(sheet2.a1,LEN(sheet2.a1)-1)), -1)

The above gives the row number. I intentionally left out how to get the value from that as it doesn’t add to the real problem solving.

[karolus]

what is actually the Formula in Sheet2.A1 ?
and please have a look on my other Comment below my Answer.

[Bgs1]

In the above simplified case it’s a static string to look up. In my example B42.

I didn’t want to include the final (whole) formula as it’s long and the real information regarding this post gets lost in the lot.

The real information is:

• Take the row number of the B cell.
• Create an array from that row in column A to the top.
• Do a match -1 with an empty string on it.
• From the match output you get the row number of the merged cells first cell which actually contains the data.

[Bgs1]

In the above simplified case it’s a static string to look up. In my example B42.

I didn’t want to include the final (whole) formula as it’s long and the real information regarding this post gets lost in the lot.

The real information is:

• Take the row number of the B cell.
• Create an array from that row in column A to the top.
• Do a match -1 with an empty string on it.
• From the match output you get the row number of the merged cells first cell which actually contains the data.