Hi,
I'm looking for some type of formula to carry over a columns data when the date is selected from the drop down list. On the weekly analysis tab I would like the users to be able to select a week from the drop down list then the corresponding data is generated below it from the Dashboard worksheet.
Thank you again for your time,
Josh
Drop Down List Vlookup
-
- StarLounger
- Posts: 94
- Joined: 16 Oct 2021, 16:22
Drop Down List Vlookup
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- StarLounger
- Posts: 94
- Joined: 16 Oct 2021, 16:22
Re: Drop Down List Vlookup
365 Business, Version 2301
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Drop Down List Vlookup
In B7:
=IF(INDEX(Dashboard!$G$7:$KU$86,SEQUENCE(80),MATCH(B6,Dashboard!$G$5:$KU$5,0))="","",INDEX(Dashboard!$G$7:$KU$86,SEQUENCE(80),MATCH(B6,Dashboard!$G$5:$KU$5,0)))
It will automatically spill to the rows below.
Fill to the right to B8.
I copied the formatting from the Dashboard sheet, then changed the background color.
See the attached version.
=IF(INDEX(Dashboard!$G$7:$KU$86,SEQUENCE(80),MATCH(B6,Dashboard!$G$5:$KU$5,0))="","",INDEX(Dashboard!$G$7:$KU$86,SEQUENCE(80),MATCH(B6,Dashboard!$G$5:$KU$5,0)))
It will automatically spill to the rows below.
Fill to the right to B8.
I copied the formatting from the Dashboard sheet, then changed the background color.
See the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 94
- Joined: 16 Oct 2021, 16:22
Re: Drop Down List Vlookup
That is one complex formula, I didn't think it would be that difficult for you I apologize. Thank you very much it works perfectly.
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Drop Down List Vlookup
It isn't that complicated. It looks complicated because the INDEX(...) part is repeated. The INDEX part is
INDEX(Dashboard!$G$7:$KU$86,SEQUENCE(80),MATCH(B6,Dashboard!$G$5:$KU$5,0))
This takes the entire range, returns all 80 rows by using SEQUENCE(80) = {1,2,3,..., 80} as row index, and only the desired column by using MATCH(B6,Dashboard!$G$5:$KU$5,0) as column index.
Unfortunately, this will return 0 if the source cell is empty. So we check if INDEX refers to an empty cell, and return "" if so, otherwise we return the result of INDEX.
INDEX(Dashboard!$G$7:$KU$86,SEQUENCE(80),MATCH(B6,Dashboard!$G$5:$KU$5,0))
This takes the entire range, returns all 80 rows by using SEQUENCE(80) = {1,2,3,..., 80} as row index, and only the desired column by using MATCH(B6,Dashboard!$G$5:$KU$5,0) as column index.
Unfortunately, this will return 0 if the source cell is empty. So we check if INDEX refers to an empty cell, and return "" if so, otherwise we return the result of INDEX.
Best wishes,
Hans
Hans