Referencing group summary and sorting

[cshort]

I'm have a problem sorting grouped data. On worksheet 2, I have direct references to cells in worksheet 1. However, when I sort worksheet 1 a new way, it rearranges the numbers in worksheet 2. Is there a way to prevent this from happening and to keep the numbers in worksheet 2 stationary? I've attached an example.

Thanks for any help!

Carl

[tlafferty]

Try the attached.

[cshort]

You are awesome!! Thank you so much!

[cshort]

I know this wasn't in the original problem...but now when I try sorting Sheet2 the results get mixed up. Is it possible to keep them the same, too? The formulas are staying the same but the numbers are changing. I can't figure it out.

Thanks!

[tlafferty]

Hmmmm....
Not sure why that is, but why over complicate it with a formula when it's completely unnecessary? Here's all you need to do:
1) collapse your outline to show level 2
2) select your data
3) press the F5 key on your keyboard
4) click special|visible cells only
5) copy and paste in a new worksheet

Results will sort properly.

If you just select and copy then paste without selecting visible cells only, you will copy the hidden rows from the outline as well.

Cheers!
Tom

[cshort]

Thanks for your help!