keithkemble
Board Regular
- Joined
- Feb 15, 2002
- Messages
- 160
I have three sheets that interact.
Sheet 2 is the prime entry and contains formula to the right that are horizontally relational to the left.
Sheet 3 contains col a to h that link directly to the same columns in sheet 2, the remainder of the cells are just archived prime entry pasted relevant to the left most cell value.
Sheet 1 links col a to h directly to sheet 2 col a to h. The column to the right are prime entry, which when activated over ride cells in sheet 2. "ap15:bf2000.end (xlup)"
I therefore have a couple of question re sorting.
1
The columns in sheet 1 ( a to H) when sorted do not sort the identical to sheet 2( a to h , prime entry). sheet 1 being formula referenceing sheet 2. WHY?
Data is alfa numeric.
2
I have a range in sheet 2 [ap15:bf2000.end (xlup)] that references the prime entry in sheet 1 [i15:w2000.end(xlup)]
I am able to sort any way I which, however I have one niggly problem. That is the formula in the range [ap15:bf2000.end(xlup)] does not stay in line ( horizontally), but changes with sheet 1.
In Lotus 123 you could sort with "no adjust" can you do that with excel, if so how as I cannot find it in the books.
3. If you cannot how can I sort range [a15:an2000] and [bk15:ih2000] together and at the same time, thereby not changing the reference.
I have tried various concatenates (& ) etc but cannot get the syntax right.
Range(Range("A15"), Range("A2000").EndUp(xlUp).Offset(0,39) & ____
Range(Range("BJ15"), Range("BJ2000").EndUp(xlUp).Offset(0,189))).Select
Can anyone explain the above.
I am sure the logic is right and I have tried sorting both ways. i.e. prime entry up and formula down with out success.
The ultimate goal is that all three sheet sort by column a or b and the prime entry stays in line in all three sheets.
This has stumped me all day and therefore any help would be appreciated.
thanks
Sheet 2 is the prime entry and contains formula to the right that are horizontally relational to the left.
Sheet 3 contains col a to h that link directly to the same columns in sheet 2, the remainder of the cells are just archived prime entry pasted relevant to the left most cell value.
Sheet 1 links col a to h directly to sheet 2 col a to h. The column to the right are prime entry, which when activated over ride cells in sheet 2. "ap15:bf2000.end (xlup)"
I therefore have a couple of question re sorting.
1
The columns in sheet 1 ( a to H) when sorted do not sort the identical to sheet 2( a to h , prime entry). sheet 1 being formula referenceing sheet 2. WHY?
Data is alfa numeric.
2
I have a range in sheet 2 [ap15:bf2000.end (xlup)] that references the prime entry in sheet 1 [i15:w2000.end(xlup)]
I am able to sort any way I which, however I have one niggly problem. That is the formula in the range [ap15:bf2000.end(xlup)] does not stay in line ( horizontally), but changes with sheet 1.
In Lotus 123 you could sort with "no adjust" can you do that with excel, if so how as I cannot find it in the books.
3. If you cannot how can I sort range [a15:an2000] and [bk15:ih2000] together and at the same time, thereby not changing the reference.
I have tried various concatenates (& ) etc but cannot get the syntax right.
Range(Range("A15"), Range("A2000").EndUp(xlUp).Offset(0,39) & ____
Range(Range("BJ15"), Range("BJ2000").EndUp(xlUp).Offset(0,189))).Select
Can anyone explain the above.
I am sure the logic is right and I have tried sorting both ways. i.e. prime entry up and formula down with out success.
The ultimate goal is that all three sheet sort by column a or b and the prime entry stays in line in all three sheets.
This has stumped me all day and therefore any help would be appreciated.
thanks