+ Reply to Thread
Results 1 to 11 of 11

Set up Excel to display the same columns in multiple worksheets

  1. #1
    Registered User
    Join Date
    03-04-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    48

    Question Set up Excel to display the same columns in multiple worksheets

    Putting together an excel file to be used as a tracker, where the first sheet will contain a list of names and a summary of the occurrences recorded on the other sheets, and a sheet for each month of the year for recording occurrences as the year goes on. How do I get the monthly sheets to reflect the master list of names? Right now if I need to add another name, inserting a row on the master list throws off the rest of the book that same row doesn't insert through the workbook taking everything out of alignment unless I go into each sheet individually and add a row and insert the formula to copy off the master list which is time consuming

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Set up Excel to display the same columns in multiple worksheets

    Have you looked at PIVOT TABLE? It could save you lots oif issues.
    You would only need 2 sheets. One to record your occurences and the other would be the pivot table.
    What are the occurences you want to track?
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

  3. #3
    Registered User
    Join Date
    03-04-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    48
    Actually, no the thought of a pivot table hadn't occurred to me. Not sure it's the solution. Putting together a workbook to track attendance occurrences for a list of staff

  4. #4
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Set up Excel to display the same columns in multiple worksheets

    That means you would have those column in the occurences sheet:
    Name
    Date
    In
    Out
    Can you complete this list?

  5. #5
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Set up Excel to display the same columns in multiple worksheets

    attaching a sample workbook with all the fields you need would help us.

  6. #6
    Registered User
    Join Date
    03-04-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    48

    Re: Set up Excel to display the same columns in multiple worksheets

    I've attached a sample of the worksheet, had to strip it down to make it small enough for upload. There would normally be a
    sheet for each month of the year, the "attendance tracker" sheet would show the totals as the year went on and the "employee list" sheet would be the master list of names.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-04-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    48

    Re: Set up Excel to display the same columns in multiple worksheets

    Any suggestions out there?
    Last edited by jstn; 01-14-2014 at 11:52 AM.

  8. #8
    Registered User
    Join Date
    03-04-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    48

    Re: Set up Excel to display the same columns in multiple worksheets



    I'm at a loss as to how to get the columns and rows to change throughout the work book any assistance would be great

  9. #9
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Set up Excel to display the same columns in multiple worksheets

    I did not know if you were open to a new approach but here is a suggestion.
    In the first sheet, you'll have a list of employees along with a list of department and a list of cause of occurrences.

    In the second sheet, you'll have you month or year resumé. If you enter a date in cell C1 (like 2014/01, just the year and the month), the table will show you the data for that particular month. If you erase the value in cell C1, you'll see the word "ANNUAL" appears in cell B1 telling you that the table now shows you the annual results. DO NOT ERASE CELL B1 as it is used as a flag in the formulas.

    In the third sheet, you'll have all the occurrences in one sheet. You select the employee's name from a validation list then you enter the date of the occurrence and its legend.
    That's all.

    The list of employees to maintain is in your Summary sheet.
    Hope you'll like this.
    If not,it could be really hard to do this with a macro.
    Attached Files Attached Files

  10. #10
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,027

    Re: Set up Excel to display the same columns in multiple worksheets

    Hi,

    I like p24leclerc's worksheet and Pivot Table and have added another Pivot from the same data. Time to learn Pivot Tables?? They are GREAT!!
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  11. #11
    Registered User
    Join Date
    03-04-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    48

    Re: Set up Excel to display the same columns in multiple worksheets

    Thanks guys, I haven't done much with pivot tables to date.. I'll take your examples and give it a shot though. If I fail miserable, I'll be back lol Thanks again!

+ 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. VBA Code to Compare Two Worksheets and display the columns with changes.
    By mrbhide in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-05-2013, 12:33 AM
  2. Copying and combining Excel columns - Multiple Worksheets
    By Bewildered101 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-02-2012, 10:24 AM
  3. display multiple values from other worksheets
    By AKwarehouse in forum Excel General
    Replies: 1
    Last Post: 03-15-2011, 06:31 PM
  4. Display one list on multiple worksheets
    By jakebryant in forum Excel General
    Replies: 6
    Last Post: 02-25-2011, 03:16 AM
  5. Replies: 7
    Last Post: 03-08-2010, 06:36 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