Forum Discussion

Copper Contributor

Sep 19, 2022

Solved

Referring to data in another sheet within the INDIRECT function.

Hi, I am having issues using the INDIRECT function with data in another sheet called 'InputData'. =IF(B10=0,IF(C8="No","0",INDEX(Table1[X_Coordinates],MATCH(MIN(Table1[Y_Coordinates]),...

excel

Formulas and Functions

HansVogelaar
Sep 19, 2022
andrewfeenan

Does this do what you want?

=IF(E14=0,IF(F12="No","0",INDEX(Table1[X_Coordinates],MATCH(MIN(Table1[Y_Coordinates]),Table1[Y_Coordinates],FALSE),1)),IF(F12="No","0",INDEX(Table1[X_Coordinates],MATCH(1,(F16=INDIRECT("InputData!N2:N" & InputData!E5 * InputData!F5))*(G16=INDIRECT("InputData!M2:M" & InputData!E5 * InputData!F5))),0)))

andrewfeenan

Copper Contributor

Sep 19, 2022

Hi HansVogelaar

Thank you for your reply. I created a test file but it only contains very basic elements of the main file. I hope it provides enough information.

TestFile.xlsx47 KB

HansVogelaar

MVP

Sep 19, 2022

andrewfeenan

Are the ranges M2:M... and N2:N... in the formula supposed to be on the InputData sheet?

andrewfeenan
Copper Contributor
Sep 19, 2022
Yes, they are.
- HansVogelaar
  MVP
  Sep 19, 2022
  andrewfeenan
  
  Or slightly shorter
  
  =IF(F12="No",0,INDEX(Table1[X_Coordinates],IF(E14=0,MATCH(MIN(Table1[Y_Coordinates]),Table1[Y_Coordinates],0),MATCH(1,(F16=INDIRECT("InputData!N2:N" & InputData!E5*InputData!F5))*(G16=INDIRECT("InputData!M2:M" & InputData!E5*InputData!F5))))))
  - andrewfeenan
    Copper Contributor
    Sep 19, 2022
    That worked perfectly, thank you so much!
- HansVogelaar
  MVP
  Sep 19, 2022
  andrewfeenan
  
  Does this do what you want?
  
  =IF(E14=0,IF(F12="No","0",INDEX(Table1[X_Coordinates],MATCH(MIN(Table1[Y_Coordinates]),Table1[Y_Coordinates],FALSE),1)),IF(F12="No","0",INDEX(Table1[X_Coordinates],MATCH(1,(F16=INDIRECT("InputData!N2:N" & InputData!E5 * InputData!F5))*(G16=INDIRECT("InputData!M2:M" & InputData!E5 * InputData!F5))),0)))