+ Reply to Thread
Results 1 to 5 of 5

Formula that will allow drag to fill row counts from all other worksheets

  1. #1
    Registered User
    Join Date
    05-20-2016
    Location
    USA
    MS-Off Ver
    2007
    Posts
    5

    Formula that will allow drag to fill row counts from all other worksheets

    Hello all,

    I have the formula =COUNTA(Sheet3!A:A) in a cell to count the amount of rows in column A starting on worksheet 3. I need to be able to use the drag to fill option to populate the remaining cells with data from worksheet 4 and so on. Is there a way to modify this to do so? I've tried messing with nesting COUNTA and INDIRECT but I can't get anything to go.

    After using the drag to fill feature down the column, the formulas should read:

    Please Login or Register  to view this content.
    Thanks for taking a look!

  2. #2
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Formula that will allow drag to fill row counts from all other worksheets

    Hello jts5814,

    Welcome to the forum

    Try this formula then, from row 3 downward:
    =COUNTA(INDIRECT("Sheet"&ROW()&"!A:A"))
    (copy pasta from Ford)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools

    Regards,
    Lem

  3. #3
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    2016
    Posts
    3,836

    Re: Formula that will allow drag to fill row counts from all other worksheets

    Try

    =COUNTA(INDIRECT("Sheet"&ROW(A3)&"!A:A")) and drag down.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  4. #4
    Registered User
    Join Date
    05-20-2016
    Location
    USA
    MS-Off Ver
    2007
    Posts
    5

    Re: Formula that will allow drag to fill row counts from all other worksheets

    Wow both of those work fantastic! Thank you both so so much for the quick replies.

    Sincerely,
    Jake

  5. #5
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    2016
    Posts
    3,836

    Re: Formula that will allow drag to fill row counts from all other worksheets

    Glad to help you Jake.

    Please add reputation by clicking * star icon on your left corner on your screen on those post which helps you

+ 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. How to drag/fill a column with a 3D formula
    By BG23 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-17-2015, 07:03 AM
  2. [SOLVED] Drag fill not recognising formula pattern
    By steveneedshelp in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-12-2015, 09:06 AM
  3. formula same cell different worksheets drag down
    By rmb623 in forum Excel General
    Replies: 9
    Last Post: 07-09-2014, 01:41 PM
  4. [SOLVED] Can I make excel automatically drag-down a formula as you fill in data?
    By Yoshi64 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 01-16-2014, 07:28 PM
  5. Help With Correct Drag Formula In Order To Fill Blank Cells
    By Seán_Ireland in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-19-2013, 05:43 PM
  6. Seconday Worksheet Reference Formula Drag Fill incrementing Issue
    By DavidCharles1224 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-08-2010, 04:05 PM
  7. how to fill formula in any cell and drag it?
    By vumian in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-10-2006, 10:46 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