Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Cell referencing across sheets.

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Cell referencing across sheets.

    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

  2. #2
    Board Regular navic's Avatar
    Join Date
    Jun 2015
    Location
    Europa - Croatia
    Posts
    179
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cell referencing across sheets.

    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.
    Code:
    =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)
    Code:
    =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?

  3. #3
    New Member
    Join Date
    Apr 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cell referencing across sheets.

    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.

  4. #4
    Board Regular navic's Avatar
    Join Date
    Jun 2015
    Location
    Europa - Croatia
    Posts
    179
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cell referencing across sheets.

    Quote Originally Posted by ETyrrell View Post
    Thank you Navic for your help.
    You are welcome.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •