Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 "vezerid" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How do I data sort multiple sheets in Excel that are linked with i | Excel Worksheet Functions | |||
how can I sort sheets within a workbook in excel | Excel Worksheet Functions | |||
dynamic sort macro across 3 linked sheets | Excel Discussion (Misc queries) | |||
a-z sort sheets | Excel Discussion (Misc queries) | |||
How do I sort sheets in an excell spreadsheet? | Excel Worksheet Functions |