sort two sheets at once

[LeighAnne]

I have quite a large spreadsheet that i'm having problems sorting. In simple terms:

I have two sheets in a workbook that are set up in the same way, dates are held in column A and projects across row 1. Sheet1 holds 'cash in' data and Sheet2 contains 'cash out' data.

To save the user some time, whenever they add a new project to sheet1 row 1, sheet2 row 1 contains a formula that references sheet1 row 1 to pull the project name through.

I want to sort sheet1 by project (row 1), but that means that the values in sheet2 row 1 also change. This would be fine if the data was sorted also, but of course it isn't, only the titles.

Can anyone give me advice on how I can solve this?

Le

 

[SkipVought]

Hi,

I want to sort sheet1 by project (row 1),

You cant sort by rows -- only by columns.

Please post a consistent example of both sheets of data.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[LeighAnne]

Maybe i'm using the wrong terminology, I'm sorting from left to right by doing the following:

Data > Sort > Press Options button > select sort left to right > Sort by row 1 > press ok

Here is an example of sheet 1:

DATES PROJ CCC PROJ BBB PROJ AAA
01/01/05 10000 12000 13000
02/01/05 95688 4535 2394
03/01/05 0 20434 24050
04/01/05 5395 0 355
etc.

Here is an example of sheet 2:
DATES =Sheet1!B1 =Sheet1!C1 =Sheet1!C2
=Sheet1!A2 -98390 0 0
=Sheet1!A3 0 -349 -2349
=Sheet1!A4 0 0 0
=Sheet1!A5 -3 0 0
etc.

When I sort Sheet1 from left to right, the project names obviously change on Sheet2 because of the formula references, but then the values in Sheet2 are against the incorrect projects.

I hope this makes more sense!

[Ponytails2]
Le

 

[SkipVought]

If I were doing this project, I'd have my source data in NORMALIZED form which would be much easier to analyze and report from.
[tt]
Date ProjName Value ValTyp
1/1/2005 PROJ CCC 10000 CashIn
1/1/2005 PROJ BBB 12000 CashIn
1/1/2005 PROJ AAA 13000 CashIn
2/1/2005 PROJ CCC 95688 CashIn
2/1/2005 PROJ BBB 4535 CashIn
2/1/2005 PROJ AAA 2394 CashIn
3/1/2005 PROJ BBB 20434 CashIn
3/1/2005 PROJ AAA 24050 CashIn
4/1/2005 PROJ CCC 5395 CashIn
4/1/2005 PROJ AAA 355 CashIn
1/1/2005 PROJ CCC -98390 CashOut
2/1/2005 PROJ BBB -349 CashOut
2/1/2005 PROJ AAA -2349 CashOut
4/1/2005 PROJ CCC -3 CashOut
[/tt]
and why are you "sorting" your headings anyhow?


Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[SkipVought]

BTW, reorganizing the data you posted took me about 5 minutes using the technique outlined in NORMALIZE Your Table using the PivotTable Wizard faq68-5287.

I should add that your CURRENT sheet format is a REPORT format that can easily be generated with a few keystrokes using the Pivot Table Wizard.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[LeighAnne]

SkipVought,

I take your point about putting the source data in NORMALIZED form, and will consider this in future projects. However, there are many other sheets referencing the sheets I described previously and it document wasn't set up by myself originally so I don't really want to do a whole re-write.

I eventually got around this by righting code that sorted the sheet2, then sorted sheet1, then re-applied the formulas in row1 of sheet2.

Not the neatest of solutions I admit, but seems to work.

Thanks for all your help again,

LeighAnne