+ Reply to Thread
Results 1 to 2 of 2

auto grow rows in sheet 2 when new data is added in sheet 1

  1. #1
    Registered User
    Join Date
    07-17-2013
    Location
    washdc
    MS-Off Ver
    Excel 2010
    Posts
    1

    auto grow rows in sheet 2 when new data is added in sheet 1

    in sheet 1 i have data like name and phone number a1: sally b1:555-1212

    i put that in sheet 2 easy enough by clicking sheet 2 a1 then =sheet1a1 got it.....

    ok so when i add a new name in a new row, "a2: janet" i want excel to know that it has to put that name sheet 2 as well. (i realize i could drag and copy but i dont want to drag and copy like 1000 rows just awaiting for data to be filled in

    What is the formula that says "if data is put into sheet 1 new row, then add this to sheet 2 new row also...."

  2. #2
    Forum Contributor
    Join Date
    07-16-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2013
    Posts
    176

    Re: auto grow rows in sheet 2 when new data is added in sheet 1

    the only way i'm aware of is to put sheet1 on a pivot table. You'll need to make the range for your pivot table dynamic by using an offset function and define the name. Instructions to define name:
    1. click on formulas
    2. Name manger (new) give it a name..(range - for example)
    3. paste =OFFSET($A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
    note: This formula assume your heading is on row 1 and the data in column A do not skip rows.

    to insert pivot table
    1. click on insert
    2. for "select a table or range" - enter the name you created. "range" was the example used
    3. click on the pivot table then click on the design tab
    4. click on report layout and change it to "show in tabular form"
    It will essentially recreate the list for 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. Replies: 4
    Last Post: 05-02-2013, 04:39 PM
  2. [SOLVED] Formula updating when rows are added on other sheet
    By D-smoke in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-21-2013, 07:30 AM
  3. Creating auto populating Summary sheet as new sheets are added to work book
    By Phraedrique in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-18-2012, 05:01 PM
  4. Retry: Make a Data Validation List On Another Sheet Grow With New Entries?
    By leaning in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-18-2010, 07:01 AM
  5. Macros to auto-run formulas when a new sheet is added
    By azkhan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-07-2010, 01:08 AM

Tags for this Thread

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