Automaticly copying data between sheets

[DaanVom]

Hello everybody,

I am new to this forum and made my account in the hope that someone could help me with my problem. I have been using Excel quite some time and thought to know alot until I spend many hours trying to find a solution for my current problem. I am trying to achieve the following:

• Document contains 9 sheets
• Data from column A-K needs to be same in each document
• Different sheets are product category, where after column K the amount each client spends on each category is filled in
• Each sheet makes a summary of the results of that sheets and summarises that on client level on original sheet

This all works. The problem starts when I add a different client and then sort it in the first sheet. After doing this all the data from column a-k gets sorted as wanted, just after column k the data does not get sorted resulting in the fact that all revenues are listed at the wrong client.

I have been trying for hours to resolve this issue and hope that someone here will be able to help me.

Thanks in advanced.

Daan

[Pete_UK]

I think you need to attach a sample Excel workbook, so we can see how your summary tables are derived - the FAQ describes how to attach a file to one of your posts.

Pete

[DaanVom]

Hi Pete,

sorry for the late reply. Things have been so busy.

Attached is the base document. I made a couple of test clients. The problem start when I start adding company names that should fit in between the existing once. When I sort them all the revenues are incorrect etc.

I hope that you are able to help me with this.

Best Regards,

Daan Example..xlsx

[Pete_UK]

First of all, you should get rid of the circular reference errors. These occur on the Klanten sheet in column Y, round about rows 330 to 340 and the formulae refer to the same cell, so you should just remove these.

To get around the other problem, you will have to change all the formulae in the sheets that refer to the Klanten sheet, but you can do this using Find & Replace (CTRL-H) multiple times. For example, select columns A to J in sheet Online, then CTRL-H, and:

Find What: !A
Replace with: !A$
Click Replace All

then change the A to B and repeat, and continue with C, D, E, F, G, H, I and N in turn. Then do that for all the other sheets (or you could copy that block of formulae from the sheet you have changed to the other sheets). Now if you add a new line to the Klanten sheet and then sort the data, it will not affect the other sheets - you will need to sort those sheets separately.

Note, however, that you should not insert a new row into the Klanten sheet. If you insert a new row 7, for example, the formulae in the other sheets will automatically adjust, and there will be no formula which refers to row 7.

Hope this helps.

Pete

[DaanVom]

Hi Pete,

Thanks for the help. It worked for the half. There are still a couple of issues with which I hope you can help me.

• When I use Ctrl - h I am able to see the replace menu, but when I enter what you suggested I get an error. I manual inserterted the formula in FB
• The klanten file does sort the numbers, the FB sheet automatically sorts the client names but the numbers that used to be attached to that client are now listed behind another client

Attached is the updated file. I made the clients in a different order, if you sort the clients you will see what I mean. I hope you can help me with this. It is greatly appreciated.

Best Regards,

Daan van OmmenExample..xlsx