How to find value of named range? 1

[may1hem]

I'm using Excel 2003 and I have a worksheet as follows:

Mon/ / /Thu/ /
Mon/Tue/Wed/Thu/ /
Mon/Tue/Wed/Thu/ /
Mon/Tue/Wed/Thu/Fri/

Just so that you can understand I've written "Mon" four times just to indicate that it's a merged cell. The word "Mon" is only actually in cell A1 but the cell is merged from A1 to A4.

The same applies to "Tue" which is in B2:B4, and "Wed" which is in C2:C4, and "Thu" which is in D14. "Fri" is a single cell E4.

I'm writing a formula which will go in cells A5 to E5 which checks for the day of the week one cell above (i.e. A4 to E4).

However because the cells are merged it doesn't return the correct value except for "Fri".

How can I check if the cell above (i.e. A4 to E4) is part of a named range, and if it is then what is the value of the (first) cell?

Thanks,
May

 

[GlennUK]

You have mentioned "named range" more than once. What are the names of the ranges, and their definitions? Or did you really mean merged ranges without defined names?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[may1hem]

Thanks for your reply Glenn.

Sorry that's my mistake, I didn't mean "named range", I meant to say "merged cell".

So I meant to ask: "How can I check if the cell above (A4 to E4) is part of a merged cell, and if it is then what is the value of the (first) cell?

 

[GlennUK]

You could use an array formula ( entered using Ctrl-Shift-Enter instead of Enter ) to find the last filled cell in the range:

=INDEX(A$1:A$4,MAX(IF(A$1:A$4<>"",ROW(A$1:A$4),0)))

which would give you Mon for the above formula. Copy the formula across to the other columns, and you should have the results you want.

Remember to enter using Ctrl-Shift-Enter instead of Enter ... Excel will put curly brackets around your formula to indicate that it's an array formula.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[may1hem]

That's wonderful, thanks Glenn!

I didn't know about array formulas so I'll read about it now..

May

 

[may1hem]

Actually it works when the days of the week are in the top row, but if I insert some rows above and so the days of the week start in row 11 downward then the formula doesn't work anymore, even if I update the formula manually with the new range (A11:A14). I do use the CTRL+SHIFT+ENTER but this doesn't help.

Why is this?

Thanks,
May

 

[GlennUK]

Of course it won't work if you insert some rows above. The formula was designed to work for your requirements, as given, which was for data in days in rows 1 to 4.

A new formula is given here:

=INDEX(A$11:A$14,MAX(IF(A$11:A$14<>"",ROW(A$11:A$14)-ROW($A$11)+1,0)))

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[may1hem]

Aha! Thanks Glenn

 

[may1hem]

I want to use this in another situation where I don't want to specify the merged cell maximum range.

I just want to refer to one particular cell and find out what its merged cell value is.

How is this possible?

Thanks,
May

 

[GlennUK]

In that case you will need VBA. This UDF will do it:

Function First_cell_of_merge(mycell As Range) As String
    Application.Volatile
    First_cell_of_merge = ""
    If Not mycell.MergeCells Then
        First_cell_of_merge = mycell.Cells(1, 1).Value
    Else
        First_cell_of_merge = mycell.MergeArea.Cells(1, 1).Value
    End If
End Function

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[may1hem]

I don't want to use VBA for this spreadsheet but thanks for the idea Glenn..

Thanks,
May

 

[GlennUK]

If you don't want to use VBA, and you don't want to specify the merged cell maximum range, then use my first formula, and always specify row 1 as the start, no matter where the merging begins.

Cheers, Glenn.

Beauty is in the eye of the beerholder.