CODEWITHSUNDEEP
excelformula
Jojoe
Jojoe

Reputation: 35

How to reference a cell from another sheet?

I have to count the number of cells that are not empty in a range, so I must use the COUNTA function. The problem is, I want it to be dynamic.

Steps:

First, I have to retrieve the reference of a cell in D column, and the line correspond to the line in A column who contains a specific string. Let's assume that this string is "NRUTI_02".

So here is my code

=ADDRESS(MATCH("NRUTI_02";A1:A65535;0);4)

It searchs for the string inside the range A1:A65535, and once it's found, it applies an offset to get the "D" column (4). -> If "NRUTI_02" is in A18, the output will be D18.

If I change to another sheet (Sheet1), the code will be the following :

=ADDRESS(MATCH("NRUTI_02";Sheet1!A1:A65535;0);4)

However, to use this formula inside COUNTA, I must use the INDIRECT formula. But if I put INDIRECT before my formula, it doesn't work.

How can I use my formula inside COUTNA function please ?

EDIT : My goal is to have something like COUNTA('Sheet1!'D18:D65535)

EDIT 2 : COUNTA("Sheet1!D"&MATCH("NRUTI_02";Sheet1!A1:A65535;0):D65535) isn't working

EDIT3 : Real example : My string "NRUTI_02" is in A18. The beginning of my range would be D18, because I want to count on column D. The next empty cell in column D is D21. I want to count all cells (lines) in range D18:D21. So I'd like my range to be kind of dynamic : the first arg find automatically D18, and the last arg would take the output of the first arg until the end of column.

COUNTA(INDEX(Sheet1!D:D;MATCH("NRUTI_02";Sheet1!A:A;0)):INDEX(Sheet1!D18:D65535;MATCH(TRUE;ISBLANK(Sheet1!D18:D65535);0))) ;

EDIT4 : Here is what my Excel file looks like : XLSX

Upvotes: 0

Views: 434

Answers (1)

BigBen
BigBen

Reputation: 49998

Doable with INDEX instead of ADDRESS.

=COUNTA(INDEX(Sheet1!D:D,MATCH("NRUTI_02",Sheet1!A:A,0)):INDEX(Sheet1!D:D,65535))

or even simpler:

=COUNTA(INDEX(Sheet1!D:D,MATCH("NRUTI_02",Sheet1!A:A,0)):Sheet1!D65535)

Upvotes: 2

Related Questions