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 eve...
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?
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" )