Forum Discussion
Link data from another sheet and be able to sort it
I need to link data from 3 separate worksheets to one, I have used the formula =Sheetname!A1:A10 (example) But when I try and sort this information, I get a pop up saying "You can't change part of an array".
I think that means I need to change the way I link information, but I don't know how to deal with this.
I have been told not to copy and paste the information, it needs to come from a link.
5 Replies
That's how dynamic arrays function. But, since this occurs for you it means you can use either the SORT or SORTBY function to do the sorting within the formula.
Let's say you want to sort Sheetname!A1:A10 in descending order use this:
=SORT(Sheetname!A1:A10,,-1)
- Problem is, i need to get information from 3 seperate sheets into one, and they all need to be sorted from A-Z. So is there another way to maybe link the information that can also be sorted? I haven't been able to find this out anywhere.
If you are on Excel for MS365 you can use the VSTACK function. Otherwise, look into Power Query to combine the data from all three sheets (preferably formatted as Excel tables), sort as you wish and load back to Excel.
If you have Microsoft 365 or Office 2021, you can use
=SORT(Sheetname!A1:A10)
If you have an older version, use the formula
=Sheetname!A1
and fill down.
You will then be able to sort.
- Problem is, i need to get information from 3 separate sheets into one, and they all need to be sorted from A-Z. So is there another way to maybe link the information that can also be sorted? I haven't been able to find this out anywhere.
