Forum Discussion
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
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?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" )
