Drop Down question

[Jagermeister]

Newbie here,
I've looked through some of the previous posts, but I'm really not sure what i'm looking for.

Basically i have a Master spread sheet that i would like to extract data from into other tabs/worksheets. My Master sheet is very simple...Cell A1 is called "Name" and i have listed all of the names in column A (All of the Names are unique). All of the remaining cells in row 1 are the dates of the year, though there is a "week total" column at the end of every week. There are a total of 52 "week total" columns. I have populated all of the remaining cells in the table with a # indicating how many assignments each "name" has completed during that day of the week. Of course the "week total" column at the end of every week adds up all of the assignments completed per each name.

I would then like to create another worksheet called "week 1." In this worksheet cell A1 is also labeled "Name." I then need to be able to go to Cell "B1" and select a "Name" using a drop down. The drop down will have the list of names that are listed in the master worksheet column A. When i select the name, i need all of the data from week 1 of the master worksheet to be populated in that row. This scenario will need to be replicated on the "week 1" worksheet throughout column A.

Can someone please assist on how to create a drop down in the "week 1" worksheet that will allow me to pull all of the appropriate data for week 1 from the master worksheet?

 

[ken2step]

You would use "Validation" to do this. But since you can not select a list from another worksheet you will need to create a "Named" range for this. Go to your master list and select all the names in column A, plus your header which you said was Name. Then go to Insert/Name/Create and use the Top Row check mark to name your range "Name". Then in Cell B1 of your Week1 worksheet go to Data/Validation from your menu. In the settings tab of this box choose "List" and the source field enter =Name. Hit OK and you will have what I think it is you want.
Hope this helps you get started!

 

[Jagermeister]

Thanks Ken, the drop down works! But maybe i didnt word my question correctly? Currently only the "Name" i select appears, but no data.

When I select a "name" using the drop down in B2 (not B1, Row 1 is the header row), i would like the week 1 data that is associated with that name from the master worksheet to also appear. Currently the Master sheet has the data for all the dates/weeks. I am trying to summarize that data in the week1 worksheet. Hopefully i've explained myself correctly...
[/img]

 

[acw]

Hi

1) Is the name selection drop down going to be in B2, or A2?
2) Which data for that name is actually coming in - the total data from the week1 column, or each day's entry for the 7 days.
3) Where will the output from 2 be going?


Tony

 

[Jagermeister]

Hi

1) Is the name selection drop down going to be in B2, or A2?
2) Which data for that name is actually coming in - the total data from the week1 column, or each day's entry for the 7 days.
3) Where will the output from 2 be going?


Tony

1) There will be multiple drown downs in column B begining with B2
2) Each day entry for the 7 days
3) C2- I2 ($C$2:$I$2)

 

[acw]

Hi

Highlight the range 'week 1'!C2:I2 and array enter (ctrl, shift, enter) the formula

=OFFSET(Master!$A$1,MATCH('Week 1'!$B2,Master!$A$2:$A$6,0),MATCH("week 1",Master!$1:$1,0)-8,1,7)

The source sheet is called master. On this sheet, A1 has Name, B1:H1 are the week1 dates, J1 has the heading "week 1" and contains the sum of the previous 7 columns. This structure is repeated across the page with Q1 containing "Week 2", Y1 containing "Week 3" etc.

HTH

Tony

 

[Jagermeister]

Hi

Highlight the range 'week 1'!C2:I2 and array enter (ctrl, shift, enter) the formula

=OFFSET(Master!$A$1,MATCH('Week 1'!$B2,Master!$A$2:$A$6,0),MATCH("week 1",Master!$1:$1,0)-8,1,7)

The source sheet is called master. On this sheet, A1 has Name, B1:H1 are the week1 dates, J1 has the heading "week 1" and contains the sum of the previous 7 columns. This structure is repeated across the page with Q1 containing "Week 2", Y1 containing "Week 3" etc.

HTH

Tony

Perfect, but what do i change in the formula to reflect the correct data for the 'week 2' sheet and so on? Also, the formula appears to be off somewhat. i had to add 2 columns in the "master" work sheet before the first day of the week in order to get the formula to match up correctly on the 'week 1" sheet. Any ideas?

Thanks for your help.

 

[acw]

Hi

1) For week 2, change the "week 1" to "week 2". This item has to be the heading in the master sheet that is the total for the week.

2) How does your data structure compare to the positions I gave in my response.


Tony