Excel table: refer to a particular row?

gebobs · #1 08-04-2015, 07:17 AM

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

tinfanide · #2 08-04-2015, 07:37 AM

Quote:

Originally Posted by gebobs

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

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!

tinfanide · #3 08-04-2015, 10:42 PM

Quote:

Originally Posted by gebobs

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

By the way, I'd like to ask

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)

Similar Threads
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
If (C1 = refer to a cell in a range) then (show the description in C2) & (corresponding price in C3)	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