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

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.

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.

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.

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.

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.

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!

Originally Posted by Richard Buttrey

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?