# Ensuring every value in a table has been used?

Copper Contributor

# Ensuring every value in a table has been used?

I have a workbook that updates market values of accounts each month. I do this by updating the last tab "Data" that contains a table of account numbers and market values. I need to make sure that every account has been referenced in the previous 6 tabs by the formula "=XLOOKUP(C2,Table1[[#All],[Plan ID]],Table1[[#All],[Market Value (CAD)]])"

If an account has not been referenced I need to know so I can add it to the client. I'm trying to use =IF((Table1[@[Market Value (CAD)]]='1,000,000+:[0-49,999]0-49,999'!F:F),"","Missing") but am getting a spill error.

Any suggestions on how to make sure that the market values for all accounts have been used would be very much appreciated!

3 Replies

# Re: Ensuring every value in a table has been used?

The use of an entire column reference F:F will cause an array formula  to perform precisely 1048576 calculations which will not fit on a worksheet unless the formula is written in row 1.  The end of sheet blocks the calculation and returns #SPILL!

It would be better to define your range at the minimum size that encloses the data.

# Re: Ensuring every value in a table has been used?

Thank you for the help!
I've changed it but am now getting a reference error. I don't know the proper syntax for referencing multiple sheets in a formula, that's likely my problem.
I've selected all needed tabs using SHIFT then selected and dragged down to highlight the cells with the data I'm looking for.

This has also caused the formula to auto-fill on each row below my entry which I was not expecting, is that because it's a table and not a cell that I'm referencing?

# Re: Ensuring every value in a table has been used?

Your 3D reference could well be correct.  You could test whether

``=COUNTA('1,000,000+:[0-49,999]0-49,999'!F3:F201)``

gives a correct result.  However, what you can do with 3D ranges is very limited.  If you are a 365 user you could convert the 3D range to a single array by using TOCOL and then test to determine whether there is a TRUE somewhere in the list.

``````= IF(