Prabhudharun

New Member
Joined
Feb 22, 2018
Messages
7
Hi..
I have 5X5 array table. I would like to know the project name, if one who save hours in the corresponding project.

for an example if i am selecting the name Name E from drop down list, the output supposed to be

Project C5
Project D5


<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

Below is the sample table

Project\NameName AName BName CName DName E
Project A2 95
Project B 2 6
Project C 1745
Project D1 615
Project E 2 2

<colgroup><col><col span="5"></colgroup><tbody>
</tbody>

Pls help me solve this
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,003
choose a name =Name E
Below is the sample table
Project\Name
Name AName BName CName DName E Name E
Project A295
Project B26
Project C1745 5
Project D1615 5
Project E22
Name E
I made 2 helper tables that show only the details for the chosen name Project C5
then I added a column to the lower helper that pulls down the numbers Project D5
from the upper helper table
clunky - but it works

<colgroup><col><col span="16"></colgroup><tbody>
</tbody>
 

Prabhudharun

New Member
Joined
Feb 22, 2018
Messages
7
Please can you elaborate how did you achieve it. As i dont have deep knowledge on excel...can you pls post me the steps.
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,003
col A row 1col Jchoose a nameName Ecol O
Project\NameName AName BName CName DName Erow 2
Project A2 95
Project B 2 6 col Kcol L
Project C 1745row 51
Project D1 6152
Project E 2 2 Project C53
Project D54
5
col O is a helper column so this will work no matter how many rows you have
cell K5 (the first potential entry in the output table)immediately under the cell with col K in it
has the formula
=IF(OFFSET($A$2,O5,MATCH($M$1,$B$2:$G$2,0))=0,"",OFFSET($A$2,O5,MATCH($M$1,$B$2:$G$2,0)))
this runs along row 2 from vol B to col G looking for what is in cell M1 (in this case Name E)
and then drops down by the value in O5 (1)
when this is dragged down in col K it finds another blank, a 5, a 5 and a blank
in col J (immediate left of col K where the blank blank 5 5 blank are)
this formula is in J5
=IF(K5="","",OFFSET($A$2,O5,1))
so where there is a blank it returns a blank
and where it is not blank it drops down from A2 by the number in O5 and skips one column to the right
this is also dragged down so Project D is found by
=IF(K8="","",OFFSET($A$2,O8,1))

<colgroup><col><col><col span="15"></colgroup><tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,108,751
Messages
5,524,616
Members
409,595
Latest member
therevford

This Week's Hot Topics

Top