Ask Your Question

value of merged cell next to a non-merged one [closed]

asked 2014-08-19 14:30:59 +0100

Bgs gravatar image

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-02 12:53:25.458842

2 Answers

Sort by » oldest newest most voted

answered 2014-08-19 15:02:12 +0100

karolus gravatar image

updated 2014-08-19 15:04:16 +0100

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.


edit flag offensive delete link more


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.

Bgs gravatar imageBgs ( 2014-08-19 15:28:12 +0100 )edit

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 gravatar imagekarolus ( 2014-08-19 15:48:13 +0100 )edit

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.

Bgs gravatar imageBgs ( 2014-08-19 16:39:33 +0100 )edit

If it doesn't matter i would not ask!

karolus gravatar imagekarolus ( 2014-08-19 17:07:35 +0100 )edit

answered 2014-08-19 15:39:50 +0100

Bgs gravatar image

updated 2014-08-19 15:56:56 +0100

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.

edit flag offensive delete link more


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

karolus gravatar imagekarolus ( 2014-08-19 15:58:02 +0100 )edit

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.
Bgs gravatar imageBgs ( 2014-08-19 16:44:55 +0100 )edit

Question Tools

1 follower


Asked: 2014-08-19 14:30:59 +0100

Seen: 1,481 times

Last updated: Aug 19 '14