How to retrieve column header for non empty zero values as rows in a seperate sheet?

Hello Willy Lau,

Thank you for the explanation, I could solve my problem using this formula too.

Really overwhelmed by the response I got from this community, and hats off to all your logic, most of the solutions suggested by community members

---

Willy Lau wrote:

Assumption:

1. your table on A1:AY501 in sheet1, where project column on column a, and resource headers from B1 to AY1.
2. project selection cell on A1 in sheet2
3. resource listing from A3:B53. Its size matches with total number of resource
4. resource with empty cell or 0(zero) will not be listed

 

Steps:

1. In Name manager, create a name, ProjectData

   =Sheet1!$A$2:$A$501

2. In Name manager, create a name, ResourceData

   =Sheet1!$B$2:$AY$501

3. In Name manager, create a name, ResourceHeaders

   =Sheet1!$B$1:$AY$1

4. In Name manager, create a name, SelectedProject

   =Sheet2!$A$1

5. In Name manager, create a name, ProjectResourceRow

   =INDEX(ResourceData, MATCH(SelectedProject,ProjectData,0),0)

6. In Name manager, create a name, ColumnList

   =IF(--(ProjectResourceRow<>0)=0,NA(),--(ProjectResourceRow<>0)*(COLUMN(ProjectResourceRow)-COLUMN(Sheet1!$A:$A)))

7. In Sheet2, cell A3

   =IFERROR(INDEX(ResourceHeaders, 1, AGGREGATE(15,3,ColumnList,ROW(Sheet2!A3)-ROW(Sheet2!$A$2))),"")

8. In Sheet2, cell B3
   

   =IFERROR(INDEX(ProjectResourceRow, 1, AGGREGATE(15,3,ColumnList,ROW(Sheet2!A3)-ROW(Sheet2!$A$2))),"")

9.  Copy Sheet2!A3:B3, and paste the formula to Sheet2!A4:B53.

 

Try it.

---

worked for me. Thank you once again.