+ Reply to Thread
Results 1 to 7 of 7

How to make the 'Total's' row automatically move up and down as list grows and shrinks?

Hybrid View

  1. #1
    Registered User
    Join Date
    08-30-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    12

    How to make the 'Total's' row automatically move up and down as list grows and shrinks?

    Hello all and Happy 2014.

    I have a list that grows and shrinks based on a condition as the list itself is based on this condition. The list is an array formula with a 'small' function (it is looking up another 'master' list). If we say the condition is color, here is what the list for 'blue' would look like;

    1). Dogs - Blue - 2
    2). Cats - Blue - 4
    5). Giraffes - Blue - 1
    10). Elephants - Blue - 2
    11). Mice - Blue - 3
    14). Rats - Blue - 3
    16). Birds - Blue - 4
    20). Fish - Blue - 1

    Total: 21

    But that same list, for 'red' will be a lot smaller;

    3). Kanagroos - Red - 3
    6). Frogs - Red - 5







    Total: 8




    - BUT, as I have tried to demonstrate, the 'Total' row is still accommodating all that space for the 'blue' condition (for which there were many more entries than for 'red').

    How do I make the 'total' row automatically move up and down depending on how long the list is? Ideally, the total row should always appear one row below the last entry on the list...

    Thanks in advance...

  2. #2
    Forum Contributor rajeshturaha's Avatar
    Join Date
    08-22-2012
    Location
    Assam, India
    MS-Off Ver
    Excel 2003, 2007
    Posts
    236

    Re: How to make the 'Total's' row automatically move up and down as list grows and shrinks

    Here you can use Table.
    select your data and Goto insert>Table.
    and from Design Tab in the menu bar select "Total Row".

    Always have a practice to send a sample file for easy understanding and solution
    Rgd
    RT
    If my answer(s) helped you, please add me reputation by click on *

  3. #3
    Registered User
    Join Date
    08-30-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: How to make the 'Total's' row automatically move up and down as list grows and shrinks

    Thanks - but the total function of the table, does not grow or shrink with the list - it stays at the bottom of the largest number of rows that the table could be, and does not move up as a different condition is selected (eg. 'red' instead of 'blue') where there are fewer rows. Instead it leaves that big gap between the last row of the list and the total row - the gap I wanted to avoid.

  4. #4
    Forum Contributor rajeshturaha's Avatar
    Join Date
    08-22-2012
    Location
    Assam, India
    MS-Off Ver
    Excel 2003, 2007
    Posts
    236

    Re: How to make the 'Total's' row automatically move up and down as list grows and shrinks

    Can you plz upload your sample file

  5. #5
    Registered User
    Join Date
    08-30-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: How to make the 'Total's' row automatically move up and down as list grows and shrinks

    Hello - really sorry I can't - it's sensitive data to the company.

    But it really is a simple question. You have a list that's based on a drop-down menu. The list displays 'blue' animals or 'red' animals based on that drop-down menu.

    If you select 'blue', the list is long (because there are many blue animals and the list will now only display the many blue animals), but if you select 'red' - that same list is short (because there are few red animals and that same list will now only display the few red animals).

    However, I need the totals row to always be one row below the bottom row of the list - whether that list is only blue animals or only red animals.

    At the moment, when you select 'red', the totals row appears one below the last row for BLUE animals - which is a long way down from the last row for the RED animals as there are far fewer red animals than blue animals (and, of course, each animal occupies one row of the spreadsheet). So a big gap is left. I need the totals row to move up and down depending on how long the list is - not stay in one place as the list changes length.
    Last edited by cactus_farmer; 01-03-2014 at 08:28 AM.

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to make the 'Total's' row automatically move up and down as list grows and shrinks

    here is one method
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  7. #7
    Registered User
    Join Date
    08-30-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: How to make the 'Total's' row automatically move up and down as list grows and shrinks

    ^^Thanks very much.

    Wasn't able to use your solution exactly, but it pointed me in the right direction, definitely.

+ 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. Make an Unique List and Total
    By turist in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-26-2013, 05:54 PM
  2. Replies: 4
    Last Post: 07-13-2009, 08:27 AM
  3. [SOLVED] Drop down list shrinks as you go down column
    By D.Farns in forum Excel General
    Replies: 7
    Last Post: 06-16-2006, 08:40 AM
  4. Move values to make a list
    By Optitron in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-05-2005, 04:05 PM
  5. [SOLVED] The selected area grows when I click a cell or press keys to move.
    By yendorexcel in forum Excel General
    Replies: 2
    Last Post: 02-14-2005, 03:06 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