Drop down list to fill column automatically based on different table. 1

[marcq]

Hello.

I can't find proper tutorial for the issue like below.

Assume we have columns with rows:

|Task|Persons(D1)|Hours|
| T1 | Js | 5 |
| T2 | JW | 8 |

(D1 - is drop down list of namesthat I created in different sheet)
It works for me fine.

Now I open 3th Sheet where have colums (and want to have data in it like below filled autmatically based on D2)

|Task|Person|MON|TUE|WED|FRI|
D2 D3 D4 D4 D4 D4

D2 I drop down based on sheet mentioned earlier. That I have already working.
But now I want is D3 and D4 (data) beeing autmatically filled and based on the table mentioned in the beginning.
D3 is Persons(D1), D4 is Hours but dependend what I drop down in Task (D2)

Like in example here:
1. I drop down (D2) and take Task1 (T1)

D2->T1
Table will automatically fill:

|Task|Person|MON|TUE|WED|FRI|
T1 Js 5 5 5 5

If I change (Drop down) Task to T2
it will change to

|Task|Person|MON|TUE|WED|FRI|
T1 JW 8 8 8 8

Thank you in advance.

 

[SkipVought]

hi,

Ususlly refer to TABLES. So you have a lookup value that you perform a lookup in a table.

Is there ONE person per task or multiple?

In what TABLE is the Task to Person/Hours per day?

You probably need a FORMULA like
[tt]
=VLOOKUP(LookupValueRef,LookupTableRef,LookupColumn,False)
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

I assume that your first table is you lookup table (|Task|Persons(D1)|Hours|)

Lets NAME the range tLookup

Then you have your |Task|Person|MON|TUE|WED|FRI| table. Lets asasume thats begins in A1.

Your formula would be in B2 ...
[tt]
B2: =VLOOKUP($A2,tLookup,Column(),False)
[/tt]
Copy it across and down thru all the rows of tasks.




Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[marcq]

Hi
Thank you for help.
It seems I need function just lookup.
Your hint helped me however becouse of this lookup key.
I'm testing it now.

 

[SkipVought]

Just a note.

My objective, when crafting a formula, is to make ONE FOMULA that can be used in EVERY ROW AND COLUMN, without having to change anything.

I just realized that my posted formula has a problem: COLUMN() only works in the columns B & C.

So here's the fix:

Modify your display table as follows
[tt]
2 3 3 3 3
Task|Person|MON|TUE|WED|FRI|

[/tt]
then the formula in B3, the first row of data
[tt]
B3: =VLOOKUP($A2,tLookup,B$1,False)
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]