Apr 15 2019 10:06 PM
I am looking at finding a formula for:
=IF(B25=Sheet1!A2 than B26=Sheet1!B2 and B27=Sheet1!C2 .... and so on BUT
=IF(B25=Sheet1!A3 than B26=Sheet1!B3 and B27=Sheet1!C3
Apr 15 2019 11:14 PM - edited Apr 15 2019 11:23 PM
It seems that you need a formula like this in B26, copied down rows:
=INDEX(CHOOSE(1+(B$25=Sheet1!A$3),
Sheet1!B$2:J$2,Sheet1!B$3:J$3),
ROW()-25)
May 08 2019 04:10 PM
I have tried your formula and it works for the first drop down, but when I drop down to the second name which is on Sheet1!A$2 it doesnt pick up the line he is on. Am I missing something?
May 08 2019 09:20 PM
May 08 2019 09:40 PM
@Twifoo Please see attached Thanks.
May 08 2019 10:02 PM
May 08 2019 10:27 PM
@Twifoo the drop down in Sheet2 B25, so when you choose PR it pre fills Sheet2 B26:B29 from the info in Sheet1 B2:F2
And Im hoping that you can get it to do the same thing when you choose MB from the Sheet2 B25 drop down to pick up the info in Sheet1 B3:F3 to show in the Sheet2 B26:B29 fields
May 08 2019 10:51 PM
In the attached file, I modified Sheet2!A26 from "Manager Position" to "Role" so that it will coincide with Sheet1!C1. Thereafter, a simple VLOOKUP will return your desired results in Sheet2!B26:B29. Thus, the formula in Sheet2!B26, copied down to Sheet2!B29, is:
=VLOOKUP(B$25,Sheet1!A$2:G$31,
MATCH(A26,Sheet1!A$1:G$1,0),0)