+ Reply to Thread
Results 1 to 4 of 4

Automatically adjust the number of rows in a Table based on the CountIf data using formula

  1. #1
    Registered User
    Join Date
    11-16-2017
    Location
    MANILA
    MS-Off Ver
    365
    Posts
    11

    Automatically adjust the number of rows in a Table based on the CountIf data using formula

    Hello to all, newbie here.

    I just want to ask if excel can dynamically/automatically adjust the number of rows of a table based on the countif data i collected from other sheet?
    By default I have 5 rows (and 10 columns) on my Table with all the necessary formulas in each row. Everyday, we are expecting more than 100 entries in the table so I want the table to auto adjust based on the actual count.

    Any help/ideas will be appreciated. tia

  2. #2
    Forum Contributor shivya's Avatar
    Join Date
    08-19-2017
    Location
    Delhi, India
    MS-Off Ver
    2013
    Posts
    240

    Re: Automatically adjust the number of rows in a Table based on the CountIf data using for

    Welcome to the forum.

    Yes this is possible, You can use Offset formula for the same.

    =OFFSET($A$1,0,0,COUNTA($A$1:$A$27),COUNTA($A$1:$C$1))

    Now, Play with range according to your data.
    If your table starts from A1 then you don't need to change the range.
    To attach worksheet Go advanced -> Manage attachments -> Choose file -> Upload
    Don't forget to Mention your desired result in the sheet..
    There should be sample data only, a lot of data creates confusion.

    Thanks & Regards

    Shivya

    http://excelvbatipsforbeginners.blogspot.in/

  3. #3
    Valued Forum Contributor
    Join Date
    04-27-2015
    Location
    Abu Dhabi, U.A.E
    MS-Off Ver
    Office 365 | 2016
    Posts
    696

    Re: Automatically adjust the number of rows in a Table based on the CountIf data using for

    Do conditional formatting.
    Have a look at the attachment.

    See the rule created in conditional formatting.
    Go to Home Tab > Conditional Formatting > Manage Rules
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    11-16-2017
    Location
    MANILA
    MS-Off Ver
    365
    Posts
    11

    Re: Automatically adjust the number of rows in a Table based on the CountIf data using for

    Hi Shivya,
    I'm talking about the format as table, the table you created will be in the Name Manager (Under formula Ribbon), but the formula entry is grayed out. I cannot paste the offset formula from there.
    I've attached a screenScreen Shot 2017-11-23 at 8.52.42 AM.jpgshot.

+ 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. Replies: 2
    Last Post: 01-25-2017, 01:11 PM
  2. Automatically adjust Bar Graph when Pivot Table changes
    By FranktheBank in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 05-31-2016, 02:53 PM
  3. [SOLVED] Formula to automatically adjust lunch hour based on start time
    By Zyphon in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-26-2015, 07:23 AM
  4. [SOLVED] Way to have Array Formula automatically adjust when rows added
    By bdouglas1011 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-28-2014, 04:00 PM
  5. Replies: 2
    Last Post: 10-17-2013, 02:44 PM
  6. Can a formula be used to automatically adjust data used on an Axis?
    By Grimace in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-06-2012, 01:21 AM
  7. Replies: 3
    Last Post: 04-01-2010, 07:49 PM

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