Cell referencing across sheets.

[ETyrrell]

Hi,

I am a beginner at excel. A number of months ago I created an excel file, used for sorting quantity data.
The first sheet is used for importing (pasting) the raw data. This raw data is then sorted into other sheets depending on the "Product Type" column.

I have since introduced two new columns ("QtyUnits" and "Weight") to the raw data.
However, I cannot for the life of me figure out how to include these new columns in the sorting process.

I would greatly appreciate if someone could have a quick look at the file and make recommendations!

https://1drv.ms/x/s!ArkYLe2g0t73hQ-MCtStOF2G4Qat

E

 

[navic]

If I understood you well, try
1. On the worksheet "DataImport" format the cells in the column 'I' as 'General' that displays integers (numbers). If you still want to enter values as text then ignore my advice. But if you need a sum in the future, you will need to extract the number from the text data. This may imply an additional auxiliary column.

2. On each hidden worksheet that has the name 'Level2Calc, Level3Calc, etc', create two new columns. In 'H1' cell, place the header 'QtyUnits'. In the 'I1' cell, place the header 'Weight'.
In the 'H2' cell, place the following formula. (Copy this formula to the 'I2' column. Then both copy down). This is your formula.

=IFERROR(INDEX(DataImport!H:H;MATCH(ROWS($1:1);DataImport!$J:$J;0));"")

3. On each worksheet that has the name 'Level 2, Level 3, etc', create two new columns. In the 'G1' cell, place the header 'QtyUnits', In the 'H1' cell, place the header 'Weight'.
In the 'G2' cell, place the following ARRAY formula. (Copy this formula to the 'H2' column. Then both copy down). This formula returns the data from the same row of two conditions.
You enter this formula with Ctrl+Shift+Enter (not just Enter)

=INDEX(Level2Calc!H$2:H$1000;MATCH(1;($B2=Level2Calc!$B$2:$B$1000)*($C2=Level2Calc!$C$2:$C$1000);0))

Now you can use custom sort by column.
Hope this helps or at least gives you an idea?

 

[ETyrrell]

Thank you Navic for your help. I really appreciate it.

It was the hidden sheets in the end! I had completely forgotten about them...the dangers of returning to a workbook after months. I had wasted hours trying to decipher what was going on.

Everything is now working as it should.

 

[navic]

Thank you Navic for your help.

You are welcome.