OK, now that we are getting through the SUMIFS nightmare, there are two things to know:
-
SUMIFS are good when they work and COUNTIFS give you some debugging. The main problem is that there isn’t any way to figure out what rows are actually summed up, so no way to know about doubling counting. For most spreadsheets, it is far better to divide things up hard between rows so you can count them and audit them. For instance, for people if you can 100% assign them to a department and a project then you don’t need SUMIFS.
-
Links between Workbooks. These don’t seem to work at all with Mac Excel. What happens is that they seem to always be absolute links even though for Windows Excel, you get relative links if they are in the same folder. I’m not sure why that is, but it is a real pain. The implication is that links just don’t work and you always have to do this change link thing. In many ways, you really don’t want to link at least with Mac Excel. I can’t see any way to force a relative link. Maybe use the indirect function. Other folks are complaining about this behavior, but it seems to be on network drives where relative links are not honored. The solution seems to be to build up your own file name with INDIRECT(“RealNameOfSheet.xls!”&C5) which basically puts the name into a string so Excel can’t mess with it.
-
Inserting sheets into a workbook. Wow this causes lots of problems. If you have a sheet which refers to other another sheet, say an Assumptions sheet, then if you copy that sheet, it will retain an absolute reference to your old sheet. That is if you have a workbook OLD.XLSX and you have IS sheet in it and then it refers to ASSUME if you just copy it, it appears to then change all the references to “OLD.XLSX:ASSUME!A5” or what ever rather than referring to the new ones. The solution appears to be a manual copy of all the cells, so it is just copying text and not being smart.
-
Finally, if you have lots of sheets, it is a pain, but when transferring information between sheets, make a transfer table at say a well know row like 500, then you can check to make sure you “speared” all the references correctly. The same token, don’t insert rows into these kinds of things, just have lots of tables with the same row, then you can check easily if something is off. So you can see references like PROD1!B504+PROD2!B504+PROD3!B504 and make sure it is all working right.
