Sort referenced sheets together!

[Addy]

I would like to know that how can I sort one sheet and accordingly get other
sheets to sort with it.

Problem being faced:

USing 8 Sheets which referenced to each of the previous sheet based on the
value of one of the columns in that previous sheet,

ex: This is the formula I am using in one of the columns of sheet 2
"=IF('Cutting or Slitting'!N2=0,0,'Cutting or Slitting'!J2)"

this sheet is named RG and the next sheet is referenced to RG as
"=IF(RG!F2=0,0,RG!A2)"

Now the problem is that I need to sort the data in shee1 (Cutting or
Slitting) but whenever I do that the correspnding reference in the next sheet
changes too and whcih results in changing of orientation of the rows in the
sheet which was not sorted.

In a nutshell sorting the main sheet also sorts the referenced cells of a
particular sheet but does not sort it according to the rows of that sheet.

Hope sbd undersands my problem and replies soon.

Thanks anyway

 

[vezerid]

Addy,
Your problem could be solved provided the first column in your original
sheet is a KEY column, i.e. its values are not repeated and each value
uniquely identifies the record. In this case I assume that you are
using the same keys in the other sheets as well, in the first column in
every instance.

If this is the case, then the expression:

'Cutting or Slitting'!N2

can be replaced by

VLOOKUP(A2, 'Cutting or Slitting'!A:Z, 14, 0)

because N is the 14th column.

This way, your first formula would become,

=IF(VLOOKUP(A2, 'Cutting or Slitting'!A:Z, 14, 0)=0,0,VLOOKUP(A2,
'Cutting or Slitting'!A:Z, 10, 0))

With this technique, the other tables will not be sorted automatically,
but they will show the correct amount in every case, regardless of how
the original table is sorted.

Does this help?

Kostis Vezerides

 

[Addy]

Well thanks alot for the solution
but I have one more question that while using the formula specified by you
that is:

=IF(VLOOKUP(A2, 'Cutting or Slitting'!A:Z, 14, 0)=0,0,VLOOKUP(A2,
'Cutting or Slitting'!A:Z, 10, 0))

I was getting a circular reference but when I changed A2 to 1 then its
working fine
Do u suspect any problem with that if so please let me know.

Another thing if there is no problem suspected with the changed formula that
I am usign then how can I auto fill the formula in 1000 Rows with the 1
increasing in steps of 1 till 999.

Will using this formula mean that the formula in A2 row of the RG sheet will
always refer to the data corresponding to S.No.1 in sheet 'Cutting or
Slitting' even if I resort 'Cutting or Slitting'.

Does the S.no column for VLOOKUP need to be sorted in ascending order or
they can work without the sorting

 

[Addy]

Well thanks VezerID,

I think the first time I went in thewrong direction but now the solution
told by you is working just fine so please ignore the post just after your
reply

Thanks alot