Sorting data on one sheet, linked to another, but staying the same
A colleague of mine is putting together a large spreadsheet of customer
parts. "Worksheet A" calculates a price for each part based on many variables. "Worksheet B" contains a list of some of these parts, which make up asseblies. Two cells from "Worksheet A" are linked to "Worksheet B". If "Worksheet A" is sorted the data in "Worksheet B" is no longer correct. Is there a way to sort "Worksheet A" and the data in "Worksheet B" stay the same or follow the data? The link has to be maintained between the two because the data in "Worksheet A" changes frequently and it needs to adjuston "Worksheet B" Thanks |
Sorting data on one sheet, linked to another, but staying the same
Using an Index and Match would probably take care of the problem that you are
having. =INDEX('Worksheet A'!$B$5:$D$22,MATCH(B176,$B$5:$B$22,0),3) You index the range that you want to look through ($B$5:$D$22), Match a specific variable (B176) througout another range ($B$5:$B$22) with an exact match (,0) and the number of columns (,3)from the start of the index range. You might need to select a criteria or variable that remains consistent between the ranges as the Match criteria. When the sort occured on the second worksheet, it wouldn't matter what order the information ended up in as you are Matching the criteria no matter where it occurs. "Ryled Up" wrote: A colleague of mine is putting together a large spreadsheet of customer parts. "Worksheet A" calculates a price for each part based on many variables. "Worksheet B" contains a list of some of these parts, which make up asseblies. Two cells from "Worksheet A" are linked to "Worksheet B". If "Worksheet A" is sorted the data in "Worksheet B" is no longer correct. Is there a way to sort "Worksheet A" and the data in "Worksheet B" stay the same or follow the data? The link has to be maintained between the two because the data in "Worksheet A" changes frequently and it needs to adjuston "Worksheet B" Thanks |
All times are GMT +1. The time now is 11:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com