![]() |
#1
|
|||
|
|||
![]() Views: 12 Size: 11.2 KB">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. Code:
=COUNTIF(Table2[@],"") Any help is appreciated. |
#2
|
|||
|
|||
![]()
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. |
#3
|
|||
|
|||
![]()
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. |
#4
|
|||
|
|||
![]()
OK...I was able to kludge together a formula that does it and is sortable. See attached.
|
#5
|
|||
|
|||
![]() Quote:
Many thanks for your help! |
#6
|
||||
|
||||
![]()
To avoid volatile functions you can also use a little known (or rarely used) property of the INDEX function in D2
Code:
=COUNTBLANK(INDEX(Table2[#All],MATCH(Table1[[#This Row],[Name]],Table2[Name],0)+1,))
__________________
Using O365 v2503 - 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 |
#7
|
|||
|
|||
![]() Quote:
Code:
' no error =COUNTBLANK(INDIRECT("Sheet2!"&ADDRESS(MATCH(A2,Table2[Name],0)+1,2)&":"&ADDRESS(MATCH(A2,Table2[Name],0)+1,4))) ' return #REF =COUNTBLANK(INDIRECT("Sheet2!"&ADDRESS(MATCH(A2,Table2[Name],0)+1,2)&":"&"Sheet2!"&ADDRESS(MATCH(A2,Table2[Name],0)+1,4))) ' Sheet2!B2:D2 (1st formula's result) ' Sheet2!B2:Sheet2!D2 (2nd formula's result) ' They should be the same (as a range) Last edited by tinfanide; 08-05-2015 at 04:01 AM. |
#8
|
||||
|
||||
![]()
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
__________________
Using O365 v2503 - 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 |
#9
|
|||
|
|||
![]()
Yes, I see. Thanks.
|
#10
|
||||
|
||||
![]()
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.
__________________
Using O365 v2503 - 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 |
#11
|
|||
|
|||
![]()
Damn yer good Peco!
|
![]() |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
Insert a Excel table in an existing PowerPoint table | Vallin | PowerPoint | 1 | 03-09-2015 06:25 AM |
![]() |
lily | Excel | 9 | 10-23-2014 06:21 AM |
How to change the path of a table copied by excel in word as linked table RTF? | Cristin7 | Word VBA | 3 | 03-10-2014 05:25 AM |
update one embedded excel table based on another excel table | ajetrumpet | Word | 0 | 07-05-2013 08:51 PM |
How do I refer to page numbers, when the numbers change as I prepare the document? | StevenD | Word | 5 | 11-29-2012 12:52 AM |