CODEWITHSUNDEEP
excelexcel-formula
Billpo
Billpo

Reputation: 3

How can I reference a cell on a different sheet using a formula to determine the source location?

I am using Excel 2016. On Sheet A, I am trying to define a range of Sheet B cells based on the contents of those cells. Sheet B has roughly 60k rows in about 250 groups based on the contents of Column A. For a given value in Column C on Sheet A, I am using successfully using something like MATCH(C7,'Sheet B'!$A:$A,0) to find the first row of a group and MATCH(C7,'Sheet B'!$A:$A,1) to find the last row. So far, so good.

Ultimately, I'm trying to use the row values determined by values in Column A above to help define a range in Column B that I can use for further evaluation. However, I think I just don't know the right way to define the column value. I've been using COUNTA as a check, and it's failing every time.

=COUNTA('Sheet B'!"B"&MATCH(C7,'Sheet B'!$A:$A,0):'Sheet B'!"B"&MATCH(C7,'Sheet B'!$A:$A,1)

Upvotes: 0

Views: 36

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Use INDEX:

=COUNTA(INDEX('Sheet B'!B:B,MATCH(C7,'Sheet B'!$A:$A,0)):INDEX('Sheet B'!B:B,MATCH(C7,'Sheet B'!$A:$A,1)))

Upvotes: 2

Related Questions