Use table column names as range in INDEX() function

Blue1971

New Member
Joined
May 19, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
In an Excel 2016 table:

I have a formula that I use to check if parent records have the right 'Use With' values (if a child record has a 'Use With' value, then it's parents must have it too).
More info here.

Column B =
IFERROR(IF(SUMPRODUCT(COUNTIF(INDEX( C:E, [@[Parent - RowNum]],0),Table1[@[Use With 1]:[Use With 3]]))<>COUNTA(Table1[@[Use With 1]:[Use With 3]]), "error", ""),"")

enter image description here


For example, if I were to delete the value in C2, the formula would successfully flag it as causing an error:

enter image description here


Question:

I'm trying to convert all explicit cell references —to— structured references (aka table column names). I want to do this to avoid some issues I've been having with adding/deleting columns in the spreadsheet (and because I assume it's best practice/cleaner).

I've tried to replace C:E with Table1[[Use With 1]:[Use With 3]].

New formula for column B:
=IFERROR(IF(SUMPRODUCT(COUNTIF(INDEX( Table1[[Use With 1]:[Use With 3]], [@[Parent - RowNum]],0),Table1[@[Use With 1]:[Use With 3]]))<>COUNTA(Table1[@[Use With 1]:[Use With 3]]), "error", ""),"")

However, when I do that, the formula fails to work correctly — it doesn't flag the problem rows with "error".

enter image description here


What's the correct way to use table column names as a range in the INDEX() function (instead of using explicit cell references)?

Thank you.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,393
Office Version
  1. 365
Platform
  1. Windows
You will generally get faster responses if your provide sample data with XL2BB so that helpers do have to type it out to test. ;)

Also, you are familiar with your data and what it means but readers are not. So how do we identify a "parent record" and a "child record" in your data?

Can you tell us in words what the formula calculation should be?
 

Blue1971

New Member
Joined
May 19, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
You will generally get faster responses if your provide sample data with XL2BB so that helpers do have to type it out to test. ;)

Also, you are familiar with your data and what it means but readers are not. So how do we identify a "parent record" and a "child record" in your data?

Can you tell us in words what the formula calculation should be?

Thanks. I ended up figuring it out.

I just needed to add [#All], to the index array.

VBA Code:
Table1[[#All],[Use With 1]:[Use With 3]]

Full formula:

Code:
=IF(SUMPRODUCT(COUNTIF(INDEX(   Table1[[#All],[Use With 1]:[Use With 3]],    [@[Parent - RowNum]],0),Table1[@[Use With 1]:[Use With 3]]))<>COUNTA(Table1[@[Use With 1]:[Use With 3]]), "error", "")
 
Solution

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,393
Office Version
  1. 365
Platform
  1. Windows
Glad you got it sorted. Thanks for letting us know. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,328
Messages
5,624,034
Members
416,007
Latest member
csf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top