In trying to use the INDIRECT function shown below.

=INDIRECT("B"&SUMPRODUCT((sheet1!C3:Q60=F4)*ROW(sheet1!C3:Q60)))

It works when written in the same sheet but when this function is used in a different sheet than the data used in the INDIRECT function it won't work.

according to the SUMPRODUCT((sheet1!C3:Q60=F4)*ROW(sheet1!C3:Q60)) i want access value from B1,B2,B3...etc.

but when the function runs it returns the value from the active sheet.

I want something like this

=INDIRECT((sheet1!"B"&SUMPRODUCT((data=AW4)*ROW(data))))

is this possible to achieve?

CodePudding user response：

I won't even try to unfrazzle the Sumproduct and why you're using it here, but suffice it to say that Indirect requires text input, which can be stitched together.

If you want to create a reference to Sheet1, column B, and the row number is calculated by your formula, then the syntax for Indirect is

=INDIRECT("Sheet1!B"&<your formula that returns a whole number>)

You may want to check your formula in a separate cell to ensure that it really returns the desired row number.

Indirect() has its problems, though. It is volatile an if used a lot, it will slow workbook performance. It may be more efficient to use Index() instead, for example

=index(sheet1!B:B,<your formula that returns a whole number>)