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

4
votes

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

4
excel
so you want the yellow "Column 1" to appear only if in the same row a green cell has a value? If so, why does table 2 not show any header? Anyway, try this formula, say in header cell C3: =IF(COUNTA($D4:$F4)>0,"Column 1","") – Peter Albert
Because in column 1 has 2 cells cell 2 and cell 3 have value>0, so the header show column 1 on row 2 and 3 – NCC
I think you mix up column and row header? To me, the yellow "Column 1" is a row header, the white one a column header! – Peter Albert
Ahhhh - could it be that with 1, 2 and 3 you always refer to the same table at different steps in the process? i.e. 1=the blank table 2=the input from the user 3=the result of the calculated headers? – Peter Albert

4 Answers

7
votes

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)),"")

1
votes

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?

0
votes

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.

0
votes

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.