+ Reply to Thread
Results 1 to 11 of 11

Mirror worksheets

  1. #1
    Registered User
    Join Date
    12-25-2015
    Location
    GREECE
    MS-Off Ver
    2007
    Posts
    31

    Mirror worksheets

    Need your expertise.
    I have a sheet1 with a table. Range A1:C20 has products, in the other columns I have some information about those products.
    I have another sheet, which has range a1:c20 copied by sheet1. From D1:J20 I put other info about those products which change frequently.
    Every now and then in Sheet1 changes are made, rows are inserted, deleted or products name change. So until now whenever I make changes I go to the other sheet and make those changes as well.
    What I would like is somehow, don't know if it needs vba or can be done with formula is:
    When changes are made to sheet 1 in a1:c20 I want them to be made to the other sheet as well.
    I've tried the following which I found on the net and works, it does insert/delete the rows and make the changes once refreshed BUT only between a1:c20.
    I want the rows to be inserted/deleted on the whole sheet so that the info I have already put in a product won't be at the wrong row.

    1. Assume data on sheet1 is in range A5:B10. headings are in row 4
    2. Select A4:B10 and assign it a name, say dummy
    3. Select A4:B10 and press Ctrl+T to convert to a Table and save the file
    4. Click on any cell in sheet2 and go to Data > From Other Sources > From Microsoft Query > Excel Files
    5. Navigate to the folder where the file is saved, select the file and click on Next
    6. click on the plus sign in the left hand side box, select the column which you want on sheet2 and click on the > Symbol
    7. Click on Next twice
    8. On the last screen, select "Return Data to MS Excel"
    9. In the Import Data box, select Table and click on Finish

    Of course, I know I could use Access instead of Excel but it'll take a long time for me to set up everything, I wonder if there is a way to do it in Excel since I've been using this workbook many years now.

    Thank you in advance.

  2. #2
    Registered User
    Join Date
    12-31-2015
    Location
    Barreiro, Portugal
    MS-Off Ver
    MS Office 365
    Posts
    84

    Re: Mirror worksheets

    This may be dumb from me, but can't you simply write on the other sheets, since the range is the same, something like: =Sheet1!A1 in cell A1, =Sheet1!B1 in cell B2 and so on?

  3. #3
    Registered User
    Join Date
    12-25-2015
    Location
    GREECE
    MS-Off Ver
    2007
    Posts
    31

    Re: Mirror worksheets

    No, that won't insert/delete rows. It's the same with what I already use.
    But thanks anyway.

  4. #4
    Registered User
    Join Date
    12-31-2015
    Location
    Barreiro, Portugal
    MS-Off Ver
    MS Office 365
    Posts
    84

    Re: Mirror worksheets

    How about you add an extra sheet and write the cell references there (a1, a2...).

    Then, on the other sheets except your primary sheet (sheet1 for this example) and this new one, use =indirect("Sheet1!"&newsheet!a1)

    I've tried and the formula still holds if rows are deleted.

  5. #5
    Registered User
    Join Date
    12-25-2015
    Location
    GREECE
    MS-Off Ver
    2007
    Posts
    31

    Re: Mirror worksheets

    Can't get it to work. I have a feeling what I want can't be done in excel but I'm still trying.

  6. #6
    Registered User
    Join Date
    12-31-2015
    Location
    Barreiro, Portugal
    MS-Off Ver
    MS Office 365
    Posts
    84

    Re: Mirror worksheets

    Can you please see the file attached?

    If your idea is to mimic on the other sheets what happens in the first sheet, then this works, almost 100% certain.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-25-2015
    Location
    GREECE
    MS-Off Ver
    2007
    Posts
    31

    Re: Mirror worksheets

    The thing is:
    In sheet3 in column F I put info, and if I go to sheet1 and insert a row or delete a row that row won't be inserted or deleted in column F in sheet3 and so my info is wrong.

  8. #8
    Registered User
    Join Date
    12-31-2015
    Location
    Barreiro, Portugal
    MS-Off Ver
    MS Office 365
    Posts
    84

    Re: Mirror worksheets

    But what you had in column F is only text info? Or you have formulas?

    If it is only text, then you must use vba, I'll try to write if I can, but please search insert row over multiple sheets, pretty sure somebody had the same problem.

  9. #9
    Registered User
    Join Date
    12-25-2015
    Location
    GREECE
    MS-Off Ver
    2007
    Posts
    31

    Re: Mirror worksheets

    In column F is numbers, not formulas.
    I've already searched that's why I asked here. Many people asked the same thing but I could not figure out the way to adapt it to my file.
    Thank you very much for your help.

  10. #10
    Registered User
    Join Date
    12-31-2015
    Location
    Barreiro, Portugal
    MS-Off Ver
    MS Office 365
    Posts
    84

    Re: Mirror worksheets

    Hi,

    Please see attached.

    I do not know if you are familiar with VBA so some advices:

    Don't forget to enable macros;
    Enable the Developer tab (through the options),
    If you want to see the code, press alt+F11;
    The first module creates rows, by checking which cell you selected in the first sheet, and then inserting that same row in all the sheets
    The second module is the same but for deleting.

    To get this to your workbook, inside the VBA editor, click in insert module and simply copy mine (no more, no less).

    Then add the buttons (developer tab, insert), then after designing it, a window will ask to what macro you want to connect.

    Then change the text and format the buttons (the grey color is like that.

    About the information you will add after inserting rows: you can select all the sheets at once (using shift) and the info you enter in one of the sheets will replicate in all.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    12-25-2015
    Location
    GREECE
    MS-Off Ver
    2007
    Posts
    31

    Re: Mirror worksheets

    Rui, I know VBA, thank you, the macro and the buttons where not what I needed, but don't laugh: Th info about selecting all the sheets and deleting or adding stuff was EXACTLY what I needed.
    Inserting rows and changing the product infos to all the sheets at once was my problem. And this was so simple and I had forgotten it. I was trying to find a code when it was there all along. I don't need to join the tables anymore, since I'll copy the template once and then select all the sheets and make any changes needed and it's done.
    Now I can write a vba code to do things faster like selecting all sheets with a button etc.
    So thank you very much you were indeed very helpful. Sometimes we go to the difficult way, we forget there is an easy one.
    Thank you, thank 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. mirror data between cells in different worksheets
    By dumdum in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 09-19-2016, 02:40 AM
  2. Mirror data between worksheets in same workbook
    By seaneee in forum Excel General
    Replies: 7
    Last Post: 07-03-2013, 07:09 AM
  3. VBA to mirror cells between worksheets conditional to cell color
    By Guy Waggoner in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-25-2013, 11:43 AM
  4. [SOLVED] Mirror certain data from several worksheets into one 'front page' worksheet
    By excel_novice_rob in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-04-2012, 08:51 AM
  5. Mirror certain worksheets to a new workbook
    By Lisea123 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-19-2011, 12:05 PM
  6. mirror ranges of cell between worksheets
    By Dribblermaker in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-27-2009, 08:26 PM
  7. Mirror data across worksheets
    By agm1101 in forum Excel General
    Replies: 0
    Last Post: 03-15-2005, 10:17 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