+ Reply to Thread
Results 1 to 10 of 10

Copy/Paste sheet problem - details in post.

  1. #1
    Registered User
    Join Date
    09-08-2019
    Location
    Sweden
    MS-Off Ver
    Office 365
    Posts
    6

    Copy/Paste sheet problem - details in post.

    Hey experts,

    This is probably not a complicated question but in my beginner phase I am unable to describe it uncomplicated, so bare with me


    ------------------------------------------------------------------

    I need to create an excel document based on a yearly basis. Meaning a sheet called 'January' with a 'January Total' and so on with all months.




    The information in the 'January Total' is obviously based on the data entered in the 'January' sheet. I use numbers and drop down menues to add it. The 'Customer' has 28 choices and the 'Job' has 11 choices.



    Now; in the 'Total' sheet I have 28 seperat 'Customer' boxes calculating the info added in the 'January' sheet and in each of those 28 boxes adding the 11 seperate 'Jobs'.




    Each cells code looks like this:



    ------------------------------------------------------------------

    Now to my problem:

    If I copy 'Januray Total', to get all those boxes with all those codes, to make a 'February Total' - how do I make the codes in all those seperate cells to change to "Februari" as it should be gathering info from the sheet called 'Februari' and end up in 'Februari Total'.

    As of now I have managed create an exact copy, with all formates and formulas. But I am left with changing the 'Januari' to 'Februari' information in the seperat cells coding window. Or else all cell codes in the newly created copy sheet will change month after what I rename the original sheet name to.

    Does any of this make sense to you? Cause I feel I am struggling just describing the problem. Please let me know what you need claried in able to help me.

    Thank you
    Last edited by Zajo; 09-08-2019 at 11:13 AM. Reason: Removed attachments

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    30,900

    Re: Copy/Paste sheet problem - details in post.

    Welcome to the forum.

    Will you please attach a small sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment (it hasn't worked for years, and despite our repeatedly asking the technical team who own the forum to fix it, they can't be bothered to do so), so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    09-08-2019
    Location
    Sweden
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Copy/Paste sheet problem - details in post.

    Hey Ali,

    I was hoping there would be someone able to explain how I do it, not doing it for me

    So if I understand you correct, you want me to redo my entire excelsheet (as is) but renaming the sensitive date?

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    30,900

    Re: Copy/Paste sheet problem - details in post.

    You asked for help. You asked what else we needed. I asked for a sample workbook. You now say you want a solution without having to supply anything else. Go figure ...

    Go and read up on the INDIRECT function and then apply it to your spreadsheet. If (when?) you get stuck, come back and provide the workbook I requested.
    Last edited by AliGW; 09-08-2019 at 07:15 AM.

  5. #5
    Registered User
    Join Date
    09-08-2019
    Location
    Sweden
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Copy/Paste sheet problem - details in post.

    Ali, I am humble to the fact most of the members on this forum are extremly more knowledgable than me in how to work in excel. I did my best to explain my problem.
    Based on your response I added a follow up question to make sure I understood what you wanted.

    I did not say I wanted a solution without supplying anything, I stated I was after an explained solution, not someone doing the actual work for me.
    Your snarky remark was completely uncalled for.
    Last edited by AliGW; 09-08-2019 at 07:33 AM. Reason: Please don't quote unnecessarily!

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    30,900

    Re: Copy/Paste sheet problem - details in post.

    I gave really comprehensive details in my initial post about how to prepare a sample workbook.

    Your snarky remark was completely uncalled for.
    It wasn't 'snarky'. You asked for help and also what else we needed to help. I asked for a workbook. Happy to help when you have provided this. I am not offering to 'do it for you' - I just need to see the data in context to offer more than vague, generic advice. It's up to you.
    Last edited by AliGW; 09-08-2019 at 07:38 AM.

  7. #7
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,506

    Re: Copy/Paste sheet problem - details in post.

    Whilst this is not a direct answer to your question I offer it as a bit of advice.

    I've lost track of the number of people who set up individual sheets, all exactly the same layout for stuff like months, weeks, departments, staff names, product codes,...I guess you get the picture.

    What I believe you have here is essentially a database analysis request and I believe you are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

    A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. Yours exhibits all those features.


    You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
    You will also throw open the whole wonderful world of the powerful Pivot table functionality.

    So before you get too far with this I'd create a single sheet database that contains the columns you currently have in your monthly sheets but with an additional column that holds a date for the month. When you've done that, and for the moment it can be a small subset of say three months, manuallyadd the results you expect to see for a particular request indicating which cells/ranges comprise the results and upload the workbook.

    We'll then be able to offer perhaps a more elegant solution - assuming you are happy to reconsider your data layout.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  8. #8
    Registered User
    Join Date
    09-08-2019
    Location
    Sweden
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Copy/Paste sheet problem - details in post.

    Thank you! I think I understand what you mean.
    I will try and wrap my head around it and see if I can create a 'simpler' version and then from that version find an easier way to get the final report to work. By doing it correct from start!
    Last edited by AliGW; 09-08-2019 at 10:19 AM. Reason: Please don't quote unnecessarily!

  9. #9
    Registered User
    Join Date
    09-08-2019
    Location
    Sweden
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Copy/Paste sheet problem - details in post.

    Quote Originally Posted by Richard Buttrey View Post
    When you've done that, and for the moment it can be a small subset of say three months, manuallyadd the results you expect to see for a particular request indicating which cells/ranges comprise the results and upload the workbook.

    We'll then be able to offer perhaps a more elegant solution - assuming you are happy to reconsider your data layout.
    Is this what you ment?
    Last edited by Zajo; 09-08-2019 at 11:12 AM.

  10. #10
    Registered User
    Join Date
    09-08-2019
    Location
    Sweden
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Copy/Paste sheet problem - details in post.

    Problem solved! Thank you all!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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