Forum Discussion
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]),Table1[Y_Coordinates],FALSE),1)),IF(C8="No","0",INDEX(Table1[X_Coordinates],MATCH(1,(D13=INDIRECT("Q2:Q" & InputData!I6*InputData!J6))*(E13=INDIRECT("P2:P" & InputData!I6*InputData!J6)),0))))
This is the formula I am using, it works fine if the data and table are in the same sheet. I cannot get it to work when the data is moved to the 'InputData' sheet.
Is the problem with the text reference parts of the formula?
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)))
7 Replies
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?
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.
Are the ranges M2:M... and N2:N... in the formula supposed to be on the InputData sheet?
