How to get text in multi-row merged cell always visible (when at least one of its rows is visible)?

2

I have merged a lot of rows in Column A (e.g. 100 rows) and added a relatively short text in that cell. Then, I have data in each row in Column B. Is there a way for Excel to always show my Column A text when I am scrolling through the 100 rows? Like a dynamic align option?

MyExample

I added a picture showing an example: I would like to have "Test" always showing in Column A whenever I am displaying at least one row from 1 to 50. Then it would be "Test2" in column A from row x to y, and etc.

Vince

Posted 2019-07-09T13:28:17.803

Reputation: 1 043

Is the requirement limited to one merged range, or is that 100 rows followed by another group of merged rows and at that point, you need to start seeing the value associated with that group? – fixer1234 – 2019-07-09T23:55:01.617

The 2nd part of your comment is what I am trying to get. – Vince – 2019-07-10T14:10:21.477

There are some VBA tools you can apply; not a direct solution, but check out Detect merged cells in VBA Excel with MergeArea. Also not a direct solution, but this thread could be useful in getting there: Lookups targeting merged cells - only returning value for first row.

– fixer1234 – 2019-07-10T17:45:07.213

If the merged cells follow a pattern, like merging every group of 100 rows, you could freeze a row containing a display cell and calculate what to display there from the pattern, rather than detecting what's merged. – fixer1234 – 2019-07-10T19:16:57.523

Unfortunately, it is not always the same number of merged rows – Vince – 2019-07-10T20:01:45.490

Answers

0

If it is the first row or column, you can set that row or column (or both) to be headers, which means that regardless of you scrolling that row and/or column will always be visible.

In order to activate this, select CELL B2 (anything above and to the left of the selected cell will become frozen panes).

Now select the View tab, and choose Freeze Panes.

LPChip

Posted 2019-07-09T13:28:17.803

Reputation: 42 190

No, it is not for the 1st rows/headers. It is not at all time for the same cells. When I scroll down, I will have another merged cell over another big number of rows and I want that text value to appear on the display, until I keep going down and get to the next merged cell... – Vince – 2019-07-10T14:13:06.930

Asked: 2019-07-09T13:28:17.803

Viewed: 337 times

Active: 2019-07-10T14:20:08.910