+ Post New Thread
Results 1 to 13 of 13
Office Software Thread, excel trying to get it to display a cell value if 3 conditions are met in Technical; bear with me im trying to get excel to fill in a table for me (basically it vertically monday to ...
  1. #1


    Join Date
    Mar 2009
    Location
    Leeds
    Posts
    6,506
    Thank Post
    227
    Thanked 848 Times in 727 Posts
    Rep Power
    287

    excel trying to get it to display a cell value if 3 conditions are met

    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,"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

  2. #2

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,432
    Thank Post
    1,432
    Thanked 1,160 Times in 794 Posts
    Rep Power
    705
    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

  3. Thanks to LosOjos from:

    sted (31st January 2014)

  4. #3


    Join Date
    May 2009
    Posts
    2,878
    Thank Post
    258
    Thanked 767 Times in 582 Posts
    Rep Power
    269
    Can you post the workbook?

  5. Thanks to pcstru from:

    sted (31st January 2014)

  6. #4


    Join Date
    Mar 2009
    Location
    Leeds
    Posts
    6,506
    Thank Post
    227
    Thanked 848 Times in 727 Posts
    Rep Power
    287
    Quote Originally Posted by pcstru View Post
    Can you post the workbook?
    if i "change the names to protect the innocent" i see no reason why not give me 5

  7. #5


    Join Date
    Mar 2009
    Location
    Leeds
    Posts
    6,506
    Thank Post
    227
    Thanked 848 Times in 727 Posts
    Rep Power
    287
    declassified lol version
    tech time no names.xlsx

  8. #6


    Join Date
    May 2009
    Posts
    2,878
    Thank Post
    258
    Thanked 767 Times in 582 Posts
    Rep Power
    269
    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.
    Last edited by pcstru; 31st January 2014 at 11:32 AM.

  9. Thanks to pcstru from:

    sted (31st January 2014)

  10. #7


    Join Date
    Mar 2009
    Location
    Leeds
    Posts
    6,506
    Thank Post
    227
    Thanked 848 Times in 727 Posts
    Rep Power
    287
    Quote Originally Posted by pcstru View Post
    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.
    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

  11. #8


    Join Date
    May 2009
    Posts
    2,878
    Thank Post
    258
    Thanked 767 Times in 582 Posts
    Rep Power
    269
    Well, hopefully you can work out from that what you need to do? If not, shout.

  12. #9


    Join Date
    Mar 2009
    Location
    Leeds
    Posts
    6,506
    Thank Post
    227
    Thanked 848 Times in 727 Posts
    Rep Power
    287
    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)

  13. #10

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,432
    Thank Post
    1,432
    Thanked 1,160 Times in 794 Posts
    Rep Power
    705
    Quote Originally Posted by sted View Post
    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

  14. Thanks to LosOjos from:

    sted (31st January 2014)

  15. #11


    Join Date
    Mar 2009
    Location
    Leeds
    Posts
    6,506
    Thank Post
    227
    Thanked 848 Times in 727 Posts
    Rep Power
    287
    Quote Originally Posted by LosOjos View Post
    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

  16. #12

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,432
    Thank Post
    1,432
    Thanked 1,160 Times in 794 Posts
    Rep Power
    705
    Quote Originally Posted by sted View Post
    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
    Last edited by LosOjos; 31st January 2014 at 04:09 PM.

  17. Thanks to LosOjos from:

    sted (31st January 2014)

  18. #13


    Join Date
    Mar 2009
    Location
    Leeds
    Posts
    6,506
    Thank Post
    227
    Thanked 848 Times in 727 Posts
    Rep Power
    287
    thanks never used it before and the tutorials i found were lets go with either too detailed or too brief lol

SHARE:
+ Post New Thread

Similar Threads

  1. Metro Electro on IE 10 - pockle to getting it to work (I think?!)
    By gerardsweeney in forum Educational Software
    Replies: 0
    Last Post: 19th September 2013, 12:38 PM
  2. Replies: 3
    Last Post: 10th March 2011, 07:42 PM
  3. Former 6th former trying to get IT technician post in East Devon
    By Aizen-sama in forum Educational IT Jobs
    Replies: 6
    Last Post: 12th January 2009, 05:41 PM
  4. Replies: 5
    Last Post: 16th December 2007, 06:26 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •