Re Formula linking sheets

[gelder]

This may be an easily solved question, but here goes.

I have my main list in sheet 1, with a sub-list on sheet2. I would like to have sheet 2 change automatically as I rearrange sheet 1.

Ex. Original is Sheet 1, Cell A5. Sheet 2, A1 is linked to this.
When I alphabetize Sheet 1, A5 moves to A7.
Sheet 2 A1 is still linked to Sheet 1 A5. I need it to be now linked to Sheet 1 A7.

This is the formula I have used: =Sheet1!A5. I would like it to automaticall change to: =Sheet1!A7 (as necessary). But this does not change as I change sheet 1. Please help!

 

[Greg Truby]

Genoa,

My approach was two-stepped. Step 1 was to take the dependent cells and find their precedents, then write the address of the dependents as comments into the precedents.

Then do whatever sort you had in mind.

Step 2 was to then run through all the precedents, use the comments to find their dependents and then re-write formulas in the dependent cells that restores the connections.

However I find myself thinking that Keith & Erik's approach might be more robust by cutting and pasting and letting Excel track all dependencies (especially if there were multiple sheets depending on sheet 1). Don't think I'll muddy the waters by posting my approach...

Keith, FWIW there are .Dependents and .Precendents properties for range objects. Unfortunately they only return Dependents/Precedents on the same worksheet. So for this particular problem, they are not much help.

 

[gelder]

Ok, thanks.

Just to check, dependents are the cells I will be typing the formulas into and precedents are the cells that the formulas refer to?

Still trying to figure out Keith and Erik's idea. Not all that Excel literate, if you haven't seen that already!

 

[erik.van.geit]

Gelder,

Just to check, dependents are the cells I will be typing the formulas into and precedents are the cells that the formulas refer to?

Yes, that's true!

Step through the code with some testdata and see what happens, especially when sorting starts.
In the provided code the selection is sorted, based on the active cell (=sorting key).

Give your comments: is it OK for your situation? (especially the fact that the code is writing to your sheet in the columns next to the data: perhaps it should write to another location, f.i. inserted column(s) )

kind regards,
Erik

 

[gelder]

I've fiddled with it a bit, but its going to take me awhile to figure out how to get it going. Also, I won't have much time to play with it for the next few days. I'll let you know as soon as I do! Meanwhile, is this the "easiest" explanation you can give me? Thanks so much for what you have given! Its so much more than what I started with.

 

[Corso]

i know this is an old post but maybe there was some new progress, ive tried the codes that have been listed in this forum. none of them seem to work properly it will sort one colum fine but the next one that i wanted to sort by wont change. any more information if anyone has would be much appriciated.

 

[erik.van.geit]

i know this is an old post but maybe there was some new progress, ive tried the codes that have been listed in this forum. none of them seem to work properly it will sort one colum fine but the next one that i wanted to sort by wont change. any more information if anyone has would be much appriciated.

Hi,

to my sense, it would be better to start a new thread
(you could post the link here if you want)
explain your specific problem with a little example

none of them seem to work properly

I think they work properly for the purpose they were designed to

if you ask a car to fly it "won't work properly"

IMPORTANT NOTE:
if you can avoid the need of this kinda operations, you would be better of, so think about your layout first ...

kind regards,
Erik