Link Cells from one sheet to another then Sorting Data

Zeidinho · May 21st, 2002, 04:37 AM

Ok, this sounds like a simple question, but it seems quite difficult to solve. I have copied cells from one sheet to another using the = sign. My goal was to consolidate all my data in one sheet. However, when i try to create stats for my consolidated sheet, for example creating a currency weighting (%), everything seems fine up until i decide to sort the data out say by alphabetical. Once i do the sorting which is quite essential, all the links get muddled and consequently all my stats are wrong. Is there anyway i can get around this?? I would really appreciate it. Thanx

Lee Attlesey · May 21st, 2002, 05:03 AM

Hi Zeidinho

I don't know how many sheets you are combining but (not being to advanced) could you duplicate your worksheet containing the links, copy your linked cells then do a paste special as a value which will remove all your links.

You can sort the new worksheet.

Zeidinho · May 21st, 2002, 05:32 AM

Thanx Lee. Its not a bad idea, but i do need the links, i'll try to explain why. I have several accounts which i have to update on a regular basis, each accoutn contains a list of bonds, equities, deposits etc...
Now the reason why i have to make one sheet that consolidates all the accounts, is so that my boss can take a look at all his assets on one page. The links are important, becasue once i change anything in the seperate accoutns, they will auatomatically be updated on the consolidated sheet. However, when i try to make a stat for example currency weighting using the consol sheet like i mentioned in my last post, everything is fine, but sometimes i need to sort data by maturity date (i.e date when certain assets mature like fixed deposits) and if u do so, the links are still fine, but the formula for my statistics gets muddled, and its starts to represent different cells. the only way i know that i can get around this prob is by doing the stats on all the seperate accounts as i dont sort them, which will be tedious. Any other ideas? thanx

Lee Attlesey · May 21st, 2002, 06:13 AM

Have you tried the Pivot Table wizard?

In Excel 97 look in the main toolbar under Data, PivotTable Report...

I don't know if it will review linked cells but it may be worth a go!

Let me know how you get on. If you havn't used pivot tables before send me an e-mail and I'll send you a word doc I put togther for my place of work about them.

Gary_E · May 21st, 2002, 01:41 PM

Maybe I'm just old fashion, but why not convert links to absolute reference & sort.

The ASAP add-in makes converting fromto absolute referencing pretty easy

http://www.asap-utilities.com/

Drawback - only does one sheet @ a time

_________________
Thxs for tips - this place is a great source
tips & tricks

G_L

[ This Message was edited by: Gary_E on 2002-05-21 12:44 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 21st, 2002, 04:37 AM	#1
Zeidinho New Member Join Date: May 2002 Location: Zeid Posts: 9	Ok, this sounds like a simple question, but it seems quite difficult to solve. I have copied cells from one sheet to another using the = sign. My goal was to consolidate all my data in one sheet. However, when i try to create stats for my consolidated sheet, for example creating a currency weighting (%), everything seems fine up until i decide to sort the data out say by alphabetical. Once i do the sorting which is quite essential, all the links get muddled and consequently all my stats are wrong. Is there anyway i can get around this?? I would really appreciate it. Thanx

May 21st, 2002, 05:03 AM	#2
Lee Attlesey New Member Join Date: May 2002 Location: Cambridge, UK Posts: 22	Hi Zeidinho I don't know how many sheets you are combining but (not being to advanced) could you duplicate your worksheet containing the links, copy your linked cells then do a paste special as a value which will remove all your links. You can sort the new worksheet.

May 21st, 2002, 05:32 AM	#3
Zeidinho New Member Join Date: May 2002 Location: Zeid Posts: 9	Thanx Lee. Its not a bad idea, but i do need the links, i'll try to explain why. I have several accounts which i have to update on a regular basis, each accoutn contains a list of bonds, equities, deposits etc... Now the reason why i have to make one sheet that consolidates all the accounts, is so that my boss can take a look at all his assets on one page. The links are important, becasue once i change anything in the seperate accoutns, they will auatomatically be updated on the consolidated sheet. However, when i try to make a stat for example currency weighting using the consol sheet like i mentioned in my last post, everything is fine, but sometimes i need to sort data by maturity date (i.e date when certain assets mature like fixed deposits) and if u do so, the links are still fine, but the formula for my statistics gets muddled, and its starts to represent different cells. the only way i know that i can get around this prob is by doing the stats on all the seperate accounts as i dont sort them, which will be tedious. Any other ideas? thanx

May 21st, 2002, 06:13 AM	#4
Lee Attlesey New Member Join Date: May 2002 Location: Cambridge, UK Posts: 22	Have you tried the Pivot Table wizard? In Excel 97 look in the main toolbar under Data, PivotTable Report... I don't know if it will review linked cells but it may be worth a go! Let me know how you get on. If you havn't used pivot tables before send me an e-mail and I'll send you a word doc I put togther for my place of work about them.

May 21st, 2002, 01:41 PM	#5
Gary_E Board Regular Join Date: Feb 2002 Location: Charlotte, NC USA Posts: 108	Maybe I'm just old fashion, but why not convert links to absolute reference & sort. The ASAP add-in makes converting fromto absolute referencing pretty easy http://www.asap-utilities.com/ Drawback - only does one sheet @ a time _________________ Thxs for tips - this place is a great source tips & tricks G_L [ This Message was edited by: Gary_E on 2002-05-21 12:44 ]