Hello,
I have five rosters of names in a Roster tab. I have a cell on a separate Report tab that calls an individual roster. Can I use data validation to choose which roster to call to this Report cell?
I want to have two data validation areas on the Report tab. One that calls the correct roster to the other one.
Thanks for any help.
Here are pics of a couple of the rosters and the report:
DV prob - Roster.JPG
DV prob 1.JPG
Hard to say without an uploaded Excel file.
I usually do this with all the lists in contiguous columns, put unique identifying headers above each list and then in data validation use something like
=INDEX(the range,,MATCH(ref,the headers,0))
Is that helpful?
Dave
Thanks, Dave. I'll try that. Can I send you the file in a private message?
Please don't. Others will benefit from your post + plus you will likely get additional response to your thread. There are a lot of good ideas out there. Please take advantage of the opportunities. That's why we're here.
Sounds good. I'm attaching the file to this reply.
Please see the first three tabs: Roster, Week 1, and Report 1; and the last one: Data.
I would like to eliminate the Report tabs 2-5, and just have Report 1.
I would like this Report tab to have 2-3 data validation lists.
What I envision is: one list to call the correct roster from the Roster tab (located in D3),
one list to call the correct week from the Data tab (located in C3),
and one list to choose the correct individual from the chosen roster (located in E3).
Ideally, I would end up w/ just 8 tabs (instead of the current 12).
I tried to implement your formula: =INDEX(the range,,MATCH(ref,the headers,0)) - but I couldn't get that to work.
Any help would really be appreciated.
jaljr
Well I'm not sure what I proposed is such a good idea, and there is a lot of missing information and far more DV lists than I anticipated. I am trying to find my next question(s).
In the meantime there is one thing that definitely needs to be done. Please get rid of the merged cells. They cause havoc for formulas in the simplest of workbooks. With everything you have going on here they will cause nightmares.
I have removed the merged cells from the Roster tab, and added the label of the second column of each roster.
I'll attach my current draft of the worksheet.
If you look at the "Report 1" tab, you'll see three different data validation lists in C3:E3.
C3 is showing the week columns from the Data tab,
D3 and E3 currently show the same roster; but I'd like D3 to display the "Week 1 Roster, Week 2 Roster..." instead.
i.e., I want it to choose which roster cell E3 displays w/ its list.
Thanks again for the replies!
You're welcome. This is probably going to take me some time to figure out ... of course there is always Divine Inspiration
I really appreciate your time! And am hoping for that Divine Inspiration, too!
I'm really trying to consolidate the worksheet, so that it's not so cumbersome.
Thanks again,
jaljr
Hello again,
I consolidated the Week tabs into one "Feedback" tab using Groups and color-coded headings. This has helped w/ the business, imo.
All that's left now is figuring out how to consolidate the five Report tabs into one tab. A Report tab that can call any one of five rosters.
Thanks again for any help.
jaljr
Not sure if this what is required:
Tab "Report"
in D7
=IFERROR(INDEX(Data!$B$1:$F$17,MATCH($C7,Data!$A$1:$A$17,0),MATCH(Report!$C$3,Data!$B$2:$F$2,0)),"")
copy down to D11
similar formulae in D14/D18, C18
Data changes on selection of Week
Hello JohnTopley,
Thank you for the reply.
With your solution, I still don't see that I can choose another week's roster from the Roster tab?
It does great at showing the different week's data for the Week 1 roster; but doesn't allow me to switch to the Week 2 - Week 5 rosters.
I was hoping this could be done with a data validation cell in D3; but I'm wondering:
Could this possibly be done w/ a "smart" roster? One that calls one of the five rosters?
What I'm thinking is: maybe having six rosters on the Roster tab? One of the rosters would be "smart". e.g., if the C3 cell on the Report tab shows Week 2, then the "smart" roster would show the information from D2:E30 in it, etc.?
Thank you again for the reply.
Sincerely,
jaljr
Maybe another way to do this would be to put multiple If/Then formulas in each cell of the "smart" roster?
e.g., for cell A2 on the Roster tab (see attached), a formula that checks for the Week showing in cell C3 of the Report tab.
If C3 shows "Week 2", then A2 would display "John Test2"; if C3 shows "Week 3", then A2 would display "John Test3", etc.?
Here's an image of the Roster tab draft I'm attaching:
Smart Roster.JPG
Thanks again for any help,
jaljr
Hello again,
The "smart" roster did the trick!
Here's the formula I put in A2 on the Roster tab:
=IF(ISBLANK(IF(Report!$C$3=Data!$B$2,Roster!D2,IF(Report!$C$3=Data!$C$2,Roster!G2,IF(Report!$C$3=Data!$D$2,Roster!J2,IF(Report!$C$3=Data!$E$2,Roster!M2,IF(Report!$C$3=Data!$F$2,Roster!P2,"")))))),"",IF(Report!$C$3=Data!$B$2,Roster!D2,IF(Report!$C$3=Data!$C$2,Roster!G2,IF(Report!$C$3=Data!$D$2,Roster!J2,IF(Report!$C$3=Data!$E$2,Roster!M2,IF(Report!$C$3=Data!$F$2,Roster!P2,""))))))
Here's the formula I put in B2:
=IF(ISBLANK(IF(Report!$C$3=Data!$B$2,Roster!E2,IF(Report!$C$3=Data!$C$2,Roster!H2,IF(Report!$C$3=Data!$D$2,Roster!K2,IF(Report!$C$3=Data!$E$2,Roster!N2,IF(Report!$C$3=Data!$F$2,Roster!Q2,"")))))),"",IF(Report!$C$3=Data!$B$2,Roster!E2,IF(Report!$C$3=Data!$C$2,Roster!H2,IF(Report!$C$3=Data!$D$2,Roster!K2,IF(Report!$C$3=Data!$E$2,Roster!N2,IF(Report!$C$3=Data!$F$2,Roster!Q2,""))))))
I was able to fill the cells below from there.
I'm pretty sure this worksheet is about good to go!
Thank you again for all the help!
jaljr
In "Roster"
in A2
=INDEX($D$2:$Q$30,ROWS($1:1),MATCH(Report!$C$3 & "*",$D$1:$Q$1,0))
in B2
=INDEX($D$2:$Q$30,ROWS($1:1),MATCH(Report!$C$3 & "*",$D$1:$Q$1,0)+1)
Remove colon ":" from "Week 1:" etc in "Data"
Hello JohnTopley,
Those formulas look a lot more streamlined than mine. I'll try them out.
Thank you very much!
jaljr
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks