+ Reply to Thread
Results 1 to 6 of 6

Autofill next blank row on 2nd sheet based on data in the 1st sheet

  1. #1
    Forum Contributor
    Join Date
    05-04-2015
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2010
    Posts
    129

    Autofill next blank row on 2nd sheet based on data in the 1st sheet

    Hello friends,

    I got this 1st sheet for data entry having 3 columns and i got the 2nd sheet as a result sheet. the 2nd sheet is based on the 1st sheet data. what I want to achieve is to fill up the 2nd sheet with data (based on the 1st sheet) without leaving any blank rows in between data rows. the 1st sheet can have blank rows but the 2nd sheet cannot have any blank rows in between the data entered. I have attached a sample document.

    I would also like to perform this using only formulas instead of VBA unless ofcourse that a VBA is a must for this.

    many thanks in advance

    best wishes to all
    Rizvi
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Autofill next blank row on 2nd sheet based on data in the 1st sheet

    copy paste below on B2 of sheet 2 then hold control and shift then hit enter to make it array formula drag down and drag to right

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Autofill next blank row on 2nd sheet based on data in the 1st sheet

    In B3 of Sheet2 then copied across.

    =IFERROR(INDEX(Sheet1!B$3:B$15,AGGREGATE(15,6,ROW($B$3:$B$15)/(Sheet1!$B$3:$B$15<>""),ROWS($B$3:B3))-ROW($B$3)+1),"")
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Forum Contributor
    Join Date
    05-04-2015
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2010
    Posts
    129

    Re: Autofill next blank row on 2nd sheet based on data in the 1st sheet

    Dear Hemesh and Srinivasamurthy,
    A big thank you to both of you! They both work great!!!

  5. #5
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Autofill next blank row on 2nd sheet based on data in the 1st sheet

    You are welcome !

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Autofill next blank row on 2nd sheet based on data in the 1st sheet

    Welcome .Have a good day.

+ 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. Autofill Data based off current sheet lastrow not first sheet lastrow
    By bcarroll76 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-06-2018, 06:26 PM
  2. Delete worksheets based on blank data on setup sheet
    By vqho90 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-28-2018, 11:26 AM
  3. Replies: 2
    Last Post: 01-23-2016, 01:16 PM
  4. [SOLVED] Copy and paste data from sheet 2 to sheet 1 based on specific criteria on sheet 1
    By VBADUD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-05-2012, 04:18 AM
  5. As user fills out data sheet, then fill out a results sheet based on data sheet entry
    By clemsoncooz in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-13-2012, 03:57 PM
  6. Can't autofill data in workbook from sheet to sheet
    By sn.henderson in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-18-2010, 01:01 PM
  7. Replies: 0
    Last Post: 03-02-2005, 12:06 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