Excel/Reeturn the Header of the Named Range based on Logical Test
QUESTION: Hi Tom,
I have some Data and a header for that range.
It is a named range and the Header itself is the Name of the Range A2:A7.
I want to return the header of the range if value is TRUE.
So in B4, output should be HHHHH (Which the header of the range.)
I don't want to use A:A or A1:A7 in formula.
I want to catch the header just based on the cell which contains TRUE.
Can you help?
then drag fill down column B next to your data.
Since you are making a decision on the value in only one cell, then you only need to reference that cell (A2 in the example).
As you drag fill down, the A2 will change to A3 in cell B3, A4 in cell B4 and so forth (Relative Cell Reference).
The reference to A1 is fixed by writing it as $A$1 so that will not change (absolute cell reference)
Hopefully that answers what you are asking. If not, then follow up with additional information that will clarify your request.
[an error occurred while processing this directive]---------- FOLLOW-UP ----------
QUESTION: Hi Tom,
Thanks, Yes it gives me the result.
But as I specified, I want to catch the header just based on that relative reference.
Instead of $A$1, I want to set formula which refers "the Top row of the range - which is the Name of that Range", in which A2 is situated.
For example, =if(A2=true,index(RangeName,1,1)) this will give me the Value; contained in the first row of the Range right?
But there is nothing like =index(RangeName,-1,1) or =index(RangeName,0,1) !!
If that was possible then I could have the Header as a result.
So is there any alternative which does the same?
INDEX won't do that but OFFSET will
Returns the value in the row above the named range HHHH
---------- FOLLOW-UP ----------
I realized that even in this solution (and the one I suggested), I have to enter the name of the range in the formula; Which I don't want to do; and that is why I need formula.
I want to catch the header just based on that relative reference.
I don't want to type Header (or say Name of the Range) in the formula.
In Short, I want to do this -
If a2=true,"THE TOP ROW OF THE RANGE, OF WHICH, A2 IS A PART"
Yes, This is exactly what I want.
There isn't any built in function that will give you that information. An individual cell could be part of any number of names but even if it was in one named range, there is no function that will give you that information. The CELL function is the function that can return information about a cell, but name of namedrange membership is not one of the things it does.
Even VBA does not have a property that will tell you that. You would have to loop through all the named ranges and test if the cell in question is a member of that named range.
Pursing an array formula would pretty much require checking every named range but that would require having prior knowledge of all the named ranges and recording them in a formula.
So I don't see a built in function solution for this problem.