Forum Discussion
Help to match Fund Name to an array of possible entity Codes
To add to mathetes comments, I believe you make it more difficult than needed. In the attached workbook I added a few helper columns to the first tab. These determine that:
1) the date falls in the reporting month
2) the transaction type equals "Redemptions", as that is what you seem to be looking for.
3) it finds the row number for the entity code in the legend (from each of the four possible options)
4) based on 3) it finds the Fund Code
5) finally, it calculates the Units in case both 1) and 2) are TRUE
The formula in E in the Monthly Upload is a simple SUMIF.
In order to test the example, I changed one of the Transaction types to "Redemptions". It's very likely that someone else can create a very complex formula that does everything in one go. But I suspect that such a formula would not be very easy to understand or maintain.
It's very likely that someone else can create a very complex formula that does everything in one go. But I suspect that such a formula would not be very easy to understand or maintain.
Well said! Just because something can be done in the most difficult [and, not incidentally, impressive!] manner, doesn't mean it should be done that way.
Nesting functions and formulas within functions and formulas within.... is, admittedly, on occasion fun. But even if it works, it'll be next to impossible to decipher or debug when you get the inevitable #NUM or #REF or some other error message.
I'll go as far as nesting two or maybe three levels deep, but just keeping track of the parentheses gets tricky beyond that. And if it happens to be a shared spreadsheet, I'd just be creating work for myself in the future were something to go wrong.
All of that having been said, Excel is kind of amazing in that there is almost always a different route to the desired destination, often easier than the first tried.
Just walk around the mountain rather than trying to clamber over it.
