Page 1 of 2
1
2
LastLast
  1. #1

    Question Need help with Excel spreadsheet

    Hello, I would like to ask for a help. I'm making a spreadsheet for attendence for our guild. I decided to go with Excel so I can upload it then to Google Docs or similar service so everyone has access to it. What I would like to do though, is to create a dropdown menu with months (I can do that) and after you choose one attendence for that month would show up (I dunno how to do this). Basically some kind of zone that would load data from other list of the spreadsheet probably according to chosen month. Any ideas? Problem is that I don't know how to call it so I cannot even google it.
    Thank you in advance

  2. #2
    Where's the attendance data going to be?

    Is it just going to be a table with months and names as the columns/rows?

  3. #3
    Merely a Setback Reeve's Avatar
    10+ Year Old Account
    Join Date
    Oct 2009
    Location
    Houston, TX USA
    Posts
    28,800
    Quote Originally Posted by Torm View Post
    Hello, I would like to ask for a help. I'm making a spreadsheet for attendence for our guild. I decided to go with Excel so I can upload it then to Google Docs or similar service so everyone has access to it. What I would like to do though, is to create a dropdown menu with months (I can do that) and after you choose one attendence for that month would show up (I dunno how to do this). Basically some kind of zone that would load data from other list of the spreadsheet probably according to chosen month. Any ideas? Problem is that I don't know how to call it so I cannot even google it.
    Thank you in advance
    Sounds like you want to use a VLOOKUP based on a selection from a validation list.
    'Twas a cutlass swipe or an ounce of lead
    Or a yawing hole in a battered head
    And the scuppers clogged with rotting red
    And there they lay I damn me eyes
    All lookouts clapped on Paradise
    All souls bound just contrarywise, yo ho ho and a bottle of rum!

  4. #4
    Quote Originally Posted by Torm View Post
    Hello, I would like to ask for a help. I'm making a spreadsheet for attendence for our guild. I decided to go with Excel so I can upload it then to Google Docs or similar service so everyone has access to it. What I would like to do though, is to create a dropdown menu with months (I can do that) and after you choose one attendence for that month would show up (I dunno how to do this). Basically some kind of zone that would load data from other list of the spreadsheet probably according to chosen month. Any ideas? Problem is that I don't know how to call it so I cannot even google it.
    Thank you in advance
    Before you do all that, when I use to run raids I had an addon that took attendance for everyone and organized it. Just wish I could remember the name....

  5. #5
    Just to clarify, are you using Microsoft Excel or Google Sheets?

    It might be easier to just use the Sheets if you're just wanting to upload it anyway.

    If so, maybe this will help:
    https://productforums.google.com/for...cs/kxdP3Sg6ezg

    I believe this is what Reeve is talking about, but I just skimmed over it.

  6. #6
    This sounds like what you're looking for. Let me know if anything needs explaining....

    https://docs.google.com/spreadsheets...gid=1586523955

  7. #7
    I already made the attendence table, here is a link: https://docs.google.com/spreadsheets...it?usp=sharing
    I'm using both MS Excel and Google Sheets. So far they work exactly the same.
    Pretty much on the Attendence list I would like to have on top of the list a dropdown menu with months. Under it there would be a "zone" which would load data (cells) from other lists. Depending on the month selected in the dropdown menu.

  8. #8
    Stealthed Defender unbound's Avatar
    7+ Year Old Account
    Join Date
    Nov 2014
    Location
    All that moves is easily heard in the void.
    Posts
    6,798
    TBH, long ago when I ran a guild, we did that via tools that were included in most guild websites (don't remember which one anymore). Can help you track a lot of things, and was easy enough to work with that we were able to manage everything pretty easily. Also looked nicer that way.

  9. #9
    Quote Originally Posted by Nymrohd View Post
    Aha, OK you need to use the data from that cell to reference the sheet in the book for the average function. That's doable
    But how?

  10. #10
    Merely a Setback Reeve's Avatar
    10+ Year Old Account
    Join Date
    Oct 2009
    Location
    Houston, TX USA
    Posts
    28,800
    Quote Originally Posted by Torm View Post
    I already made the attendence table, here is a link: https://docs.google.com/spreadsheets...it?usp=sharing
    I'm using both MS Excel and Google Sheets. So far they work exactly the same.
    Pretty much on the Attendence list I would like to have on top of the list a dropdown menu with months. Under it there would be a "zone" which would load data (cells) from other lists. Depending on the month selected in the dropdown menu.
    OK, so it sounds like you could do this with a combination of a VLOOKUP and an INDIRECT function.

    So for example, if you have the Month in cell F1, the function for cell C3 might look like this: =VLOOKUP(A3,INDIRECT(""&$F$1&"!"&"$A$3:$H$30"),6,FALSE)

    Of course you'd need to extend the $A$3:$H$30 range if you added a lot more people to your roster.
    Last edited by Reeve; 2016-04-13 at 07:01 PM.
    'Twas a cutlass swipe or an ounce of lead
    Or a yawing hole in a battered head
    And the scuppers clogged with rotting red
    And there they lay I damn me eyes
    All lookouts clapped on Paradise
    All souls bound just contrarywise, yo ho ho and a bottle of rum!

  11. #11
    Titan I Push Buttons's Avatar
    10+ Year Old Account
    Join Date
    Nov 2013
    Location
    Cincinnati, Ohio
    Posts
    11,244
    What's wrong with just a simple table?

  12. #12
    Quote Originally Posted by Bovinity Divinity View Post
    Aren't there in-game mods (assuming WoW, at least) that can automatically track attendance, upload it to a webserver, display stats and all that sort of thing?

    ...and if not, why haven't I made that? Wonder if it's too late in the life cycle of the game for that sort of thing.
    There are many mods for this kind of thing but the thing is that they usually have a problem with not being good with alts. Also they are not accessable usually from outside of game, like on mobile phone or just in browser.

  13. #13
    So....

    I think you want to have a sheet per month and then be able to pull in through to the summary page yep?

    In that case try this:

    https://docs.google.com/spreadsheets...it?usp=sharing

    You'll need to name each sheet after the month spelt in full for indirect to work.

    EDIT: basically what Reeve said, but as a working example.
    Last edited by klogaroth; 2016-04-13 at 07:05 PM.

  14. #14
    I think I explained wrong what I'm trying to achieve. So I made a picture explaining it properly.
    http://i.imgur.com/TjkTxVr.png

  15. #15
    Merely a Setback Reeve's Avatar
    10+ Year Old Account
    Join Date
    Oct 2009
    Location
    Houston, TX USA
    Posts
    28,800
    Quote Originally Posted by Torm View Post
    I think I explained wrong what I'm trying to achieve. So I made a picture explaining it properly.
    http://i.imgur.com/TjkTxVr.png
    It's the same thing Klogaroth (who looks like Manakin, so I got his name wrong) and I already mentioned, just with a wider range on the VLOOKUP and the column arguments adjusted.
    Last edited by Reeve; 2016-04-13 at 07:13 PM.
    'Twas a cutlass swipe or an ounce of lead
    Or a yawing hole in a battered head
    And the scuppers clogged with rotting red
    And there they lay I damn me eyes
    All lookouts clapped on Paradise
    All souls bound just contrarywise, yo ho ho and a bottle of rum!

  16. #16
    Alright. Cheers! I will try work something out after we finish raiding tonight Thank you

  17. #17
    Merely a Setback Reeve's Avatar
    10+ Year Old Account
    Join Date
    Oct 2009
    Location
    Houston, TX USA
    Posts
    28,800
    Quote Originally Posted by Nymrohd View Post
    He could just define the range dynamically as well
    You'd still need to redefine the range at the top of the list if you added to the bottom, even if you left the bottom end of the range open and dynamic.

    Even if you did it based on a dynamic named range, you'd have to insert rows inside the range in order for it to adjust properly.

    You could make the VLOOKUPS based on column ranges like A:H, but I try not to do that, since with large workbooks it can slow things way the hell down.
    Last edited by Reeve; 2016-04-13 at 07:15 PM.
    'Twas a cutlass swipe or an ounce of lead
    Or a yawing hole in a battered head
    And the scuppers clogged with rotting red
    And there they lay I damn me eyes
    All lookouts clapped on Paradise
    All souls bound just contrarywise, yo ho ho and a bottle of rum!

  18. #18
    https://docs.google.com/spreadsheets...it?usp=sharing


    I've updated this, I think that's what you're after now?

  19. #19
    Merely a Setback Reeve's Avatar
    10+ Year Old Account
    Join Date
    Oct 2009
    Location
    Houston, TX USA
    Posts
    28,800
    Quote Originally Posted by Nymrohd View Post
    Eh I don't think he'd need any lookup values at all. Type this formula on F2: =indirect("'"&$F$1&"'!"&"I2") then paste it on the entire F2:ZZ26 range. Add conditional formating in that range to hide all the error values (when you have no month selected in F1) and you are done. But really this is still not well done; you'd be better off dynamically updating your ranges when you add new raiders.
    Yeah, if he's really looking for the base data to be pulled in, you're right. Better to just have it pull directly. You'd want to pull the A column in as well though, so that you don't end up with the wrong name being attached to the data.
    'Twas a cutlass swipe or an ounce of lead
    Or a yawing hole in a battered head
    And the scuppers clogged with rotting red
    And there they lay I damn me eyes
    All lookouts clapped on Paradise
    All souls bound just contrarywise, yo ho ho and a bottle of rum!

  20. #20
    Merely a Setback Reeve's Avatar
    10+ Year Old Account
    Join Date
    Oct 2009
    Location
    Houston, TX USA
    Posts
    28,800
    Quote Originally Posted by Nymrohd View Post
    Honestly, I'd populate the table with a macro.
    Eh, you could make a really simple macro for this, but it seems like overkill. The simple INDIRECT on the data set you mentioned earlier would do the job just fine without having to run anything.
    'Twas a cutlass swipe or an ounce of lead
    Or a yawing hole in a battered head
    And the scuppers clogged with rotting red
    And there they lay I damn me eyes
    All lookouts clapped on Paradise
    All souls bound just contrarywise, yo ho ho and a bottle of rum!

Posting Permissions

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