+ Reply to Thread
Results 1 to 2 of 2

Permemently group sheets so new rows appear

  1. #1
    Registered User
    Join Date
    01-27-2019
    Location
    London
    MS-Off Ver
    2013
    Posts
    1

    Unhappy Permemently group sheets so new rows appear

    Hello. Please forgive me being a novice.

    I have a spreadsheet where sheet 1 is a master list of staff names and their current posting. The columns to the right of this contain details of planned or completed training. The list of names also serves as a master list of staff posted to my area.

    I need to create 2 more sheets which have the names list from sheet 1 with the columns to the right showing different data about them. I want sheet 1 to be the master list of names which is the only one that is edited.

    I have created the sheets and used the simple formula =('sheet1'!A1) which works fine - the list of names is there and changes across the sheets when changed on sheet 1. My problem is, when a new row (for a new member of staff's arrival) is added to sheet 1, it does not insert a new row in the other 2 sheets. Likewise when a row is deleted from sheet 1 it remains on the others. The formula still remains correct but the new name isn't on sheets 2 and 3. I know about grouping sheets from the tabs however once another none-grouped tab is selected, the grouping stops. The team that will be using this are not computer literate and therefor telling them to group sheets before adding a row is not an option.

    Is there a way of making all changes (both changes to names and new rows inserted / old rows deleted) to sheet 1 happen across sheets 2 and 3 in real time? I've played with macros after looking on other posts and they are way above my level.

    Thank you in advance

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Permemently group sheets so new rows appear

    Hello, this approach won't fly.

    You can use formulas to pull the list of names from the first sheet, but if you have data in the columns to the right, then inserting new names into the source data will wreak havoc. Example: Your source data has

    Bill
    Eve
    George

    Your other sheet uses formulas to pull the three names and you put their shoe size next to their names

    Bill 12
    Eve 8
    George 11

    Now you insert a new row in the first sheet, between Eve and George. Your list on the first sheet now looks like

    Bill
    Eve
    Frances
    George

    And on the second sheet you will see.

    Bill 12
    Eve 8
    Frances 11
    George

    If a formula dynamically updates the names, the data that belongs to a name won't stick with the name. That's something you need to understand.

    You'd be much better off entering ALL data into the first sheet. Then you can use a report on the second, third, etc, sheet to pull just some data (some columns) onto that sheet for easier reference.

+ 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. [SOLVED] How to group and sub group rows
    By Excelforum*ser_mH7 in forum Excel General
    Replies: 3
    Last Post: 02-24-2018, 10:42 AM
  2. Replies: 14
    Last Post: 01-12-2017, 01:41 PM
  3. Replies: 1
    Last Post: 02-10-2014, 10:26 PM
  4. [SOLVED] Need VBA code to count number of rows & split into group of 7 rows in same sheet
    By amy_d2 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-05-2013, 11:03 AM
  5. [SOLVED] Macro to group and un group rows?
    By gjjh25 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-06-2013, 05:13 PM
  6. Replies: 3
    Last Post: 12-05-2012, 01:41 AM
  7. Replies: 0
    Last Post: 07-15-2009, 01:17 PM

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