Select Totals and Display them in Separate Sheet

  • Hi Everybody,


    I imagine this is actually very easy to do, but I'm a beginner and I suck.


    I need a code to pick out the totals and display them in a different sheet.


    What I have in sheet1 is something like this:
    Sales
    Sales Account1 10
    Sales Account2 10
    Sales Account3 10
    Sales Account4 10
    Sales Total 40
    HR
    HR Account1 10
    HR Account2 10
    HR Account3 10
    HR Account4 15
    HR Total 45
    Board
    Board Account1 50
    Board Account2 45
    Board Account3 60
    Board Account4 95
    Board Total 250



    What I would like, is for sheet2 to only show the department and the totals:


    Sales 40
    HR 45
    Board 250


    I hope this made sense. Thank you :)

  • Re: Select Totals and Display them in Separate Sheet


    A B C


    Sales
    Sales Account1 10


    Are your data in 3 differents column like this OR all together in one column??

    Regards


    Fotis :hammerhe:


    [SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.


    [SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.


    [SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!


    [SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

  • Re: Select Totals and Display them in Separate Sheet


    there are many options. My first option is Subtotal. In sheet3 you will get it. I have also used a formula where in cell E1 you will get a validation. Just select the department from there and you will get the result.


    I hope it will help you. Please find the attached file.

  • Re: Select Totals and Display them in Separate Sheet


    Ok. So in A2 of your second sheet put this formula.


    =LOOKUP(REPT("Z";5);CHOOSE({1\2};"";INDEX(Sheet1!$A$1:$A$20;MATCH(TRUE;INDEX(ISNA(MATCH(Sheet1!$A$1:$A$20;A$1:A1;0));0);0);1)))


    In B2, this one.


    =SUMPRODUCT((Sheet1!$A$1:$A$20=A2)*(Sheet1!$B$1:$B$20="Total")*(Sheet1!$C$1:$C$20))


    Copy down both of these as you need.


    Change ALL semi colons in my formulae to comma, if you neeed to do this(see my signature)


    Does this works for you?

    Regards


    Fotis :hammerhe:


    [SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.


    [SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.


    [SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!


    [SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

  • Re: Select Totals and Display them in Separate Sheet


    Thank you!:thanx:

    Regards


    Fotis :hammerhe:


    [SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.


    [SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.


    [SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!


    [SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

  • Re: Select Totals and Display them in Separate Sheet


    You are welcome.


    Thanks for the feed back.

    Regards


    Fotis :hammerhe:


    [SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.


    [SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.


    [SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!


    [SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

  • Re: Select Totals and Display them in Separate Sheet


    Is there a way to do it using VBA code? So that every time sheet2 is activated, it finds the cells in column B from sheet1 where it says Total, and displays the information from coulumn A and C of those rows?

  • Re: Select Totals and Display them in Separate Sheet


    My VBA knowledge is amost nonexistent!


    But why do you want to use VBA, when you can do this using formulas?


    Of course in any case, a sample workbook will help us, so to be able to help you.

    Regards


    Fotis :hammerhe:


    [SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.


    [SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.


    [SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!


    [SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

  • Re: Select Totals and Display them in Separate Sheet


    Its just the workbook will be send to different people, who will then add the details from their department. The workbook is meant to update automatically when additional departments are added, without adding/copying formulas. In short, it should be able to handle:
    - additional departments (the structure and number of accounts for each department will always be the same for all departments)
    - additional accounts for all the departments, meaning the number of accounts might change (but still be kept the same for all departments)


    I hope this made sense.


    I've tried adding the sample workbook. It's just that in the sample workbook here, the code needed is for sheet5, where the totals of the sheets1 to 4 are displayed. Its just done manually in the sample I've attached.


    Again, I appreciate any help I can get.

  • Re: Select Totals and Display them in Separate Sheet


    Apologize for this but i'll have a delay in my answer as i am at work and have some seriously problems just now. Maybe 1-2 hours later..


    Yes. We can do this using formulas.

    Regards


    Fotis :hammerhe:


    [SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.


    [SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.


    [SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!


    [SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

  • Re: Select Totals and Display them in Separate Sheet


    Could something like this works for you?

    Files

    Regards


    Fotis :hammerhe:


    [SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.


    [SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.


    [SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!


    [SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!