CODEWITHSUNDEEP
excel
NCC
NCC

Reputation: 819

Show the column header to a row if there is value in cells of that columns without VBA

Update 1:

Because my question is not clear, so I post second example

Example 2

Because row 2, there is value 1 at Item 04 so the Get Item Name = Item 04. It is random and have a large number of columns (500).

The problem:

I would like to have a way to get a column header if there is any value input to the cells under that header. Please note that if at row 2 and column 1 has value, then other cell of row 2 will not have any value (other than 0).

It is hard to explain the problem in words so I have created an example.

Sample

Upvotes: 4

Views: 34851

Answers (4)

Peter Albert
Peter Albert

Reputation: 17475

Assuming that you want a solution with more than 3 columns, this formula will return you the right header.

I assume that you data starts in row 2, column B - and row 1 contains the column headers.

Use this formula in B1 and copy it down:

=IF(COUNTA($B2:$D2)=0,"",INDEX($B$1:$D$1,MATCH(FALSE,INDEX(ISBLANK($B2:$D2),0),0)))

If your data extends further than column D, simply change this in the formula.

Upvotes: 0

barry houdini
barry houdini

Reputation: 46361

I posted a formula in the comments above, you may not have seen it. This is it:

=IF(COUNTA($B2:$D2)=0,"",INDEX($B$1:$D$1,MATCH(TRUE,INDEX($B2:$D2<>"",0),0)))

that will get the header for the first instance of a populated cell - if you have numeric values and want to ignore zeroes change to

=IF(COUNTA($B2:$D2)=0,"",INDEX($B$1:$D$1,MATCH(TRUE,INDEX($B2:$D2>0,0),0)))

Either way the formula can be extended to as large a range as you need

.....and if you have 500 columns you could use IFERROR to shorten a little

=IFERROR(INDEX($B$1:$D$1,MATCH(TRUE,INDEX($B2:$D2>0,0),0)),"")

Upvotes: 7

TiTO Maro
TiTO Maro

Reputation: 11

for a particular table which is relative small table with countable number of columns (i.e 3) try this 

=IF(COUNTA(G8)>0,"Column 1",IF(COUNTA(H8)>0,"Column 2",IF(COUNTA(I8)>0,"Column 3",""))).

my question is what if you have table contain 20 or 100 columns?

Upvotes: 1

Cristian Lupascu
Cristian Lupascu

Reputation: 40556

I assume that when you say "column header" you really mean "row header".

Considering that the first cell (containing the text "Column Header") is at A1 this is the formula you have to introduce in cell A2:

=IF(B2>0, $B$1, IF(C2>0, $C$1, IF(D2>0, $D$1, "")))

Drag this formula down on as many rows as you need and it'll (hopefully) achieve what you want.

Upvotes: 0

Related Questions