INDIRECT function from another sheet

[bukimi]

Hello!

I almost managed to make a formula I needed, but it shows values from current sheet instead of sheet I'm interested in.
=INDIRECT(ADDRESS(MATCH("Summary";Values!$A:$A;0);MATCH("2018-12-02";Values!4:4;0)))

It successfully finds row number, which has "Summary" as header and column with specified date as a header. Then it shows value of found row+column cell address, but from current sheet, when I need it to take cell value from "Values" sheet, too.
=Values!INDIRECT(---rest of formula---) is obviously wrong.

 

[JustynaMK]

Hi bukimi,

You can modify your ADDRESS formula to include a certain sheet name, e.g.:
=INDIRECT(ADDRESS(MATCH("Summary";Values!$A:$A;0);MATCH("2018-12-02";Values!4:4;0);;;"Values"))

Alternatively, you can use INDEX formula to achieve the same result:
=INDEX(Values!1:1048576;MATCH("Summary";Values!$A:$A;0);MATCH("2018-12-02";Values!4:4;0))

Hope it helps.

 

[bukimi]

That works! Thank you!
I didn't use INDIRECT function too much, so I didn't know I can add Sheet name so far in arguments list.

Still, how does this part work?
=INDEX(Values!1:1048576
It adds all cells of a Sheet as input?

 

[JustynaMK]

Glad it worked!

Correct - the first part of INDEX formula is your whole Array (you can "shrink" it to just a few rows & columns, depending where your data set is), then the next two arguments specify ROW and COLUMN numbers. The formula will return the cell value at the intersection of Row and Column.