Forum Discussion
USE A NAME FROM A TABLE
I don't like defined names and other formulas that reference empty cells, so I have converted everything (except the 365 current channel dynamic array formula) to Tables and structured references.
The first change or addition I am trying to do, gives me the attached result. Is there any incompatibility issue behind this?
Team,
1. Next step is to categorize in the same list the teams into wider teams that they belong to.2. Additionally, There ia also another wider team tier.3. Again, the teams are exclusive and the wider teams will also be exclusive as a simple sum of the subteams.4. For example AEK, AEL1 and AEL 2 belong to the wider team of Central GR, PAO and OSFP belong to South GR, ARIS, PAOK belong to North GR, And AEL 3,4,5 belong to North Belistria. In the next tier, Central GR, south GR and North Gr belong to Gr, while North Belistria belongs to Belistria. Other sub teams of all tiers may pop up.5. How can I achieve that in the same spreadsheet?6. Again, I am using excel 2016 at work or 365 at home.Thanks in advance!Best Regards!Peter,
1. I applied your advice in the original spreadsheet that you sent.
2. It works just fine.
3. On the other hand, when I try to apply the process on a new excel file, It does not work.
4. I have encountered this again with pivot tables from the 365 to the version of office before the 2016 (I don't quite remember exactly which it was).
5. I think that as I use this type of formula more often, it will be easier to apply.
Thanks a lot for the effort and help. It is much appreciated!
The columns with the #Name! error were neve intended to work with Office 2016 so are not a problem. The blank column is an issue though and, possibly, arises from the intersection operator '@' not converting to old style formulas. Try replacing these references by table structured references
= IFERROR( LOOKUP( 1, 1/([@POINTS]>=A_lower)/([@POINTS]<=A_upper)/([@CASH]>=B_lower)/([@CASH]<=B_upper), TEAM) ,"" )in which '@POINTS' is replaced by '[@POINTS]'.