Forum Discussion

mustafa kun's avatar
mustafa kun
Copper Contributor
Mar 16, 2018

reading formula problem between some sheets

hello,

While  preparing an order tracing table in excel 2010, i had a problem which i cannot fix it.

in the attached file  , has a file, name "sample.xlsx"

 

there is 6 sheets in that file. In the 5 files, from "J" to "U" columns have same formulas which are taking data's from "ORDER LIST" sheet.

In these 5 sheets,  only one of them read and write the formulas in the related cells correctly.

Sheet  "12-13 FLOOR" , cells between coloumns "J" to "U" take the datas from "ORDER LIST" sheet, but , the other sheets ("LOW RISE", "MID RISE", HIGH RISE" and "TOP RISE" sheets) not filling the cells even same formulas using in the each of the sheets.

I will be so glad if someone solve this problem .

Thank you

 

 

Excel Online
Formulas and Functions

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Mar 16, 2018

    Hi Mustafa,

     

    Your formula compares the value in column D in one sheet with value in column J or I in order list for exactly the same row. (e.g. if D12=J12 if evaluate).

     

    For first sheet the match, for other ones not.

     

    As a comment, not sure what exactly you'd like to do.

    • mustafa kun's avatar
      mustafa kun
      Copper Contributor
      Mar 16, 2018

      Sergei,

      Thank you for your interest.

      i want to trace and control stone orders of a building. All ordered stones datas are in the "ORDER LIST" sheet.

      As the building is so big, wanted to divide to 5 seperate parts and each parts stones tracing will be in different sheets.

      Each seperate sheets of these 5, theorically able to take datas from ORDER LIST sheet and put then in the columns between "J" to "U" columns.

      Each sheets have exactly same formulas but only sheet "12-13 floor" takes the datas anf full the cells, but other 4 sheets dont perceived the datas and cells are emty.

      • Matt Mickle's avatar
        Matt Mickle
        Bronze Contributor
        Mar 16, 2018

        Your formula is not doing what you think it is.... it is not comparing against the whole column but only the cell in the same row.  It looks like it works because on 12-13.FLOOR all the values are in the same sequence.  You can identify this behavior by using Formulas > Evaluate Formula

         

        For Example:

        =IF(D3="","",IF('ORDER LIST'!I:I=D3,"ORDERED",""))  This only looks at the value in I3 of Order List.  You will need to use formulas like this:

         

        J:

        =IF(D3="","",IF(ISERROR(VLOOKUP(D3,'ORDER LIST'!I:I,1,FALSE))=TRUE,"","ORDERED"))

         

        K:

        =IF(ISERROR(VLOOKUP(D3,'ORDER LIST'!B:B,1,FALSE))=TRUE,"",VLOOKUP(D3,'ORDER LIST'!B:C,2,FALSE))

         

         

        Continue a similar sequence..

Resources