Need help in combining the Data Validation formulas

[eshwarhalaharvi]

I have Two tables one filled with different departments details and the other one being empty.


I have created a data validation list on top of the empty table for departments. I need help in getting the formulas right to get only the department wise details in the empty table.


Note that both the table contains the similar headings for each column.

 

[kaushik03]

Hi eshwarhalaharvi,


I assume you have data from A1 to D7 as follows:


Department Detail1 Detail2 Detail3

A 1 7 13

B 2 8 14

C 3 9 15

D 4 10 16

E 5 11 17

F 6 12 18


You have put data validation at F1 for name of departments(A to F)


You have blank table from G1 to I1 LIKE THIS

Detail1 Detail2 Detail3

At G2 the formula would be: =VLOOKUP($F$1,$A$2:$D$7,2,0)

At H2 the formula would be: =VLOOKUP($F$1,$A$2:$D$7,3,0)

At I2 the formula would be: =VLOOKUP($F$1,$A$2:$D$7,4,0)


Hope this helps.


Regards,

Kaushik

 

[eshwarhalaharvi]

Hi Kaushik, Thanks for help but when i do that it will pick the very first value in the table array and does not get me the desired values. Below is the table 1 which was refering to.

Department City Name Level Plan Hrs Plan %age Actual Hrs Actual %age

Marketing A Anand Manager 198 189 160

Marketing B Akash GM 198 144 140

Sales C Ashish Clerk 198 147 135

Finance D Akruthi Clerk 198 189 150

Finance E Aaruthi Asst 198 108 100

The cells in the second table with same column headings as first table should get auto populated when i select the dept name from the drop down

 

[Jai9]

Hi eshwarhalaharvi,


This might not be the best approach, but this is what I can think of.

I have the data provided by you in range D1:K6.

I have created a dropdown using form control and linked to F8.

In D11, I have this formula

=IF($F$8=1,OFFSET($D$1,1,0),IF($F$8=2,OFFSET($D$1,3,0),OFFSET($D$1,4,0)))

In D12, I have

=IF($F$8=1,OFFSET($D$1,2,0),IF($F$8=2," ",OFFSET($D$1,5,0)))

In E11 put

=IF($F$8=1,OFFSET($D$1,1,1),IF($F$8=2,OFFSET($D$1,3,1),OFFSET($D$1,4,1)))

In E12,

=IF($F$8=1,OFFSET($D$1,2,1),IF($F$8=2," ",OFFSET($D$1,5,1)))

Similarly, change the columns number for other columns.


Jai

 

[kaushik03]

Ok....


So if I understand this correctly, once you select, say "marketing"...two rows of data should be populated(A to 160 and B to 140)


If so then try the following at L2:


=IFERROR(INDEX(B$2:B$6,SMALL(IF($K$2=$A$2:$A$6,ROW($A$2:$A$6)-MIN(ROW($A$2:$A$6))+1,""),ROW(A1))),"")...PRESS AS CTRL+SHIFT+ENTER as an array formula...copy down (as far as you want) and across


I assume

Data layout:

1)you have the headers from A1 to G1

2)Data spread across A2 to G6


Empty table layout:

1)Same headers from K1 to Q1

2)Data validation at K2

3)Write the formula at L2......Copy down and across


Hope this works.


In case still it does not work, consider uploading a file here:

http://chandoo.org/forums/topic/posting-a-sample-workbook


Regards,

Kaushik