Simultaneously sorting data in ranges on multiple sheets based on criteria on one sheet

Hi.
I'm a structural engineer and I'm developing a spreadsheet to use for batch designing concrete columns (pillars). The workbook is split into a number of different sheets. Sheet 1 is used for setting up the data in terms of concrete column size, label, forces imported from another programme etc. Sheet 2 is used for doing checks on the size and slenderness of the column and choosing an appropriate design method. Sheet 3 is used for specifying the required steel reinforcement and a the final sheet 4 is summary sheet. Sheets 2 references data on sheet 1, sheet 3 references data on sheets 2 and 3 and sheet 4 reference data on sheets 1,2 and 3. Sheets 2 and 3 also contain inputs that are not dependent on or linked to the other sheets. The number of rows in each sheet is always the same, but the number of columns varies.

I want to sort data on the first sheet according to a number of criteria and have the other sheets automatically sorted too. I can write a macro to do this on one sheet, but I can't do it across multiple sheets. The problem I have is that when I sort info on sheet 1, I can't get the info on sheets 2 and 3 that is input only on those sheets (i.e not a reference to, or a calculation based on sheet 1 data) to also sort based on the new order from sheet 1. A simple example would be that column A on sheet 2 is a reference to column A on sheet 1, but column B on sheet 2 consists of independent inputs. When sheet 1 is sorted based on a particular order for column A, Column A in sheets 2 is automatically re-ordered, but column B on sheets 2 is not, which means that the sheet 2 data has to be re-entered.
if I have all of the info on 1 sheet then this problem goes away, but the spreadsheet then becomes far too cumbersome to use.

I'm fairly new to VBA, with no programming experience and my macro writing is very much a hack and bash approach with the record macro function and what I can glean from Internet forums.
All suggestions welcome!

thanks
Chris

Perhaps you could use lookups rather than referencing cells directly. A sample workbook would help - simplified will do as long as it reflects your actual set up.

Hi Stephen
Thanks for the reply. The problem is not with how the data is reference or looked up across sheets, but with the data that has to be manually entered on the other sheets.
I've attached a basic example. Sheet 1 has basic concrete column data, sheet 2 has design calculations. If for example if I sort sheet 1 based on col no. then I need the manually chosen values on sheet 2 (fcu, fy, no. bars and dia. bars) for a particular col no, to be sorted too.
This is easily solve if all data is on one sheet, but I then I'll have 124 columns to work across, which becomes tricky to navigate.
Thanks for the help

Yes, it would be better to have all data on one sheet. You can always set up custom views to see specific columns or do analyses on separate sheets. Why do you need to sort the data? Do you need to manually add data repeatedly or is it a one-off exercise?

it's an iterative process, so data is amended on an on going basis.
Data needs to be sorted in a number of ways in various situations. For example in terms of load applied so that we can determine where it's economical to change a column's size rather than change the steel reinforcement; sorting in terms of column number for cross checking with the drawing office; and in terms of column type for grouping columns with the same reinforcement requirements. It makes information a great deal easier to deal with and pass on.
If I were to keep it all on one sheet, how would I create custom views?

Perhaps you could have a separate table with the fcu data etc, which you could amend and add to, and then use lookups in the main table to refer to that sheet and then all data would be sorted without having to have it all on one sheet.

For custom views, see here https://support.office.com/en-us/art...a-438fde18fc2b

You could set up a view with columns hidden etc.

thanks Stephen. I'll see what I can put all on one lookup table and I'll give custom views a try