Adjusting formulas after sorting

[Flupke]

In an Excel file, I have 13 tabpages, for each month one tabpage + one tabpage for the totals.
On each tabpage, I have a list of people, sorted by number.
On the total page, I have formula's that add the figures on the monthly pages for each person.
When I sort the people on a month-page in alphabetical order, the formula's on the total page do not adjust correctly. They remain unchanged.
How can I make them adjust, so that they reflect the new cells on the alphabetical sorted page(s)?

Many thanks and greetings from Brugge (Bruges - Belgium),

Michel

 

[xlbo]

To be honest, your best bet is to consolidate your data and add a tag for the month that it represents. You have just hit one of the major issues of storing your data in seperate places. If you do this, reporting will become much easier

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[combo]

For the same data structure, you can consolidate your data with pivot table (consolidation option in data location).

combo

 

[Flupke]

Thank you for your help. Can you please tell me how I can add a tag and how I can refer to that tag in a formula on another tabpage?

Many thanks,

Michel

 

[xlbo]

The "tag" is simply another column of information that identifies the month you are interested in so after you paste your data into your consolidated sheet, just add a month indicator to that data. Keep going until you have all your data in 1 sheet. After that, you have many options for your reporting - which one you use will be dependant on your specific requirements but I would suggest that pivot tables will be your friend !

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question