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

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

Bgs
13 ●2 ●2 ●6

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

add a comment

0

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

karolus

5584 ●17 ●40

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

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

edit flag offensive delete link

Comments

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 ( 2014-08-19 15:28:12 +0200 )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

karolus ( 2014-08-19 15:48:13 +0200 )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 ( 2014-08-19 16:39:33 +0200 )edit

If it doesn't matter i would not ask!

karolus ( 2014-08-19 17:07:35 +0200 )edit

add a comment

0

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

Bgs
13 ●2 ●2 ●6

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

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

Comments

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

karolus ( 2014-08-19 15:58:02 +0200 )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 ( 2014-08-19 16:44:55 +0200 )edit

add a comment

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

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

Comments

Comments

Question Tools

Stats

Related questions

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

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

Comments

Comments

Question Tools

Stats

Related questions

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

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