Forum Discussion

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. I...

SAMPLE.xlsx1.7 MB

Excel Online

Formulas and Functions

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

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:

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

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

Continue a similar sequence..