+ Reply to Thread
Results 1 to 4 of 4

Finding a sum from multi-sheets for a drop down list value

  1. #1
    Registered User
    Join Date
    06-20-2015
    Location
    Plantation
    MS-Off Ver
    2013
    Posts
    6

    Finding a sum from multi-sheets for a drop down list value

    What I am trying to create
    I am looking to create a workbook that spreads for 52 weeks. A sheet, week, will consist of a user defined drop down list of 50 "employee name". Horizontally across the page the "employee name" can have between 1 - 13 "X's". Vertically the sheet separates into "days of the week" and can possibly have the same "employee name" under each day, MTWTFSS. On a master sheet a sum of all "X's" specifically assigned to "employee name" from all 52 sheets is recorded.

    Why I am trying to use Drop Down List
    I attempting to create a yearly tracker for specific behaviors of all employees. I want to avoid having each employee listed under each day through out the week. It would not be convenient to have a 350 lines sheet to cover a week. (50 Emp x 7 Days).

    What I have tried
    I have less than a few hours of experience with Excel but am a avid learner and eager to become more competent in Excel. The functions I currently have tried using range from COUNTIFS and SUMIFS. Currently, I am unable to get this function to work. The error received is #value!

    Please Login or Register  to view this content.
    This function is on Sheet2!A2 "master" where Sheet2!A1 is the first option in my "employee name" drop down list.
    Sheet1!A5 consist of the first "employee name" selected to have "X's" ranging from Sheet1!D5:S5
    Sheet1!A5 should equal Sheet2!A1 for sum to happen on master @ Sheet2!A2

    After searching I believe the problem comes in with blank entries and probably some sort of text type. I have no idea.

    Predictable Problems
    I know this function will not work as I do not know a way of conveniently summing the possibility of the "employee name" showing up under multiple days of a single sheet "week".

    Thank you for taking the time to view.
    Steven L.

    I am creating a sample workbook ATM; and will edit with an attachment in a few minutes.
    - In my sample I forgot to note "X's" under behaviors for employees. Imagine next to each employee there is an X allocated to a specific behavior.
    Attached Files Attached Files
    Last edited by Tpl0sured0mem; 06-20-2015 at 11:53 PM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Finding a sum from multi-sheets for a drop down list value

    Not sure if you were interested in a VBA solution, but I wrote one for this little scenario. I created an new function called COUNTX() which is used as:

    =CountX(A3, "x")

    What this does it search EVERY sheet except the sheet where this formula was entered for the A3 value in column A. Wherever it is found it counts all the "x" values it finds in the first 100 cells on that row. Here's the code:
    Please Login or Register  to view this content.

    I've installed this into your workbook. Press ALT+F11 to open the VBEditor, look in Module1 for the added code.
    Attached Files Attached Files
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    06-20-2015
    Location
    Plantation
    MS-Off Ver
    2013
    Posts
    6

    Re: Finding a sum from multi-sheets for a drop down list value

    Thank you very much. It works as intended and exactly what I was looking for.

    - Steven L.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Finding a sum from multi-sheets for a drop down list value

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Drop down list/multi select
    By leahdrawz in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-02-2014, 10:28 AM
  2. [SOLVED] Multi cell for drop down list more than one column
    By Grahambe2010 in forum Excel General
    Replies: 11
    Last Post: 11-27-2012, 08:16 AM
  3. [SOLVED] multi drop down list with multi-lookup
    By civileng12 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-31-2012, 06:50 PM
  4. Multi-Column Drop-down list ?
    By JB in forum Excel General
    Replies: 1
    Last Post: 12-26-2005, 02:35 PM
  5. Drop down list changes, worksheets and multi-user
    By ferdy in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 08-26-2005, 09:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1