sted Posted January 31, 2014 Posted January 31, 2014 bear with me im trying to get excel to fill in a table for me (basically it vertically monday to friday and horizontally 1/2/3. what im trying to do is fill it in such that when you select a name in the dropdown box it displays their timetable so if i select me it would show im at place x monday period 1 and so on. Using countifs ive managed to get it to display how many people are busy in a given timeslot but can see how to get it to search through another sheet to see that if name dropdown and day = x and slot =x display whatever text is against that in another sheet (the sheet its pulling it from is basically formatted) school name contract type tech day slot ive used countifs on another sheet =COUNTIFS(schools!D:D,"Monday",schools!E:E,"1") but i suspect if i want it to find and display that monday slot 2 is "school b" thats not the way to go sorry if this makes no sense i suspect im looking at it in entirely the wrong way and have probably sidetracked myself down a dead end
LosOjos Posted January 31, 2014 Posted January 31, 2014 You'll need to use a vlookup. On the sheet containing the detail, create a unique lookup code in column A by concatenating the name, day and period (e.g. if name is column B, day C and period D, the forumla in A1 would be "=B1&C1&D1") You then do your lookup by concatenating the same fields in the same order on your timetable sheet to pull back the data you need. If that doesn't make sense, let me know and I'll knock together a mock up for you 1
sted Posted January 31, 2014 Author Posted January 31, 2014 Can you post the workbook? if i "change the names to protect the innocent" i see no reason why not give me 5
sted Posted January 31, 2014 Author Posted January 31, 2014 declassified lol version tech time no names.xlsx
pcstru Posted January 31, 2014 Posted January 31, 2014 (edited) tech time no names-1.xlsxI think @LosOjos' solution would work well enough. Insert a column before A on Setup tab which concatenates the name, day and slot, then on the Sheet1 matrix, concatenate the same information as the key for a vlookup (from the row and column headers + the name dropdown). When you change the name in the dropdown, the cells in the grid using vlookup should pick up the relevant information. Edited January 31, 2014 by pcstru 1
sted Posted January 31, 2014 Author Posted January 31, 2014 [ATTACH]22567[/ATTACH]I think @LosOjos' solution would work well enough. Insert a column before A on Setup tab which concatenates the name, day and slot, then on the Sheet1 matrix, concatenate the same information as the key for a vlookup (from the row and column headers + the name dropdown). When you change the name in the dropdown, the cells in the grid using vlookup should pick up the relevant information. it would have to be on schools as setup is just there to basically make the next sheet a lot of drop downs so no muppet typos something lol
pcstru Posted January 31, 2014 Posted January 31, 2014 Well, hopefully you can work out from that what you need to do? If not, shout.
sted Posted January 31, 2014 Author Posted January 31, 2014 right ok ive got it sort of working using vlookup but its putting namedayperiod in the table rather than school name so to me its working correctly (if the way ive done it is inelegant to say the least) so all i need to do is get the right display on the output code is =VLOOKUP(E4,schools!A2:F35,1,FALSE) so e4 is a column that says concatenate name (from a drop-down) day period. schools!A2:F35 is the data the first field is namedayperiod then school name (what i want as the output)
LosOjos Posted January 31, 2014 Posted January 31, 2014 right ok ive got it sort of working using vlookup but its putting namedayperiod in the table rather than school name so to me its working correctly (if the way ive done it is inelegant to say the least) so all i need to do is get the right display on the output code is =VLOOKUP(E4,schools!A2:F35,1,FALSE) so e4 is a column that says concatenate name (from a drop-down) day period. schools!A2:F35 is the data the first field is namedayperiod then school name (what i want as the output) You need to change the column in your vlookup, column 1 is the column you are looking up on hence getting namedayperiod 1
sted Posted January 31, 2014 Author Posted January 31, 2014 You need to change the column in your vlookup, column 1 is the column you are looking up on hence getting namedayperiod doh i was thinking that was where it was looking for a match
LosOjos Posted January 31, 2014 Posted January 31, 2014 (edited) doh i was thinking that was where it was looking for a match vlookup always looks at the left-most column of your selected range, the column is the value you want to return with 1 being the left-most column (the one you're looking up on Edited January 31, 2014 by LosOjos 1
sted Posted January 31, 2014 Author Posted January 31, 2014 thanks never used it before and the tutorials i found were lets go with either too detailed or too brief lol
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now