Excel Table - refer to a particular row.xlsx

In the file attached, in sheet 1, there is a table (Table 1) with a column of names.

In sheet 2, there is also a table (Table 2) with a column of same names.
I would like to in sheet 1 have a column (below) to count a particular row if there is any blank cell in that row. For example, the person "f" in Table 2 has two blank cells and in Table 1, the column "Count" for person "f" should be 2 (actually is now).

But when I have a different sort in Table 1, the Countif formula in Table 1 does not follow the person "f". As a result, the Count result for each person in Table 1 is wrong.



Please see the attached file for the whole picture.
Any help is appreciated.

What your formula is doing is counting the number of blanks in the row in Table2 corresponding to the row in Table1. If the the data in the first columns of either table differ at all, you will not get the correct result.

The simplest solution would be to combine the tables. I understand your sample is likely a very simple example of your actual tables so it might be cumbersome. But as it is, both tables appear to have the same primary key i.e. the testees' names and thus theoretically would work.

There's certainly a way to do it the way you have it set up now, but I can't think of it. I'll ponder it.

Yes, your guess is right. The reason behind is I cannot merge the two tables.

Table 2 is a large database. Table 1 is a summary table. More I would like to do is to highlight the names (e.g. person "f" and person "u") based on their empty records on Table 2.

OK...I was able to kludge together a formula that does it and is sortable. See attached.

Attached Files

Excel Table - refer to a particular row.xlsx (11.3 KB, 9 views)

Yes. While using MATCH to get the absolute row numbers, I was thinking of how to reference using those row numbers with Table. Your use of ADDRESS and INDIRECT is really insightful.

Many thanks for your help!

To avoid volatile functions you can also use a little known (or rarely used) property of the INDEX function in D2

The extra comma force the INDEX function to return the entire corresponding row

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

By the way, I'd like to ask

Your second version would return something like ...sheet2!cell_ref:sheet2!another_ref were it should be sheet2!cell_ref:another_ref
Try the " examine formula" wizard to it at work

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Yes, I see. Thanks.

Glad to help. And please, in the future don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Damn yer good Peco!