Ensuring every value in a table has been used?

Copper Contributor

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

@Brandy1670 

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.

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.
=IF((Table1[@[Market Value (CAD)]]='1,000,000+:[0-49,999]0-49,999'!F3:F201),"","Missing")

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?

@Brandy1670 

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(
    OR(Table1[@[Market Value (CAD)]]=TOCOL('1,000,000+:[0-49,999]0-49,999'!F3:F201)),
    "",
    "Missing"
  )