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)))

HansVogelaar

MVP

Sep 19, 2022

andrewfeenan

Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

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))))))